PostgreSQL Backup and Restore on OpenBSD 7.5

Logical backup with pg_dump

Single database

sudo -u postgres pg_dump -Fc appdb > /backups/appdb_$(date +%F).dump

All databases

sudo -u postgres pg_dumpall > /backups/all_$(date +%F).sql

Restore a single database:

sudo -u postgres pg_restore -d appdb /backups/appdb_2026-05-07.dump

Physical backup with pg_basebackup

sudo -u postgres pg_basebackup -D /backups/base_$(date +%F) -Ft -z -P

Automated daily backup script

Create /usr/local/bin/pg_daily_backup.sh:

#!/bin/bash
BACKUP_DIR="/backups/postgres"
RETENTION_DAYS=7
mkdir -p "$BACKUP_DIR"
sudo -u postgres pg_dumpall | gzip > "$BACKUP_DIR/all_$(date +%F_%H%M).sql.gz"
find "$BACKUP_DIR" -name '*.sql.gz' -mtime +$RETENTION_DAYS -delete

chmod +x /usr/local/bin/pg_daily_backup.sh

Add a cron job (daily at 02:00):

0 2 * * * root /usr/local/bin/pg_daily_backup.sh

Point-in-Time Recovery (PITR)

Enable WAL archiving in postgresql.conf:

archive_mode = on
archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f'
restore_command = 'cp /wal_archive/%f %p'

Restore to a specific time:

# recovery.conf (PostgreSQL < 12) or postgresql.conf (>= 12):
recovery_target_time = '2026-05-07 03:00:00'