Mercurial > p > roundup > code

annotate doc/postgresql.txt @ 8328:3bf6ad421347 2.5.0b1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
chore: update files for release 2.5.0b1.
author John Rouillard <rouilj@ieee.org>
date Wed, 11 Jun 2025 17:04:51 -0400
parents 32ead43b8299
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
6167
81ae33038ec5 more index entries.
John Rouillard <rouilj@ieee.org>
parents: 6163
diff changeset
1 .. index:: postgres; deployment notes
6163
c2fd254c9257 Doc updates/indexing.
John Rouillard <rouilj@ieee.org>
parents: 5756
diff changeset
2
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
3 ==========================
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
4 PostgreSQL/psycopg Backend
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
5 ==========================
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
6
3059
213468d6e7c9 Add installation documentation for tsearch2.
Johannes Gijsbers <jlgijsbers@users.sourceforge.net>
parents: 2860
diff changeset
7 This are notes about PostqreSQL backend based on the psycopg adapter for
213468d6e7c9 Add installation documentation for tsearch2.
Johannes Gijsbers <jlgijsbers@users.sourceforge.net>
parents: 2860
diff changeset
8 Roundup issue tracker.
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
9
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
10 Prerequisites
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
11 =============
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
12
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
13 To use PostgreSQL as backend for storing roundup data, you should
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
14 additionally install:
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
15
6662
a0a100453eb9 Document minimum psycopg version
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6262
diff changeset
16 1. PostgreSQL_ 8.x or higher
a0a100453eb9 Document minimum psycopg version
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6262
diff changeset
17
a0a100453eb9 Document minimum psycopg version
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6262
diff changeset
18 2. The psycopg_ python interface to PostgreSQL_
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
19
6662
a0a100453eb9 Document minimum psycopg version
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6262
diff changeset
20 Note that you currently need at least version 2.8 of psycopg_ -- the
a0a100453eb9 Document minimum psycopg version
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6262
diff changeset
21 postgresql backend will work with 2.7 but if you're using the native
a0a100453eb9 Document minimum psycopg version
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6262
diff changeset
22 postgresql indexer you need at least 2.8. Also if you want to run the
a0a100453eb9 Document minimum psycopg version
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6262
diff changeset
23 tests, these also need 2.8. If your distribution has an older version we
a0a100453eb9 Document minimum psycopg version
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6262
diff changeset
24 suggest that you install into a python virtual environment.
3666
8304a1c1cc98 encourage use of latest psycopg
Richard Jones <richard@users.sourceforge.net>
parents: 3500
diff changeset
25
6662
a0a100453eb9 Document minimum psycopg version
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6262
diff changeset
26 .. _psycopg: https://www.psycopg.org/
a0a100453eb9 Document minimum psycopg version
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6262
diff changeset
27 .. _PostgreSQL: https://www.postgresql.org/
a0a100453eb9 Document minimum psycopg version
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6262
diff changeset
28
3666
8304a1c1cc98 encourage use of latest psycopg
Richard Jones <richard@users.sourceforge.net>
parents: 3500
diff changeset
29
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
30 Preparing the Database
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
31 ======================
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
32
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
33 Roundup can use Postgres in one of two ways:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
34
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
35 1. Roundup creates and uses a database
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
36 2. Roundup uses a pre-created database and creates and uses a schema
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
37 under the database.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
38
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
39 In the examples below, replace ``roundupuser``, ``rounduppw`` and
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
40 ``roundupdb`` with suitable values.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
41
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
42 This assumes that you are running Postgres on the same machine with
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
43 Roundup. Using a remote database, setting up SSL/TLS and other
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
44 authentication methods is beyond the scope of this
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
45 documentation. However examples are welcome on the wiki or mailing
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
46 list.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
47
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
48 Creating a Role/User
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
49 --------------------
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
50
7961
32ead43b8299 docs: postgres user; wsgi default mode update; diff for task.index.html
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
51 For case 1 (Roundup user creates and uses a database) create a user
32ead43b8299 docs: postgres user; wsgi default mode update; diff for task.index.html
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
52 using::
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
53
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
54 psql -c "CREATE ROLE roundupuser WITH CREATEDB LOGIN PASSWORD 'rounduppw';" -U postgres
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
55
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
56 After running ``roundup-admin init`` to create your databases, you can
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
57 remove the CREATEDB permission using::
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
58
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
59 psql -c "ALTER ROLE roundupuser NOCREATEDB;"
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
60
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
61 If needed (e.g. you want to deploy a new tracker) you can use ``ALTER
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
62 ROLE`` with ``CREATEDB`` to add the permission back.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
63
7961
32ead43b8299 docs: postgres user; wsgi default mode update; diff for task.index.html
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
64 For case 2 (Roundup user uses a schema under a pre-created database)
32ead43b8299 docs: postgres user; wsgi default mode update; diff for task.index.html
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
65 you need to create the user::
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
66
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
67 psql -c "CREATE ROLE roundupuser LOGIN PASSWORD 'rounduppw';" -U postgres
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
68
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
69 This psql command connects as the postgres database superuser. You may
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
70 need to run this under sudo as the postgres user or provide a password
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
71 to become an admin on the postgres db process.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
72
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
73
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
74 Creating a Database
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
75 -------------------
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
76
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
77 For case 1, roundup will create the database on demand using the
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
78 ``roundup_admin init`` command. So there is nothing to do here.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
79
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
80 For case 2, run::
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
81
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
82 psql -c "CREATE DATABASE roundupdb;GRANT CREATE ON DATABASE roundupdb TO roundupuser;" -U postgres
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
83
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
84 This creates the database and allows the roundup user to create a new
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
85 schema when running ``roundup_admin init``.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
86
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
87
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1875
diff changeset
88 Running the PostgreSQL unit tests
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1875
diff changeset
89 =================================
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1875
diff changeset
90
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1875
diff changeset
91 The user that you're running the tests as will need to be able to access
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1875
diff changeset
92 the postgresql database on the local machine and create and drop
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
93 databases and schemas. See the config values in 'test/db_test_base.py'
4513
6a32a2fb95b4 Docs update for mysql and postgresl: Mentioned test/db_test_base.py because
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4512
diff changeset
94 about which database connection, name and user will be used.
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1875
diff changeset
95
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
96 At this time the following commands will setup the users and required
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
97 databases::
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1875
diff changeset
98
7409
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
99 sudo -u postgres psql -c "CREATE ROLE rounduptest WITH CREATEDB LOGIN PASSWORD 'rounduptest';" -U postgres
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
100
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
101 sudo -u postgres psql -c "CREATE ROLE rounduptest_schema LOGIN PASSWORD 'rounduptest';" -U postgres
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
102 sudo -u postgres psql -c "CREATE DATABASE rounduptest_schema;GRANT CREATE ON DATABASE rounduptest_schema TO rounduptest_schema;" -U postgres
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
103
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
104 Note ``rounduptest`` and ``rounduptest_schema`` are well known
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
105 accounts, so you should remove/disable the accounts after testing and
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
106 set up a suitable production account. You need to remove any database
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
107 owned by ``rounduptest`` first. To clean everything up, something like
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
108 this should work::
7409
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
109
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
110
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
111 sudo -u postgres psql -c "DROP DATABASE rounduptest;" -U postgres
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
112 sudo -u postgres psql -c "DROP ROLE rounduptest;" -U postgres
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
113 sudo -u postgres psql -c "DROP DATABASE rounduptest_schema;" -U postgres
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
114 sudo -u postgres psql -c "DROP ROLE rounduptest_schema;" -U postgres
7409
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
115
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
116 If the ``rounduptest`` database is left in a broken state
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
117 (e.g. because of a crash during testing) dropping the database and
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
118 restarting the tests should fix it. If you have issues while running
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
119 the schema test, you can drop the ``rounduptest` schema in the
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
120 ``rounduptest_schema`` database.
7409
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
121
2594
c0d6d5004464 rdbms doc updates
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
122 Credit
c0d6d5004464 rdbms doc updates
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
123 ======
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
124
2594
c0d6d5004464 rdbms doc updates
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
125 The postgresql backend was originally submitted by Federico Di Gregorio
c0d6d5004464 rdbms doc updates
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
126 <fog@initd.org>
c0d6d5004464 rdbms doc updates
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
127
Roundup Issue Tracker: http://roundup-tracker.org/