Flags need to be adjusted depending on the specific requirements and environment.

PostgreSQL Dump and Restore Guide

Basic Database Dump

Text Format Dump (SQL)

# Dump entire database
pg_dump dbname > backup.sql

# Dump with owner information
pg_dump -O dbname > backup.sql

# Dump specific schema
pg_dump -n schema_name dbname > backup.sql

# Dump specific table
pg_dump -t table_name dbname > backup.sql

Binary Format Dump (Custom Format - Recommended)

# Create binary dump (allows parallel restore and selective restore)
pg_dump -Fc dbname > backup.dump

# With compression level (0-9, default is usually 5)
pg_dump -Fc -Z9 dbname > backup.dump

# Directory format (allows parallel dump)
pg_dump -Fd dbname -f backup_dir -j 4

Offline Dumps (Database Cluster)

Stop PostgreSQL Service

# Stop the service
sudo systemctl stop postgresql
# or
sudo service postgresql stop

Physical Backup (Binary Copy)

# Copy entire data directory
sudo tar -czf postgresql_backup.tar.gz /var/lib/postgresql/{version}/main/

# Or use rsync for incremental backups
sudo rsync -av /var/lib/postgresql/{version}/main/ /backup/location/

Restart PostgreSQL

sudo systemctl start postgresql

Guaranteed Restore with Binary Format

Using Custom Format (-Fc)

# Basic restore
pg_restore -d dbname backup.dump

# Drop existing objects before restore
pg_restore -d dbname --clean backup.dump

# Create database and restore
createdb newdb
pg_restore -d newdb backup.dump

# Restore with parallel jobs (faster)
pg_restore -d dbname -j 4 backup.dump

# Restore specific schema only
pg_restore -d dbname -n schema_name backup.dump

# Restore specific table only
pg_restore -d dbname -t table_name backup.dump

# Restore with verbose output
pg_restore -d dbname -v backup.dump

Using Directory Format (-Fd)

# Restore from directory with parallel processing
pg_restore -d dbname -j 4 backup_dir/

Complete Backup and Restore Workflow

Full Cluster Backup (All Databases)

# Dump all databases in binary format
pg_dumpall > all_databases.sql

# Or dump globals and individual databases
pg_dumpall --globals-only > globals.sql
pg_dump -Fc database1 > db1.dump
pg_dump -Fc database2 > db2.dump

Guaranteed Restore Process

# 1. Create new database
createdb -T template0 newdb

# 2. Restore using binary format with error checking
pg_restore -d newdb -v --single-transaction backup.dump

# 3. Verify restoration
psql -d newdb -c "\dt"
psql -d newdb -c "SELECT count(*) FROM important_table;"

Best Practices for Production

Create Consistent Backup Script

#!/bin/bash
BACKUP_DIR="/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="production_db"

# Create backup with timestamp
pg_dump -Fc -Z9 ${DB_NAME} > ${BACKUP_DIR}/${DB_NAME}_${DATE}.dump

# Keep only last 7 days
find ${BACKUP_DIR} -name "${DB_NAME}_*.dump" -mtime +7 -delete

Test Restore Regularly

# Restore to test database
createdb test_restore
pg_restore -d test_restore latest_backup.dump

# Verify data integrity
psql -d test_restore -c "SELECT count(*) FROM critical_table;"

# Drop test database
dropdb test_restore

Advantages of Binary Format

• Selective restore: Can restore specific tables or schemas
• Parallel processing: Faster backup and restore with -j flag
• Compressed: Built-in compression
• Cross-version compatible: Works across minor PostgreSQL versions
• Transactional restore: Can use --single-transaction for atomic restore
• Portable: Binary format is portable across architectures

Common Options Summary