Mercurial > p > roundup > code

annotate doc/postgresql.txt @ 7696:4af0d235b570

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
feat(db): support using postgresql service connection file Add new service rdbms config option to set the service name to be used with a postgresql service connection file. This can be done using the PGSERVICE environment variable for a single instance tracker server. For a multi-instance server this per-tracker config option is needed. Note that settings (host, user, (db)name...) in config.ini file will override the service connection file setting. Also setting PGSERVICE and service will use the service setting.
author John Rouillard <rouilj@ieee.org>
date Tue, 07 Nov 2023 12:11:37 -0500
parents b21ebabeb2ef
children 3071db43bfb6
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
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1875
diff changeset
30 Running the PostgreSQL unit tests
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1875
diff changeset
31 =================================
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1875
diff changeset
32
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1875
diff changeset
33 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
34 the postgresql database on the local machine and create and drop
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
35 databases. See the config values in 'test/db_test_base.py'
6a32a2fb95b4 Docs update for mysql and postgresl: Mentioned test/db_test_base.py because
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4512
diff changeset
36 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
37
7409
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
38 At this time the following command will setup the user::
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1875
diff changeset
39
7409
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
40 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
41
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
42 Note ``rounduptest`` is a well known account, so you should
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
43 remove/disable the account after testing and set up a suitable
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
44 production account. You need to remove any database owned by
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
45 ``rounduptest`` first. So something like this should work::
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
46
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
47
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
48 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
49 sudo -u postgres psql -c "DROP ROLE rounduptest;" -U postgres
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
50
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
51 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
52 (e.g. because of a crash during testing) dropping the database and
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
53 restarting the tests should fix it.
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 6662
diff changeset
54
2594
c0d6d5004464 rdbms doc updates
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
55 Credit
c0d6d5004464 rdbms doc updates
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
56 ======
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
57
2594
c0d6d5004464 rdbms doc updates
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
58 The postgresql backend was originally submitted by Federico Di Gregorio
c0d6d5004464 rdbms doc updates
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
59 <fog@initd.org>
c0d6d5004464 rdbms doc updates
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
60
Roundup Issue Tracker: http://roundup-tracker.org/