Skip to main content

Back up every database on several PostgreSQL versions

On RHEL, Rocky and Alma, PGDG packages let PostgreSQL 16 and 17 (for example) run side by side, each on its own port with its own binaries in /usr/pgsql-<ver>/bin. One script can back up both.

Describe your instances

PG_INSTANCES=(
  "5432|/usr/pgsql-16/bin|pg16"
  "5433|/usr/pgsql-17/bin|pg17"
)

Use each instance's own pg_dump. A newer pg_dump can dump an older server, but not the other way round.

The loop

Run it as the postgres user so it connects over the local socket with peer authentication. No password is needed.

for inst in "${PG_INSTANCES[@]}"; do
  IFS='|' read -r port bin name <<<"$inst"
  out=/mnt/backup/$(hostname -s)/$name; mkdir -p "$out"
  "$bin/pg_dumpall" -p "$port" --globals-only | gzip > "$out/$(date +%F_%H).globals.sql.gz"
  while read -r db; do
    "$bin/pg_dump" -p "$port" "$db" | gzip > "$out/$(date +%F_%H).$db.sql.gz"
  done < <("$bin/psql" -p "$port" -Atc \
     "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres'")
done
find /mnt/backup/$(hostname -s) -type f -name '*.sql.gz' -mtime +7 -delete
  • Back up the globals. pg_dump doesn't include roles, passwords or grants. Without pg_dumpall --globals-only, a restore to a new server has no logins.
  • Check that the backup share is mounted (mountpoint -q), and that the postgres user can write to it.
  • Use flock to stop runs from overlapping.

Schedule

/etc/cron.d/backup-postgres:

0 * * * * postgres /usr/local/bin/backup_postgres.sh >/dev/null 2>&1

Restore

gunzip -c 2026-01-01_03.globals.sql.gz | psql -p 5433 postgres
createdb -p 5433 mydb && gunzip -c 2026-01-01_03.mydb.sql.gz | psql -p 5433 mydb
Back to top