PostgreSQL

For members

PostgreSQL is available as a service for members on caffeine. Just run ceo postgresql create to create a new database for your account. As of this writing, club reps cannot create PostgreSQL databases for their clubs via ceo, so they will need to send an email to syscom instead.

For syscom

We are also running a Postgres database on coffee, which is not available to members. Any software installed by syscom should use this database instead of the one on caffeine.

Creating a database manually on caffeine

See how ceo does it.

Upgrades

Upgrading Postgres is more difficult than upgrading MySQL; when you upgrade the Debian version on a machine, a newer version of Postgres will be installed but the old version will remain and the data will not be migrated. You are responsible for manually upgrading the database yourself on all machines where Postgres is installed (currently, just coffee and caffeine).

Here's the Debian-specific way to do it (steps adapted from here). In the example below, we will assume that we are upgrading from Postgres 13 to 15.

1. First, take a full backup of the database. DO NOT SKIP THIS STEP.

   pg_dumpall | xz -T0 > dump.sql.xz
   

2. Drop the new database, which should be empty at this point. Make sure that you are not dropping the old database instead! You can run pg_lsclusters to see which database versions are present.

   # Make sure that this is the NEW version, not the old version!
   pg_dropcluster --stop 15 main
   

3. Upgrade the cluster:

   pg_upgradecluster -v 15 13 main
   

4. Run psql and make sure that the databases are present:

   su - postgres -c psql
   \l
   \q
   

5. Once we are sure that everything is working, drop the old database:

   # Make sure that this is the OLD version, not the new version!
   pg_dropcluster --stop 13
   

6. It is now safe to purge the old postgres package:

   apt purge postgresql-13
   

Backups

We use pgBackRest for Postgres backups. It has already been installed on coffee and caffeine.

Installation

In the example below, we will be installing pgbackrest on coffee, and using corn-syrup to store the backups (via SSH).

The pgbackrest package in bookworm is too old and doesn't support SFTP, so we're going to download the packages we need from trixie instead (starting from trixie and higher, this should no longer be necessary):

# On coffee
wget http://mirror.csclub.uwaterloo.ca/debian/pool/main/p/pgbackrest/pgbackrest_2.48-1_amd64.deb
wget http://mirror.csclub.uwaterloo.ca/debian/pool/main/libz/libzstd/libzstd1_1.5.5+dfsg2-2_amd64.deb
apt install ./pgbackrest_2.48-1_amd64.deb ./libzstd1_1.5.5+dfsg2-2_amd64.deb

Switch to the postgres user and create a new SSH key:

su - postgres
ssh-keygen -t ed25519

Login to corn-syrup, switch to the syscom user, and paste the public key you created earlier into ~/.ssh/authorized_keys:

restrict ssh-ed25519 AAAAC3Nza... postgres@coffee

Create a folder to store the backups:

mkdir ~/backups/coffee/pgbackrest

Next, on coffee, paste something like the following into /etc/pgbackrest.conf. Make sure to adjust repo1-path and pg1-path.

[global]
repo1-retention-full=2
repo1-retention-diff=4
repo1-bundle=y
repo1-type=sftp
repo1-sftp-host=corn-syrup
repo1-sftp-host-user=syscom
repo1-path=/users/syscom/backups/coffee/pgbackrest
repo1-sftp-private-key-file=/var/lib/postgresql/.ssh/id_ed25519
repo1-sftp-public-key-file=/var/lib/postgresql/.ssh/id_ed25519.pub
repo1-sftp-host-key-hash-type=sha256
repo1-sftp-host-key-check-type=none
start-fast=y
log-level-console=info
process-max=4
compress-type=zst

[main]
pg1-path=/var/lib/postgresql/15/main

The config above will keep two full backups and at least four differential backups. See https://pgbackrest.org/user-guide.html#retention for more details.

Next, open /etc/postgresql/15/main/postgresql.conf and add/edit the following lines:

archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

See https://pgbackrest.org/user-guide.html#quickstart/configure-archiving for more details.

Next, restart Postgres:

systemctl restart postgresql@15-main

Switch to the postgres user, create the main stanza, and run the first backup:

su - postgres
pgbackrest --stanza=main stanza-create
pgbackrest --stanza=main check
pgbackrest --stanza=main backup --type=full

Upgrades

Normally, whenever you upgrade Postgres, you have to manually edit /etc/pgbackrest.conf and run the "stanza-upgrade" command. To make this easier for future sysadmins, I wrote a wrapper script around pgbackrest which does this automatically if it detects that Postgres was upgraded. Paste the following into /var/lib/postgresql/bin/pgbackrest-wrapper.sh and make it executable:

#!/bin/bash

set -ex
if [ "$(id -un)" != postgres ]; then
    echo "This script should run as the postgres user" >&2
    exit 1
fi
# Use the full path to ls to avoid bash aliases
mapfile -t pg_versions < <(/bin/ls -1 /var/lib/postgresql | grep -P '^\d+$')
if [ ${#pg_versions[@]} -ne 1 ]; then
    echo "Expected to find 1 Postgres version, found ${#pg_versions[@]} instead: ${pg_versions[*]}" >&2
    exit 1
fi
pg_ver=${pg_versions[0]}
mapfile -t pgbr_versions < <(grep -oP '/var/lib/postgresql/\K(\d+)' /etc/pgbackrest.conf)
if [ ${#pgbr_versions[@]} -ne 1 ]; then
    echo "Expected to find 1 pgBackRest folder, found ${#pgbr_versions[@]} instead: ${pgbr_versions[*]}" >&2
    exit 1
fi
pgbr_ver=${pgbr_versions[0]}
if [ $pg_ver -eq $pgbr_ver ]; then
    # pgbackrest.conf is up to date, so just run the backup normally
    pgbackrest "$@"
    exit 0
elif [ $pg_ver -lt $pgbr_ver ]; then
    echo "pgBackRest does not support downgrades - you will have to fix this manually" >&2
    exit 1
fi
# sed -i needs to create a temporary file, and the postgres user doesn't have
# write permissions on /etc, so write to a temporary file first
sed "s,/var/lib/postgresql/$pgbr_ver,/var/lib/postgresql/$pg_ver," /etc/pgbackrest.conf > /tmp/pgbackrest.conf
cp /tmp/pgbackrest.conf /etc/pgbackrest.conf
rm /tmp/pgbackrest.conf
pgbackrest --stanza=main stanza-upgrade
pgbackrest --stanza=main check
# Run the backup
pgbackrest "$@"

Now we can just pass pgbackrest parameters directly to this script, e.g. pgbackrest-wrapper.sh --stanza=main backup.

Cron

We are going to use systemd timers because they are much nicer to use than cron. Install /usr/local/bin/csc-systemd-email and /etc/systemd/system/csc-email-on-failure@.service on the target machine so that we get emails for failed jobs (there should be a copy of this on caffeine).

Paste the following into /etc/systemd/system/postgres-backup@.service:

[Unit]
Description=Postgres backup (%i)
Documentation=https://wiki.csclub.uwaterloo.ca/PostgreSQL#Backups

[Service]
Type=oneshot
User=postgres
ExecStart=/var/lib/postgresql/bin/pgbackrest-wrapper.sh --stanza=main backup --type=%i

[Unit]
OnFailure=csc-email-on-failure@%n.service

Paste the following into /etc/systemd/system/postgres-backup-full.timer:

[Unit]
Description=Postgres backup (full)

[Timer]
# Full back up at 00:15 every Sunday and Wednesday
OnCalendar=Sun,Wed *-*-* 00:15:00
Unit=postgres-backup@full.service
Persistent=true

[Install]
WantedBy=timers.target

Paste the following into /etc/systemd/system/postgres-backup-diff.timer:

[Unit]
Description=Postgres backup (diff)

[Timer]
# Differential backup at 00:30 every day
OnCalendar=*-*-* 00:30:00
Unit=postgres-backup@diff.service
Persistent=true

[Install]
WantedBy=timers.target

Paste the following into /etc/systemd/system/postgres-backup-incr.timer:

[Unit]
Description=Postgres backup (incr)

[Timer]
# Incremental backup at the 45th minute of every hour
OnCalendar=*-*-* *:45:00
Unit=postgres-backup@incr.service
Persistent=true

[Install]
WantedBy=timers.target

Finally, enable and start the timers:

systemctl daemon-reload
systemctl enable --now postgres-backup-full.timer
systemctl enable --now postgres-backup-diff.timer
systemctl enable --now postgres-backup-incr.timer

Restore

Suppose we want to restore the latest backup, and the installed Postgres is 15. First, make sure that you actually have at least one backup present for this version:

su -c postgres -c 'pgbackrest --stanza=main info'

Next, stop the database and delete all of the files:

systemctl stop postgresql@15-main
rm -rf /var/lib/postgresql/15/main/*

Now switch to the postgres user and run the "restore" command:

su - postgres
pgbackrest --stanza=main restore

If you start Postgres, everything should be in a working state:

systemctl start postgresql@15-main

If you want to restore a backup which is not the latest version, pass the --set argument to pgbackrest. See https://pgbackrest.org/user-guide.html#restore for more details.