PostgreSQL Backup and Restore on Linux

By /

Databases are special. Regular file backups don’t work well for PostgreSQL — backing up the data directory while the server is running is likely to give you a corrupt snapshot. You need PostgreSQL-aware tools.

Here’s what I actually use for backing up PostgreSQL databases, from simple single-database dumps to continuous archiving.

pg_dump — The Standard Tool

For most situations, pg_dump is the right choice. It exports a consistent snapshot of a database while the server keeps running. No downtime needed.

# Dump to SQL format
pg_dump -U postgres mydb > /backup/mydb_$(date +%Y%m%d).sql

# Dump to compressed custom format (recommended)
pg_dump -U postgres -Fc mydb > /backup/mydb_$(date +%Y%m%d).dump

# Dump with explicit host
pg_dump -h localhost -U postgres -Fc mydb > /backup/mydb.dump

The custom format (-Fc) is compressed and supports parallel restore. I use it for most backups. The plain SQL format is more portable — it’s readable text and can be piped into any PostgreSQL client.

Restoring

From SQL format:

psql -U postgres mydb < /backup/mydb_20240101.sql

From custom format:

pg_restore -U postgres -d mydb /backup/mydb_20240101.dump

# Drop and recreate the database first if needed
dropdb -U postgres mydb
createdb -U postgres mydb
pg_restore -U postgres -d mydb /backup/mydb_20240101.dump

Custom format restore with multiple workers (faster for large databases):

pg_restore -U postgres -d mydb -j 4 /backup/mydb.dump

-j 4 uses 4 parallel workers. On a multi-core server with a large database, this can cut restore time significantly.

Backing Up All Databases

pg_dumpall dumps all databases in the cluster, including roles and tablespaces:

pg_dumpall -U postgres > /backup/all_databases_$(date +%Y%m%d).sql

This is what I use for full cluster backups. The output is SQL only (no custom format for pg_dumpall), which means it’s readable but not compressed. Pipe through gzip:

pg_dumpall -U postgres | gzip > /backup/all_databases_$(date +%Y%m%d).sql.gz

Restore:

gunzip -c /backup/all_databases_20240101.sql.gz | psql -U postgres

Backup Script

Here’s what I run daily via cron:

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/var/backup/postgresql"
DATE=$(date +%Y-%m-%d)
KEEP_DAYS=14

mkdir -p "$BACKUP_DIR"

# Get list of databases (excluding templates)
DATABASES=$(psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';")

for DB in $DATABASES; do
    BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.dump"
    echo "Backing up: $DB"
    pg_dump -U postgres -Fc "$DB" > "$BACKUP_FILE"
    echo "  Done: $BACKUP_FILE ($(du -sh "$BACKUP_FILE" | cut -f1))"
done

# Also dump all with roles
pg_dumpall -U postgres --globals-only | gzip > "$BACKUP_DIR/globals_${DATE}.sql.gz"

# Clean up old backups
find "$BACKUP_DIR" -name "*.dump" -mtime +$KEEP_DAYS -delete
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_DAYS -delete

echo "Backup complete"

Add to cron:

0 3 * * * /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1

Remote Backups

Dump directly to a remote server without creating local files:

pg_dump -U postgres -Fc mydb | ssh backup@remote "cat > /backup/mydb_$(date +%Y%m%d).dump"

Or use rsync after creating local dumps to push to a remote backup server — keeping both a local copy (fast restore) and a remote copy (disaster recovery).

.pgpass for Non-Interactive Authentication

For scripts running as non-postgres users or connecting with passwords, create ~/.pgpass:

hostname:port:database:username:password
localhost:5432:mydb:dbuser:secretpassword
*:*:*:backupuser:backuppassword
chmod 600 ~/.pgpass

PostgreSQL reads this file and uses the matching password automatically. No password prompts in scripts.

Point-in-Time Recovery (PITR)

pg_dump gives you daily snapshots. If the database fails at 4pm and your last dump was at 3am, you lose the day’s data. For many applications, that’s unacceptable.

PITR solves this with continuous archiving: PostgreSQL continuously archives Write-Ahead Log (WAL) files, and you can restore to any point in time by replaying WAL from a base backup.

Enable WAL archiving in postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/archive/%f'

Take a base backup:

pg_basebackup -U postgres -D /backup/basebackup -Ft -z -P

To restore to a point in time:

  1. Stop PostgreSQL
  2. Restore the base backup
  3. Create /var/lib/postgresql/data/recovery.conf (PostgreSQL 11 and earlier) or postgresql.conf with recovery settings (PostgreSQL 12+):
restore_command = 'cp /var/archive/%f %p'
recovery_target_time = '2024-01-15 14:30:00'
  1. Start PostgreSQL — it replays WAL up to the target time

PITR setup is more involved but provides a much stronger recovery position for production databases. I set this up on any database where losing more than a few hours of data would be a problem.

Monitoring Backup Health

I check that backups are actually being created and aren’t empty:

# Check most recent backup is less than 25 hours old
find /var/backup/postgresql -name "*.dump" -mtime -1 | wc -l

If that returns 0, something went wrong. Wire this into your monitoring system — a failed backup that you don’t notice until you need it is a bad surprise.

Also verify backups are restorable. Occasionally I restore a dump to a test database to confirm it actually works:

createdb -U postgres test_restore
pg_restore -U postgres -d test_restore /backup/mydb_20240101.dump
psql -U postgres test_restore -c "SELECT count(*) FROM important_table;"
dropdb -U postgres test_restore

A backup you’ve never tested isn’t a backup — it’s a file that might be a backup.

Backup Size and Compression

Custom format dumps are already compressed. For SQL dumps, gzip is standard:

pg_dump -U postgres mydb | gzip > mydb.sql.gz

For better compression ratios, zstd or xz compress better than gzip at roughly the same speed or faster:

pg_dump -U postgres mydb | zstd > mydb.sql.zst

On large databases the size difference matters for storage and transfer time. I’ve seen 30–50% smaller archives with zstd compared to gzip.

For an automated approach to backing up other critical server files alongside your databases, rsync for backups covers the file synchronization side of a complete backup strategy.

Related Posts

Scroll to Top