Mercurial > p > roundup > code

comparison doc/postgresql.txt @ 7719:3071db43bfb6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
feat: issue2550852 - support using a specified PostgreSQL db schema Finally after 7 years this is closed. roundup/backends/back_postgresql.py: Support use of schema when specified in RDBMS_NAME. Stuart McGraws code is finally merged 8-). test/test_postgresql.py, test/conftest.py: Run all postgresql tests in the schema db as well. Also make sure that db_nuke raises an error when trying to delete the schema test database. Conftest defines pg_schema mark that can be used to exclude schema tests with pytest -m "not pg_schema". roundup/configuration.py: change doc on RDBMS_NAME to include db.schema form. .travis.yml, .github/workflows/ci-test.yml: create schema test db; add user for testing with schema; grant new user create privs for schema. doc/installation.txt: Reference to roundup-admin init deleting schema added. doc/mysql.txt doc/postgresql.txt: New documentation on psql/mysql commands to set up a production db. doc/upgrading.txt: mention schema support, also document service setting for selecting connection from pg_service.conf. doc/reference.txt: update config.ini documentation for RDBMS_NAME.
author John Rouillard <rouilj@ieee.org>
date Wed, 27 Dec 2023 22:52:14 -0500
parents b21ebabeb2ef
children 32ead43b8299
comparison
equal deleted inserted replaced
7718:3da452f4a3ac 7719:3071db43bfb6
25 25
26 .. _psycopg: https://www.psycopg.org/ 26 .. _psycopg: https://www.psycopg.org/
27 .. _PostgreSQL: https://www.postgresql.org/ 27 .. _PostgreSQL: https://www.postgresql.org/
28 28
29 29
30 Preparing the Database
31 ======================
32
33 Roundup can use Postgres in one of two ways:
34
35 1. Roundup creates and uses a database
36 2. Roundup uses a pre-created database and creates and uses a schema
37 under the database.
38
39 In the examples below, replace ``roundupuser``, ``rounduppw`` and
40 ``roundupdb`` with suitable values.
41
42 This assumes that you are running Postgres on the same machine with
43 Roundup. Using a remote database, setting up SSL/TLS and other
44 authentication methods is beyond the scope of this
45 documentation. However examples are welcome on the wiki or mailing
46 list.
47
48 Creating a Role/User
49 --------------------
50
51 For case 1 create a user using::
52
53 psql -c "CREATE ROLE roundupuser WITH CREATEDB LOGIN PASSWORD 'rounduppw';" -U postgres
54
55 After running ``roundup-admin init`` to create your databases, you can
56 remove the CREATEDB permission using::
57
58 psql -c "ALTER ROLE roundupuser NOCREATEDB;"
59
60 If needed (e.g. you want to deploy a new tracker) you can use ``ALTER
61 ROLE`` with ``CREATEDB`` to add the permission back.
62
63 For case 2 you need to create the user::
64
65 psql -c "CREATE ROLE roundupuser LOGIN PASSWORD 'rounduppw';" -U postgres
66
67 This psql command connects as the postgres database superuser. You may
68 need to run this under sudo as the postgres user or provide a password
69 to become an admin on the postgres db process.
70
71
72 Creating a Database
73 -------------------
74
75 For case 1, roundup will create the database on demand using the
76 ``roundup_admin init`` command. So there is nothing to do here.
77
78 For case 2, run::
79
80 psql -c "CREATE DATABASE roundupdb;GRANT CREATE ON DATABASE roundupdb TO roundupuser;" -U postgres
81
82 This creates the database and allows the roundup user to create a new
83 schema when running ``roundup_admin init``.
84
85
30 Running the PostgreSQL unit tests 86 Running the PostgreSQL unit tests
31 ================================= 87 =================================
32 88
33 The user that you're running the tests as will need to be able to access 89 The user that you're running the tests as will need to be able to access
34 the postgresql database on the local machine and create and drop 90 the postgresql database on the local machine and create and drop
35 databases. See the config values in 'test/db_test_base.py' 91 databases and schemas. See the config values in 'test/db_test_base.py'
36 about which database connection, name and user will be used. 92 about which database connection, name and user will be used.
37 93
38 At this time the following command will setup the user:: 94 At this time the following commands will setup the users and required
95 databases::
39 96
40 sudo -u postgres psql -c "CREATE ROLE rounduptest WITH CREATEDB LOGIN PASSWORD 'rounduptest';" -U postgres 97 sudo -u postgres psql -c "CREATE ROLE rounduptest WITH CREATEDB LOGIN PASSWORD 'rounduptest';" -U postgres
41 98
42 Note ``rounduptest`` is a well known account, so you should 99 sudo -u postgres psql -c "CREATE ROLE rounduptest_schema LOGIN PASSWORD 'rounduptest';" -U postgres
43 remove/disable the account after testing and set up a suitable 100 sudo -u postgres psql -c "CREATE DATABASE rounduptest_schema;GRANT CREATE ON DATABASE rounduptest_schema TO rounduptest_schema;" -U postgres
44 production account. You need to remove any database owned by 101
45 ``rounduptest`` first. So something like this should work:: 102 Note ``rounduptest`` and ``rounduptest_schema`` are well known
103 accounts, so you should remove/disable the accounts after testing and
104 set up a suitable production account. You need to remove any database
105 owned by ``rounduptest`` first. To clean everything up, something like
106 this should work::
46 107
47 108
48 sudo -u postgres psql -c "DROP DATABASE rounduptest;" -U postgres 109 sudo -u postgres psql -c "DROP DATABASE rounduptest;" -U postgres
49 sudo -u postgres psql -c "DROP ROLE rounduptest;" -U postgres 110 sudo -u postgres psql -c "DROP ROLE rounduptest;" -U postgres
111 sudo -u postgres psql -c "DROP DATABASE rounduptest_schema;" -U postgres
112 sudo -u postgres psql -c "DROP ROLE rounduptest_schema;" -U postgres
50 113
51 If the ``rounduptest`` database is left in a broken state 114 If the ``rounduptest`` database is left in a broken state
52 (e.g. because of a crash during testing) dropping the database and 115 (e.g. because of a crash during testing) dropping the database and
53 restarting the tests should fix it. 116 restarting the tests should fix it. If you have issues while running
117 the schema test, you can drop the ``rounduptest` schema in the
118 ``rounduptest_schema`` database.
54 119
55 Credit 120 Credit
56 ====== 121 ======
57 122
58 The postgresql backend was originally submitted by Federico Di Gregorio 123 The postgresql backend was originally submitted by Federico Di Gregorio
Roundup Issue Tracker: http://roundup-tracker.org/