PostgreSQL Backup and Restore on OpenBSD 7.5

Logical – 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

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

Physical – pg_basebackup

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

Automated daily script

#!/bin/bash
BACKUP_DIR="/backups/postgres"
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 +7 -delete

Cron (02:00 daily):

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

WAL archiving for PITR

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