/pg-backup - Database Backup

Production-grade backup with verification and multiple formats

Usage

/pg-backup [database]
/pg-backup --format custom --compress
/pg-backup --parallel 4 --format directory

Options

Option	Description	Default
--format	Output format	custom
--compress	Enable compression	true
--parallel	Parallel jobs	1
--schema-only	Schema without data	false
--data-only	Data without schema	false
--verify	Verify backup integrity	true
--output	Output path	./backup/

Backup Formats

Format	Extension	Use Case	Restore Speed
custom	.dump	Standard, recommended	Fast
directory	folder	Large DBs, parallel	Fastest
plain	.sql	Portable, readable	Slow
tar	.tar	Archive compatible	Medium

Backup Commands

Custom Format (Recommended)

pg_dump -h $HOST -U $USER -Fc -Z 6 \
    -f backup_$(date +%Y%m%d_%H%M%S).dump $DATABASE

Directory Format (Large DBs)

pg_dump -h $HOST -U $USER -Fd -j 4 \
    -f backup_$(date +%Y%m%d_%H%M%S) $DATABASE

With Timestamp and Compression

pg_dump -h $HOST -U $USER -Fc $DATABASE | \
    gzip > backup_${DATABASE}_$(date +%Y%m%d_%H%M%S).dump.gz

Schema Only

pg_dump -h $HOST -U $USER --schema-only -f schema.sql $DATABASE

Verification Steps

# 1. Check file size (should be > 0)
ls -lh backup.dump

# 2. List contents without restoring
pg_restore --list backup.dump | head -20

# 3. Test restore to temp database
createdb temp_verify
pg_restore -d temp_verify backup.dump
dropdb temp_verify

Output Format

PostgreSQL Backup
=================
Database: mydb @ localhost:5432
Started: 2024-12-30T10:00:00Z
Format: custom (compressed)

Progress:
  [============================] 100%
  Tables: 45/45
  Size: 1.2GB

Backup Complete:
  File: ./backup/mydb_20241230_100000.dump
  Size: 450MB (compressed from 1.2GB)
  Duration: 2m 34s
  Compression: 62.5%

Verification:
  [OK] File integrity check passed
  [OK] Content listing successful
  [OK] 45 tables, 12 indexes, 8 functions

Exit code: 0

Backup Strategy Recommendations

Frequency	Type	Retention
Hourly	WAL archive	24 hours
Daily	Full pg_dump	7 days
Weekly	Full pg_basebackup	4 weeks
Monthly	Off-site archive	12 months

Automation Script

#!/bin/bash
# Daily backup with rotation

BACKUP_DIR="/backups/postgresql"
RETENTION_DAYS=7
DATABASE="mydb"

# Create backup
BACKUP_FILE="${BACKUP_DIR}/${DATABASE}_$(date +%Y%m%d).dump"
pg_dump -Fc -f "$BACKUP_FILE" "$DATABASE"

# Verify
pg_restore --list "$BACKUP_FILE" > /dev/null 2>&1
if [ $? -eq 0 ]; then
    echo "Backup verified: $BACKUP_FILE"
else
    echo "Backup verification failed!"
    exit 1
fi

# Cleanup old backups
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete

Examples

# Standard backup
/pg-backup mydb

# Fast parallel backup
/pg-backup mydb --format directory --parallel 4

# Schema only for version control
/pg-backup mydb --schema-only --format plain --output ./schema.sql

# Full backup with verification
/pg-backup mydb --verify --compress

Troubleshooting

Issue	Cause	Resolution
Permission denied	Missing privileges	GRANT pg_read_all_data
Disk full	Insufficient space	Free space or use --compress
Timeout	Large database	Use --parallel
Locked tables	Active transactions	Schedule during low traffic

Exit Codes

Code	Meaning
0	Backup and verification successful
1	Backup failed
2	Backup completed, verification failed