PostgreSQL Backup and Restore with pg_dump

Basic Backup with pg_dump

# Dump a single database
pg_dump -U postgres mydb > mydb_backup.sql

# Custom format (compressed, flexible restore)
pg_dump -U postgres -Fc mydb > mydb_backup.dump

# Dump specific tables
pg_dump -U postgres -t users -t orders mydb > partial_backup.sql

# Dump schema only (no data)
pg_dump -U postgres --schema-only mydb > schema.sql

# Dump data only (no schema)
pg_dump -U postgres --data-only mydb > data.sql

Dump All Databases

pg_dumpall -U postgres > all_databases.sql

Restore Methods

# Restore SQL format
psql -U postgres mydb < mydb_backup.sql

# Restore custom format (more options)
pg_restore -U postgres -d mydb mydb_backup.dump

# Restore with create database
pg_restore -U postgres -C -d postgres mydb_backup.dump

# Parallel restore (faster for large DBs)
pg_restore -U postgres -d mydb -j 4 mydb_backup.dump

Automated Backup Script

#!/bin/bash
BACKUP_DIR="/backups/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION=14

mkdir -p "$BACKUP_DIR"

# Dump all databases in custom format
for DB in $(psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
    pg_dump -U postgres -Fc "$DB" > "$BACKUP_DIR/${DB}_${DATE}.dump"
    echo "Backed up: $DB"
done

# Also dump roles and globals
pg_dumpall -U postgres --globals-only > "$BACKUP_DIR/globals_${DATE}.sql"

# Cleanup old backups
find "$BACKUP_DIR" -mtime +$RETENTION -delete

Important Flags