Back up PostgreSQL and prove you can restore it

What a logical backup is

A logical backup, made with pg_dump, is a consistent copy of one database that can be restored on any server and often on a newer PostgreSQL version. It is the right starting point for small and medium databases. It does not capture roles and other cluster-wide objects, so they need a separate dump.

This guide assumes PostgreSQL is installed as in the database guide. Commands run as the postgres user with sudo, so no password is needed.

sudo -u postgres psql -c "\l"
sudo -u postgres psql -Atc "SELECT pg_size_pretty(pg_database_size('appdb'));"

Take a dump in custom format

The custom format compresses the dump and lets pg_restore restore selected parts, in parallel and into any database name. Prefer it to plain SQL for anything you will restore with a tool. Write it into a folder only postgres can read.

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql
sudo -u postgres pg_dump -Fc appdb -f /var/backups/postgresql/appdb-$(date +%F).dump
sudo ls -lh /var/backups/postgresql/

Save roles and settings

Roles, their passwords and tablespaces belong to the whole cluster. Dump them with pg_dumpall using the globals-only option. Without this file, a restore on a new server would have no users and ownership would not match.

sudo -u postgres pg_dumpall --globals-only -f /var/backups/postgresql/globals-$(date +%F).sql
sudo chmod 600 /var/backups/postgresql/globals-*.sql

Write a backup script with retention

The script dumps every database except the templates, saves the globals and deletes backups older than 14 days. It stops at the first error, so a failed run is visible in the journal. Adjust the retention to the amount of disk space you have, and copy the files off the server as well, for example with restic.

sudo tee /usr/local/bin/pg-backup >/dev/null <<'EOF'
#!/bin/bash
set -euo pipefail
umask 077
DEST=/var/backups/postgresql
DAY=$(date +%F)
install -d -o postgres -g postgres -m 700 "$DEST"
sudo -u postgres pg_dumpall --globals-only -f "$DEST/globals-$DAY.sql"
while IFS= read -r db; do
  sudo -u postgres pg_dump -Fc "$db" -f "$DEST/$db-$DAY.dump"
  echo "dumped $db"
done < <(sudo -u postgres psql -Atc "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres' ORDER BY datname")
find "$DEST" -type f -mtime +14 -delete
EOF
sudo chmod 750 /usr/local/bin/pg-backup
sudo /usr/local/bin/pg-backup

Schedule it

Run the script daily with a systemd timer. The timer catches up if the server was off at the scheduled time. Check the journal after the first run and look at the files it produced.

sudo tee /etc/systemd/system/pg-backup.service >/dev/null <<'EOF'
[Unit]
Description=PostgreSQL backup

[Service]
Type=oneshot
Nice=10
ExecStart=/usr/local/bin/pg-backup
EOF
sudo tee /etc/systemd/system/pg-backup.timer >/dev/null <<'EOF'
[Unit]
Description=Daily PostgreSQL backup

[Timer]
OnCalendar=*-*-* 02:30:00
Persistent=true

[Install]
WantedBy=timers.target
EOF
sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer
systemctl list-timers pg-backup.timer
sudo systemctl start pg-backup.service
sudo journalctl -u pg-backup.service --no-pager | tail -n 10

Restore into a separate test database

A backup only counts once you have restored it. Restore into a new database with a different name, so that the live one is never touched. Check that the dump can be read, restore it, then compare table row counts with the original.

Compare actual count(*) results, not n_live_tup: that is a statistics estimate and may be zero immediately after a restore. Counting large tables can be expensive. Also verify application totals, sample records and a test read through the app.

LATEST=$(ls -t /var/backups/postgresql/appdb-*.dump | head -n 1)
sudo -u postgres pg_restore --list "$LATEST" | head -n 15
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore --no-owner -d restore_test "$LATEST"

for d in appdb restore_test; do
  echo "== $d"
  sudo -u postgres psql -d "$d" -P pager=off <<'SQL'
SELECT format('SELECT %L AS table_name, count(*) AS rows FROM %I.%I;',
              schemaname || '.' || tablename, schemaname, tablename)
FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;
\gexec
SQL
done

sudo -u postgres dropdb restore_test

--------------------------------------------------------------------------------
(1 row)
   table_name    | rows 
-----------------+------
 public.evidence |    3
(1 row)
--------------------------------------------------------------------------------
(1 row)
   table_name    | rows 
-----------------+------
 public.evidence |    3
(1 row)

Restore for real after a disaster

On a new server, install PostgreSQL, restore the globals file first so roles exist, then create the database and restore the dump into it. Write down this procedure and keep it with your backups. Practise it so that you know how long it takes.

sudo -u postgres psql -f globals-YYYY-MM-DD.sql
sudo -u postgres createdb -O appuser appdb
sudo -u postgres pg_restore -d appdb appdb-YYYY-MM-DD.dump

Next steps

Copy the backup folder off the server (see the restic guide), because backups on the same disk are lost with the server. For very large databases, or when you cannot lose more than a day of data, look at continuous archiving and point-in-time recovery in the PostgreSQL documentation. Monitor the backup job so that you hear about a failure.

find /var/backups/postgresql -name "*.dump" -mtime -1 | grep -q . && echo "recent backup found" || echo "NO RECENT BACKUP"