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.

Logging / Journal

sys-systemd-journalctl-postgres

Microsoft Azure Database for PostgreSQL

Microsoft Azure

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