Nightly PostgreSQL backup script with pg_dump and rotation

This script makes a logical backup of a whole PostgreSQL server every night: one globals.sql with roles and tablespaces, and one custom-format .dump per database. Each run writes to a new folder named after the date and time. Old folders are removed only when every database in the current run was dumped successfully, so a broken night never deletes your last good backups.

The script

#!/usr/bin/env bash
set -Eeuo pipefail

BACKUP_DIR=${BACKUP_DIR:-/var/backups/postgresql}
KEEP_DAYS=${KEEP_DAYS:-14}
export PGHOST=${PGHOST:-/var/run/postgresql}

log() { printf '%s %s\n' "$(date '+%F %T')" "$*" >&2; }

[[ $KEEP_DAYS =~ ^[0-9]+$ ]] || { log "KEEP_DAYS must be a number"; exit 2; }

umask 077
TARGET="$BACKUP_DIR/$(date +%Y-%m-%d_%H%M%S)"
mkdir -p -- "$TARGET"
log "writing to $TARGET"

pg_dumpall --globals-only --file="$TARGET/globals.sql"

mapfile -t DATABASES < <(psql -X -At -v ON_ERROR_STOP=1 -d postgres -c \
  "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate ORDER BY datname")

status=0
for db in "${DATABASES[@]}"; do
  log "dumping $db"
  if pg_dump --format=custom --file="$TARGET/$db.dump.partial" --dbname="$db" \
     && pg_restore --list "$TARGET/$db.dump.partial" > /dev/null; then
    mv -- "$TARGET/$db.dump.partial" "$TARGET/$db.dump"
  else
    log "FAILED: $db"
    rm -f -- "$TARGET/$db.dump.partial"
    status=1
  fi
done

if (( status != 0 )); then
  log "at least one dump failed; old backups were kept"
  exit "$status"
fi

touch -- "$TARGET/COMPLETE"

find "$BACKUP_DIR" -mindepth 1 -maxdepth 1 -type d -name '20*' -mtime +"$KEEP_DAYS" -print |
while IFS= read -r old; do
  log "removing old backup $old"
  rm -rf -- "$old"
done

log "done: ${#DATABASES[@]} database(s)"

Install it

1. Save it as /usr/local/bin/pg-nightly-dump.sh and make it executable with sudo chmod 755 /usr/local/bin/pg-nightly-dump.sh.
2. Create the backup folder, owned by the postgres OS user: sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql.
3. Run it once by hand as postgres and check the output folder.
4. Add it to the postgres user’s crontab.

sudo -u postgres /usr/local/bin/pg-nightly-dump.sh
ls -l /var/backups/postgresql/*/

sudo -u postgres crontab -e
# m h dom mon dow command
30 2 * * * /usr/local/bin/pg-nightly-dump.sh >> /var/log/postgresql/pg-nightly-dump.log 2>&1

How it works

• It connects through the local socket as postgres, which uses peer authentication on Debian and Ubuntu. If your server uses a different socket directory (check with SHOW unix_socket_directories;), set PGHOST to it. For a remote server set PGHOST, PGUSER and a ~/.pgpass file.
• The database list comes from pg_database, skipping templates and databases that do not allow connections.
• Each dump is written as .partial and renamed only after pg_restore --list could read it, so a half-written file never looks like a good backup.
• umask 077 makes the files readable only by the owner, because dumps contain all your data.
• KEEP_DAYS (default 14) and BACKUP_DIR can be set in the environment, for example KEEP_DAYS=30 in the crontab line.

Rotation deletes folders. Before the first real run, check what would be removed by running the same find command with -print only: find /var/backups/postgresql -mindepth 1 -maxdepth 1 -type d -name '20*' -mtime +14 -print. Do not point BACKUP_DIR at a folder that holds anything else.

Restoring

sudo -u postgres psql -X -f /var/backups/postgresql/2026-10-15_023000/globals.sql postgres
sudo -u postgres createdb -T template0 appdb
sudo -u postgres pg_restore -d appdb -j 4 /var/backups/postgresql/2026-10-15_023000/appdb.dump

Copy the backup folder to another machine or to object storage as well; a backup on the same disk as the database does not survive the loss of that disk. For restoring to a moment between two nightly runs you need WAL archiving and physical backups instead.

Official documentation

• pg_dump
• pg_dumpall
• pg_restore