PostgreSQL Logical Backup on Ubuntu: A Complete pg_dump Script

A backup script is easy to write and easy to get wrong. The failure modes are rarely a crashed pg_dump — they are the missing roles file, the retention pass that deleted the last good copy after a failed night, the overlapping cron runs writing into the same directory, and the dump nobody ever restored. This guide builds a logical backup script for Ubuntu that closes each of those gaps, and explains why every guard is there.

Logical or physical? Pick by what you need to recover

PostgreSQL offers two backup families, and they are complements rather than competitors. Choosing the wrong one is the most expensive mistake in this topic, so start here.

The practical rule: use logical backups for portability and for recovering the one table someone truncated by mistake, and use physical backups plus WAL archiving for disaster recovery with a tight RPO. Most serious deployments run both. The companion article covers the physical side: PostgreSQL physical backup and point-in-time recovery with pg_basebackup.

What a dump does and does not contain

The single most common restore failure is a dump that restores cleanly into a cluster where nothing works, because the objects that live outside the database were never captured.

pg_dump operates on one database. Roles, passwords, tablespace definitions, and other cluster-wide objects are not in it. Those come from pg_dumpall --globals-only. A backup set without that file restores tables whose owners and grants do not exist.

• Included: schemas, tables, data, indexes, constraints, views, functions, triggers, sequences and their current values, extensions (as CREATE EXTENSION), and per-database grants.
• Not included: roles and their memberships, tablespace definitions, the contents of external tablespaces, server configuration (postgresql.conf, pg_hba.conf), and WAL.

The script below always writes both parts into the same timestamped set, so the two can never drift apart.

Choose the custom format, not plain SQL

--format=custom is the format worth defaulting to. Unlike a plain .sql file it is compressed, and — more importantly — it is a container that pg_restore can read selectively:

# List everything inside the dump without restoring anything
pg_restore --list app.dump

# Restore one table out of a full-database dump
pg_restore --data-only --table=orders -d app app.dump

# Restore with parallel workers (the big win on large databases)
pg_restore --jobs=4 -d app app.dump

# Extract the DDL as readable SQL, to review before applying
pg_restore --schema-only -f review.sql app.dump

A plain SQL dump gives you none of that: restoring one table means hand-editing a multi-gigabyte text file. The --list capability is also what makes cheap integrity verification possible, which the script uses on every dump it produces.

The guards that make a script trustworthy

These five behaviors are what separate the script below from a one-line pg_dump in a crontab. Each one exists because of a specific way backup jobs fail in production.

• Staging directory with an atomic rename. Dumps are written to .in-progress-TIMESTAMP and the directory is renamed only when everything succeeded. A restore can never pick up a set that was still being written, and an interrupted run leaves no plausible-looking wreckage.
• Integrity verification. Every dump is read back with pg_restore --list. It is fast, and it catches a truncated file or a destination filesystem that silently ran out of space — the two failures that otherwise stay invisible until the restore.
• Retention after success only. The find -mtime deletion runs after a good set exists. The classic disaster is a script that prunes first, fails to dump, and repeats nightly until every copy is gone.
• A lock. If a dump takes longer than the cron interval, a second run would start writing while the first is still going. flock makes the second run exit immediately instead.
• Globals in every set. Roles and tablespace definitions are dumped alongside the databases, so a set is self-sufficient.

Prepare the Ubuntu host

Run the backup as the postgres operating-system user, which authenticates locally through peer authentication and needs no password. Client tools come from the PostgreSQL packages:

sudo apt update
sudo apt install -y postgresql-client

# Verify the client version matches or exceeds the server's major version
pg_dump --version
sudo -u postgres psql -XAtqc "SHOW server_version"

# Destination, owned by postgres and unreadable by anyone else
sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql

For a remote server, create a dedicated role and use a ~/.pgpass file rather than putting a password in the script or an environment variable:

-- On the database server. A read of every row is required, so this role is powerful.
CREATE ROLE backup LOGIN PASSWORD 'use-a-generated-secret';
GRANT pg_read_all_data TO backup;   -- PostgreSQL 14+

# On the backup host, as the postgres user
echo 'db.internal:5432:*:backup:use-a-generated-secret' >> ~/.pgpass
chmod 600 ~/.pgpass

The complete backup script

Save as postgresql-logical-backup-ubuntu.sh, or download the ready-to-run file:

#!/usr/bin/env bash
#
# Logical backup for PostgreSQL on Ubuntu using pg_dump / pg_dumpall.
#
# Usage:
#   sudo -u postgres ./postgresql-logical-backup-ubuntu.sh
#   sudo -u postgres BACKUP_ROOT=/backup/pg RETENTION_DAYS=14 ./postgresql-logical-backup-ubuntu.sh
#   sudo -u postgres DATABASES="app billing" ./postgresql-logical-backup-ubuntu.sh
#
set -Eeuo pipefail

BACKUP_ROOT="${BACKUP_ROOT:-/var/backups/postgresql}"
RETENTION_DAYS="${RETENTION_DAYS:-7}"
JOBS="${JOBS:-2}"
COMPRESS_LEVEL="${COMPRESS_LEVEL:-6}"
LOCK_FILE="${LOCK_FILE:-/var/lock/postgresql-logical-backup.lock}"

log() {
  echo "[$(date --iso-8601=seconds)] $*"
}

fail() {
  echo "ERROR: $*" >&2
  exit 1
}

[[ "$RETENTION_DAYS" =~ ^[0-9]+$ ]] || fail "RETENTION_DAYS must be an integer."
[[ "$JOBS" =~ ^[1-9][0-9]*$ ]] || fail "JOBS must be a positive integer."
[[ "$COMPRESS_LEVEL" =~ ^[0-9]$ ]] || fail "COMPRESS_LEVEL must be 0-9."

for command_name in psql pg_dump pg_dumpall pg_restore flock; do
  command -v "$command_name" >/dev/null || fail "Missing command: $command_name"
done

# Serialize runs: a second cron firing while the first is still dumping would
# write into the same set and break the "complete or absent" guarantee below.
exec 9>"$LOCK_FILE" || fail "Cannot open lock file $LOCK_FILE"
flock -n 9 || fail "Another backup run is already in progress."

# Fail fast if the server is unreachable, before creating any directory.
SERVER_VERSION="$(psql -XAtqc 'SHOW server_version' postgres)" ||
  fail "Cannot connect to PostgreSQL. Check PGHOST/PGPORT/PGUSER and ~/.pgpass."

if [[ -z "${DATABASES:-}" ]]; then
  # datallowconn excludes template0, which cannot be dumped.
  mapfile -t DATABASE_LIST < <(psql -XAtqc     "SELECT datname FROM pg_database
      WHERE datallowconn AND NOT datistemplate
      ORDER BY datname" postgres)
else
  read -r -a DATABASE_LIST <<< "$DATABASES"
fi

(( ${#DATABASE_LIST[@]} > 0 )) || fail "No databases to back up."

TIMESTAMP="$(date +%Y%m%dT%H%M%S)"
# Dump into a staging directory and rename only on success, so a partial set
# is never mistaken for a valid backup by a restore or by the retention pass.
STAGING_DIR="${BACKUP_ROOT}/.in-progress-${TIMESTAMP}"
FINAL_DIR="${BACKUP_ROOT}/${TIMESTAMP}"

install -d -m 700 "$BACKUP_ROOT"
install -d -m 700 "$STAGING_DIR"

cleanup_staging() {
  local exit_code=$?
  if (( exit_code != 0 )) && [[ -d "$STAGING_DIR" ]]; then
    log "Run failed; removing incomplete set $STAGING_DIR"
    rm -rf -- "$STAGING_DIR"
  fi
  exit "$exit_code"
}
trap cleanup_staging EXIT

log "PostgreSQL $SERVER_VERSION"
log "Databases: ${DATABASE_LIST[*]}"
log "Destination: $FINAL_DIR"

# Roles, tablespace definitions and other cluster-wide objects are NOT inside a
# per-database dump. Without this file, a restore lands with no owners or grants.
log "Dumping globals (roles, tablespaces)"
pg_dumpall --globals-only --no-role-passwords   --file="${STAGING_DIR}/globals.sql"

for database in "${DATABASE_LIST[@]}"; do
  target="${STAGING_DIR}/${database}.dump"
  log "Dumping database: $database"
  # --jobs applies to directory format only; custom format is written serially.
  # JOBS is carried into the manifest because pg_restore is where it pays off.
  pg_dump     --format=custom     --compress="$COMPRESS_LEVEL"     --no-password     --verbose     --file="$target"     "$database" 2>"${STAGING_DIR}/${database}.log"

  # A dump that pg_restore cannot read is not a backup. Reading the table of
  # contents is cheap and catches truncation or a broken destination filesystem.
  pg_restore --list "$target" >/dev/null ||
    fail "Integrity check failed for $target"

  log "  $(du -h "$target" | cut -f1) — table of contents verified"
done

(cd "$STAGING_DIR" && sha256sum ./*.dump ./globals.sql > SHA256SUMS)

cat > "${STAGING_DIR}/MANIFEST.txt" <<MANIFEST
backup_type      logical (pg_dump custom format)
started_at       ${TIMESTAMP}
finished_at      $(date --iso-8601=seconds)
server_version   ${SERVER_VERSION}
host             $(hostname -f 2>/dev/null || hostname)
pg_dump_version  $(pg_dump --version)
databases        ${DATABASE_LIST[*]}
restore_globals  psql -f globals.sql postgres
restore_database pg_restore --clean --if-exists --create --jobs=${JOBS} -d postgres <database>.dump
MANIFEST

chmod -R go-rwx "$STAGING_DIR"
mv -- "$STAGING_DIR" "$FINAL_DIR"
trap - EXIT

# Retention runs only after a successful set exists, so a broken night never
# deletes the last good copy.
if (( RETENTION_DAYS > 0 )); then
  log "Applying retention: removing sets older than ${RETENTION_DAYS} days"
  find "$BACKUP_ROOT" -mindepth 1 -maxdepth 1 -type d     -name '20*T*' -mtime "+${RETENTION_DAYS}"     -print -exec rm -rf -- {} +
fi

log "Backup completed: $FINAL_DIR"
log "Total size: $(du -sh "$FINAL_DIR" | cut -f1)"
log "Reminder: a backup is only valid once a restore has been tested."

Run it

sudo install -m 750 -o postgres -g postgres   postgresql-logical-backup-ubuntu.sh /usr/local/bin/

# First run, explicit and watched
sudo -u postgres /usr/local/bin/postgresql-logical-backup-ubuntu.sh

A successful run prints each database, its dump size, and the confirmation that the table of contents was read back:

[2026-08-05T02:00:01-03:00] PostgreSQL 16.3 (Ubuntu 16.3-1.pgdg24.04+1)
[2026-08-05T02:00:01-03:00] Databases: app billing
[2026-08-05T02:00:01-03:00] Dumping globals (roles, tablespaces)
[2026-08-05T02:00:02-03:00] Dumping database: app
[2026-08-05T02:04:17-03:00]   1.4G — table of contents verified
[2026-08-05T02:04:17-03:00] Dumping database: billing
[2026-08-05T02:05:02-03:00]   212M — table of contents verified
[2026-08-05T02:05:03-03:00] Applying retention: removing sets older than 7 days
[2026-08-05T02:05:03-03:00] Backup completed: /var/backups/postgresql/20260805T020001

Each set is self-describing:

/var/backups/postgresql/20260805T020001/
├── app.dump          # custom format, restore with pg_restore
├── app.log           # pg_dump --verbose output for this database
├── billing.dump
├── billing.log
├── globals.sql       # roles and tablespaces, restore with psql
├── MANIFEST.txt      # versions, host, and the restore commands
└── SHA256SUMS        # detects silent corruption at rest

Schedule it with cron

Install the job for the postgres user so it inherits the same peer authentication as the manual run:

sudo crontab -u postgres -e

# Nightly logical backup at 02:00, two weeks of retention.
# MAILTO makes cron deliver stderr; without it, failures are silent.
MAILTO=dba@example.com
0 2 * * * RETENTION_DAYS=14 /usr/local/bin/postgresql-logical-backup-ubuntu.sh   >> /var/log/postgresql/backup.log 2>&1

If you prefer systemd timers over cron, the equivalent is a Type=oneshot service with User=postgres plus a timer with OnCalendar=*-*-* 02:00:00 and Persistent=true, which additionally re-runs a backup missed because the host was off.

Restoring — the part that must be rehearsed

Restore order matters: roles must exist before objects owned by them are created.

cd /var/backups/postgresql/20260805T020001

# 0. Confirm the files are intact before starting
sha256sum --check SHA256SUMS

# 1. Globals first: roles and tablespace definitions
sudo -u postgres psql -f globals.sql postgres

# 2. Then each database. --create makes the database, so connect to postgres.
sudo -u postgres pg_restore   --clean --if-exists --create   --jobs=4   --dbname=postgres   app.dump

What the flags do, and when they will hurt you:

• --clean --if-exists drops existing objects before recreating them. This destroys the current contents of the target database. Never point it at a production database you have not intended to overwrite.
• --create issues CREATE DATABASE from the dump, so you connect to postgres rather than to the database being restored.
• --jobs=4 restores tables and builds indexes in parallel. It is the single largest restore speedup; set it near the core count, and note it cannot be combined with --single-transaction.
• --single-transaction makes the restore all-or-nothing — valuable for a critical restore, but it serializes the work and holds locks throughout.

Recovering a single table from a full dump is the everyday case, and it is why the custom format was worth choosing:

# Inspect what is available
pg_restore --list app.dump | grep -i orders

# Restore just that table's data into a scratch database first,
# then copy the rows across. Restoring straight over production
# loses whatever was written since the dump.
sudo -u postgres createdb app_recovery
sudo -u postgres pg_restore --table=orders --dbname=app_recovery app.dump

After any restore, statistics are missing until the planner is given fresh ones. Until then, queries can be dramatically slower than on the source:

sudo -u postgres vacuumdb --analyze-in-stages --dbname=app

Prove the backup works

An unverified backup is a hypothesis. Verification means a full restore into a throwaway environment, then checking the data — not merely that the command exited zero.

# Restore last night's set into a scratch database and compare
LATEST=$(ls -1d /var/backups/postgresql/20*T* | tail -1)
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore --jobs=4 -d restore_test "$LATEST/app.dump"

# Row counts per table, source versus restored
sudo -u postgres psql -X -d restore_test -c "
  SELECT relname, n_live_tup
    FROM pg_stat_user_tables
   ORDER BY n_live_tup DESC
   LIMIT 20;"

sudo -u postgres dropdb restore_test

Do this on a schedule — monthly at minimum — and record how long the restore took. That number is your real Recovery Time Objective, and it is usually several times larger than people assume, because index rebuilds dominate.

Common failures and what they mean

• pg_dump: error: aborting because of server version mismatch — the client is older than the server. Install the newer postgresql-client.
• The dump takes progressively longer each night — usually growth, but check for bloat: a table with far more dead than live rows is read in full by pg_dump. Autovacuum tuning solves this at the source.
• Long-running dumps block schema changes — pg_dump holds an ACCESS SHARE lock on every table for its whole duration, so a concurrent ALTER TABLE waits, and any query queued behind that ALTER waits too. Schedule migrations outside the backup window.
• Permission denied for a table — the backup role lacks read access on something added later. GRANT pg_read_all_data avoids per-table grant drift.
• The disk fills mid-dump — the staging directory is discarded and the run exits non-zero, so no truncated set is promoted. Monitor free space on the backup volume as a first-class metric.

Watch backups the way you watch the database

The metric that matters is not "did the job run" but "how old is the newest verified backup, and can it be restored inside the recovery window." PG Monitoring tracks database growth, bloat, and I/O behavior alongside the workload, so you can see a dump window stretching toward the maintenance window before it collides with it — and correlate a slow backup with the checkpoint or vacuum activity actually causing it.

References: the PostgreSQL manual covers SQL dump, the pg_dump and pg_restore references, and pg_dumpall for cluster globals.