Mercurial > p > roundup > code

comparison doc/postgresql.txt @ 8416:370689471a08 issue2550923_computed_property

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merge from default branch accumulated changes since Nov 2023
author John Rouillard <rouilj@ieee.org>
date Sun, 17 Aug 2025 16:12:25 -0400
parents 32ead43b8299
children
comparison
equal deleted inserted replaced
7693:78585199552a 8416:370689471a08
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 (Roundup user creates and uses a database) create a user
52 using::
53
54 psql -c "CREATE ROLE roundupuser WITH CREATEDB LOGIN PASSWORD 'rounduppw';" -U postgres
55
56 After running ``roundup-admin init`` to create your databases, you can
57 remove the CREATEDB permission using::
58
59 psql -c "ALTER ROLE roundupuser NOCREATEDB;"
60
61 If needed (e.g. you want to deploy a new tracker) you can use ``ALTER
62 ROLE`` with ``CREATEDB`` to add the permission back.
63
64 For case 2 (Roundup user uses a schema under a pre-created database)
65 you need to create the user::
66
67 psql -c "CREATE ROLE roundupuser LOGIN PASSWORD 'rounduppw';" -U postgres
68
69 This psql command connects as the postgres database superuser. You may
70 need to run this under sudo as the postgres user or provide a password
71 to become an admin on the postgres db process.
72
73
74 Creating a Database
75 -------------------
76
77 For case 1, roundup will create the database on demand using the
78 ``roundup_admin init`` command. So there is nothing to do here.
79
80 For case 2, run::
81
82 psql -c "CREATE DATABASE roundupdb;GRANT CREATE ON DATABASE roundupdb TO roundupuser;" -U postgres
83
84 This creates the database and allows the roundup user to create a new
85 schema when running ``roundup_admin init``.
86
87
30 Running the PostgreSQL unit tests 88 Running the PostgreSQL unit tests
31 ================================= 89 =================================
32 90
33 The user that you're running the tests as will need to be able to access 91 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 92 the postgresql database on the local machine and create and drop
35 databases. See the config values in 'test/db_test_base.py' 93 databases and schemas. See the config values in 'test/db_test_base.py'
36 about which database connection, name and user will be used. 94 about which database connection, name and user will be used.
37 95
38 At this time the following command will setup the user:: 96 At this time the following commands will setup the users and required
97 databases::
39 98
40 sudo -u postgres psql -c "CREATE ROLE rounduptest WITH CREATEDB LOGIN PASSWORD 'rounduptest';" -U postgres 99 sudo -u postgres psql -c "CREATE ROLE rounduptest WITH CREATEDB LOGIN PASSWORD 'rounduptest';" -U postgres
41 100
42 Note ``rounduptest`` is a well known account, so you should 101 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 102 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 103
45 ``rounduptest`` first. So something like this should work:: 104 Note ``rounduptest`` and ``rounduptest_schema`` are well known
105 accounts, so you should remove/disable the accounts after testing and
106 set up a suitable production account. You need to remove any database
107 owned by ``rounduptest`` first. To clean everything up, something like
108 this should work::
46 109
47 110
48 sudo -u postgres psql -c "DROP DATABASE rounduptest;" -U postgres 111 sudo -u postgres psql -c "DROP DATABASE rounduptest;" -U postgres
49 sudo -u postgres psql -c "DROP ROLE rounduptest;" -U postgres 112 sudo -u postgres psql -c "DROP ROLE rounduptest;" -U postgres
113 sudo -u postgres psql -c "DROP DATABASE rounduptest_schema;" -U postgres
114 sudo -u postgres psql -c "DROP ROLE rounduptest_schema;" -U postgres
50 115
51 If the ``rounduptest`` database is left in a broken state 116 If the ``rounduptest`` database is left in a broken state
52 (e.g. because of a crash during testing) dropping the database and 117 (e.g. because of a crash during testing) dropping the database and
53 restarting the tests should fix it. 118 restarting the tests should fix it. If you have issues while running
119 the schema test, you can drop the ``rounduptest` schema in the
120 ``rounduptest_schema`` database.
54 121
55 Credit 122 Credit
56 ====== 123 ======
57 124
58 The postgresql backend was originally submitted by Federico Di Gregorio 125 The postgresql backend was originally submitted by Federico Di Gregorio
Roundup Issue Tracker: http://roundup-tracker.org/