Postgres

Note

In these examples, green is the live database.

Note

In these examples, the owner name is the same as the database name.

List current databases:

psql -U postgres -c "\l"

Backup database:

pg_dump -U postgres green -f ~/repo/temp/out.sql

Restore data (from green to the empty blue database):

psql -U blue -d blue -f out.sql 2> out.log

Important

Remember to check out.log.

Authentication

For more information ref md5 and scram-sha-256 see our Chat ref scram-sha-256 and Ticket 1437.

Logging / Journal

SQL Logging

To log SQL queries and their parameters:

# vim /etc/postgresql/14/main/postgresql.conf
log_statement = all
# restart postgresql
tail -f /var/log/postgresql/postgresql-14-main.log

Tip

The parameters appear after the query in the logs e.g:

2023-12-07 10:26:23 GMT LOG:  execute <unnamed>: SELECT RES.*
from ACT_RE_PROCDEF RES WHERE  RES.ID_ in ($1) order by RES.ID_ asc
2023-12-07 10:26:23 GMT DETAIL:
parameters: $1 = 'createInvoice:4:7bd4c336-80ae-11ee-b09f-000d3a7f022c'

Warning

If the query fails, the parameters are not written to the log file (which is not very helpful).

For more information, see value too long

Microsoft Azure Database for PostgreSQL

Microsoft recommend using PgBouncer with Azure Database for PostgreSQL: Setup PgBouncer connection pooling proxy with Azure DB for PostgreSQL The configuration below assumes you will be using PgBouncer.

The Microsoft Azure Database for PostgreSQL has an extra layer of security (authentication). To configure this:

Create a db/postgres_settings.sls file in your pillar e.g:

# db/postgres_settings.sls
postgres_settings:
  bouncer: True
  bouncer_listen_on_all_addresses: True
  listen_address: mypgserver.postgres.database.azure.com
  postgres_pass: azure-postgres-db-password
  postgres_ssh: verify-full
  postgres_user: azure-postgres-db-user
  postgres_host: mypgserver

And add it to the config for the server e.g:

# top.sls
'test-a':
  - db.postgres_settings
  - sites.my

Add the db_user to the sites pillar e.g:

# sites/my.sls
sites:
  www.hatherleigh.info:
    package: hatherleigh_info
    profile: django
    db_user: "www_hatherleigh_info@mypgserver"

In your Django project, settings/production.py:

DOMAIN = get_env_variable("DOMAIN")
DATABASE_NAME = DOMAIN.replace(".", "_").replace("-", "_")
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql_psycopg2",
        "NAME": DATABASE_NAME,
        "USER": get_env_variable("DB_USER"),
        "PASSWORD": "",
        "HOST": "localhost",
        "PORT": "6432",
        "DISABLE_SERVER_SIDE_CURSORS": True,
    }
}

Warning

Don’t forget to DISABLE_SERVER_SIDE_CURSORS.

Read Only User

Add database_users to your pillar file. The key is the name of the read only user e.g. bi_www_hatherleigh_info:

# sites/my.sls
sites:
  www.hatherleigh.info:
    db_pass: MyPassword
    db_type: psql
    database_users:
      bi_www_hatherleigh_info:
        pass: MyReadOnlyPassword
    env:
      activiti_host: "localhost"
      activiti_pass: "kermit"

The Salt state will create the read only users in the PGBouncer files.

Tip

Prefix the name of the read only user with bi_ (business information / reporting).

Use fabric to create the read only users e.g:

fab create-database-users www.hatherleigh.info

Warning

The user will not have access to database tables created after running this command. To give the user access to new tables, just run the command again.

Upgrade Cluster

After a do-release-upgrade, you will probably be left with several database clusters e.g:

# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
10  main    5434 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

Tip

Example commands for moving from Postgres 10 to 12, can be found here: Postgres Upgrade Cluster…

To move the data from Postgres 12 to Postgres 14, run the following as root:

# list the clusters
pg_lsclusters

# drop the new (empty) cluster
pg_dropcluster 14 main --stop
# migrate the data from 12
pg_upgradecluster 12 main

# remove the 12 cluster
pg_dropcluster 12 main --stop

# remove the old database engines
apt remove postgresql-12

# list the clusters
pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
14  main    5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log