Pgdump_restore

Chapter 44: pg_dump & pg_restore

Backup and Restore Fundamentals

---

44.1 pg_dump

pg_dump extracts a PostgreSQL database into a script file or archive.

# Basic dump (plain SQL text)
pg_dump -U postgres -d mydb > backup.sql

# Compressed dump
pg_dump -U postgres -d mydb | gzip > backup.sql.gz

# Custom format (parallel, compressed)
pg_dump -U postgres -d mydb -Fc -f backup.dump

# Directory format (parallel)
pg_dump -U postgres -d mydb -Fd -j 4 -f backup_dir

# Only schema
pg_dump -U postgres -d mydb --schema-only > schema.sql

# Only data
pg_dump -U postgres -d mydb --data-only > data.sql

# Specific tables
pg_dump -U postgres -d mydb -t users -t orders > tables.sql

# Exclude table
pg_dump -U postgres -d mydb --exclude-table=logs > backup.sql

---

44.2 pg_restore

pg_restore restores PostgreSQL databases from pg_dump archives.

# Restore from plain SQL
psql -U postgres -d mydb < backup.sql

# Restore from custom format
pg_restore -U postgres -d mydb backup.dump

# Restore to different database
pg_restore -U postgres -d newdb backup.dump

# Restore specific tables
pg_restore -U postgres -d mydb -t users backup.dump

# Drop objects before creating
pg_restore -U postgres -d mydb --clean backup.dump

# Create database first
createdb -U postgres newdb
pg_restore -U postgres -d newdb backup.dump

---

44.3 Automation

# Daily backup script
#!/bin/bash
BACKUP_DIR="/backups"
DATE=$(date +%Y%m%d)
DB_NAME="mydb"

# Create backup
pg_dump -U postgres -d $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

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

# Verify backup
gunzip -c $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz | head -n 10

---

Summary

Command	Use
pg_dump	Create backup
pg_restore	Restore from archive
psql	Execute SQL file

---

Next: Chapter 45: Point-in-Time Recovery