PostgreSQL Backup

Backup Methods

pg_dump dbname > file.sql

pg_dump -Fc dbname > file.dump

pg_dump -Fd dbname -f dir/

pg_dumpall > all_dbs.sql

🔹 Basic Database Backup

The pg_dump utility creates logical backups of individual databases. It exports database contents as SQL commands that can recreate the database. This method is portable across PostgreSQL versions and platforms, making it ideal for migrations and simple backup needs.

-- Backup single database
pg_dump mydb > mydb_backup.sql

-- Backup with timestamp
pg_dump mydb > mydb_$(date +%Y%m%d).sql

-- Backup specific schema
pg_dump -n public mydb > public_schema.sql

-- Backup specific table
pg_dump -t employees mydb > employees_backup.sql

🔹 Compressed Backup

Compressed backups save storage space and reduce transfer time. The custom format (-Fc) provides built-in compression and allows selective restoration. Compression is especially valuable for large databases or when storing multiple backup versions.

-- Custom format (compressed)
pg_dump -Fc mydb > mydb.dump

-- Custom format with compression level
pg_dump -Fc -Z9 mydb > mydb_max_compress.dump

-- Tar format
pg_dump -Ft mydb > mydb.tar

-- Pipe to gzip
pg_dump mydb | gzip > mydb.sql.gz

🔹 Parallel Backup

Directory format enables parallel backup for faster processing of large databases. Multiple tables are backed up simultaneously using multiple CPU cores. This significantly reduces backup time for databases with many large tables.

-- Directory format with 4 parallel jobs
pg_dump -Fd -j 4 mydb -f mydb_backup_dir/

-- Parallel backup with compression
pg_dump -Fd -j 8 -Z5 mydb -f backup_dir/

-- Backup large database quickly
pg_dump -Fd -j 16 large_db -f large_db_backup/

🔹 Backup All Databases

pg_dumpall backs up all databases in a PostgreSQL cluster including global objects like roles and tablespaces. This ensures complete system backup for disaster recovery. Use this for full server backups or when migrating entire PostgreSQL installations.

-- Backup all databases
pg_dumpall > all_databases.sql

-- Backup only global objects (roles, tablespaces)
pg_dumpall --globals-only > globals.sql

-- Backup all with roles
pg_dumpall --roles-only > roles.sql

-- Complete cluster backup
pg_dumpall | gzip > cluster_backup.sql.gz

🔹 Selective Backup

Back up specific database objects for targeted data protection. Selective backups are useful for exporting specific tables, schemas, or data subsets. This approach saves time and storage when you only need portions of your database.

-- Backup specific tables
pg_dump -t employees -t departments mydb > tables.sql

-- Backup schema only (no data)
pg_dump --schema-only mydb > schema.sql

-- Backup data only (no schema)
pg_dump --data-only mydb > data.sql

-- Exclude specific tables
pg_dump --exclude-table=logs mydb > backup_no_logs.sql

🔹 Automated Backup Script

Automate backups with shell scripts for consistent data protection. Automated backups run on schedule without manual intervention, ensuring regular protection. Include rotation policies to manage storage space by removing old backups automatically.

#!/bin/bash
# Automated backup script

# Configuration
DB_NAME="mydb"
BACKUP_DIR="/backups"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$DATE.sql.gz"

# Create backup
pg_dump $DB_NAME | gzip > $BACKUP_FILE

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

echo "Backup completed: $BACKUP_FILE"

🔹 Backup Best Practices

Follow these guidelines for effective backup strategies and data protection. Proper backup practices ensure you can recover from any disaster scenario. Regular testing of backups verifies they work when needed most.