PostgreSQL Backup Strategies: Complete Implementation Guide

Physical Backup (pg_basebackup):
+ Fast for large databases
+ Includes all database objects
+ Supports point-in-time recovery
- Requires same PostgreSQL version
- Platform/architecture dependent

Logical Backup (pg_dump):
+ Version independent
+ Platform independent
+ Selective backup possible
- Slower for large databases
- No point-in-time recovery

#!/bin/bash
# backup_physical.sh

BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_PATH="${BACKUP_DIR}/base_${DATE}"

# Create backup directory
mkdir -p ${BACKUP_PATH}

# Perform base backup
pg_basebackup \
    -h localhost \
    -U postgres \
    -D ${BACKUP_PATH} \
    -Ft \
    -X fetch \
    -P \
    -z

# Cleanup old backups (keep last 7 days)
find ${BACKUP_DIR} -type d -mtime +7 -exec rm -rf {} +

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 60

# Recovery configuration
restore_command = 'cp /archive/%f %p'
recovery_target_timeline = 'latest'

#!/bin/bash
# archive_wal.sh

ARCHIVE_DIR="/archive/postgresql/wal"
MAX_AGE_DAYS=7

# Archive cleanup
find ${ARCHIVE_DIR} -type f -mtime +${MAX_AGE_DAYS} -delete

# Check archive size
ARCHIVE_SIZE=$(du -sh ${ARCHIVE_DIR} | cut -f1)
echo "Current archive size: ${ARCHIVE_SIZE}"

# Alert if archive is getting too large
if [ $(du -s ${ARCHIVE_DIR} | cut -f1) -gt 1000000000 ]; then
    echo "WARNING: WAL archive is larger than 1GB" | mail -s "WAL Archive Alert" [email protected]
fi

#!/bin/bash
# backup_logical.sh

# Configuration
BACKUP_DIR="/backup/postgresql/logical"
POSTGRES_USER="postgres"
POSTGRES_HOST="localhost"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Ensure backup directory exists
mkdir -p ${BACKUP_DIR}

# Function to backup a single database
backup_database() {
    local db_name=$1
    local backup_file="${BACKUP_DIR}/${db_name}_${DATE}.custom"
    
    echo "Backing up database: ${db_name}"
    pg_dump \
        -h ${POSTGRES_HOST} \
        -U ${POSTGRES_USER} \
        -Fc \
        -v \
        -f ${backup_file} \
        ${db_name}
    
    # Compress backup
    gzip ${backup_file}
    
    echo "Backup completed: ${backup_file}.gz"
}

# Get list of databases
databases=$(psql -h ${POSTGRES_HOST} -U ${POSTGRES_USER} -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'postgres')")

# Backup each database
for db in ${databases}; do
    backup_database ${db}
done

# Cleanup old backups
find ${BACKUP_DIR} -type f -mtime +${RETENTION_DAYS} -delete

# Verify backups
echo "Verifying recent backups..."
for backup in ${BACKUP_DIR}/*_${DATE}.custom.gz; do
    gunzip -t ${backup} || echo "WARNING: Backup verification failed for ${backup}"
done

#!/bin/bash
# backup_tables.sh

# Configuration
TABLES=(
    "users"
    "orders"
    "products"
)
DB_NAME="your_database"
BACKUP_DIR="/backup/postgresql/tables"
DATE=$(date +%Y%m%d_%H%M%S)

# Backup specific tables
for table in "${TABLES[@]}"; do
    echo "Backing up table: ${table}"
    pg_dump \
        -h localhost \
        -U postgres \
        -t ${table} \
        -Fc \
        -v \
        -f "${BACKUP_DIR}/${DB_NAME}_${table}_${DATE}.custom" \
        ${DB_NAME}
done

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 60

# Recovery time configuration
recovery_target_time = '2025-01-30 12:05:06+05:30'
recovery_target_inclusive = true
recovery_target_timeline = 'latest'

#!/bin/bash
# perform_pitr.sh

# Configuration
BACKUP_DIR="/backup/postgresql"
ARCHIVE_DIR="/archive/postgresql/wal"
DATA_DIR="/var/lib/postgresql/14/main"
RECOVERY_TIME="2025-01-30 12:05:06+05:30"

# Stop PostgreSQL
systemctl stop postgresql

# Clear data directory
rm -rf ${DATA_DIR}/*

# Restore base backup
latest_backup=$(ls -t ${BACKUP_DIR}/base_* | head -1)
tar xf ${latest_backup} -C ${DATA_DIR}

# Create recovery.signal
touch ${DATA_DIR}/recovery.signal

# Create recovery configuration
cat > ${DATA_DIR}/postgresql.auto.conf << EOF
restore_command = 'cp ${ARCHIVE_DIR}/%f %p'
recovery_target_time = '${RECOVERY_TIME}'
recovery_target_inclusive = true
recovery_target_timeline = 'latest'
EOF

# Start PostgreSQL
systemctl start postgresql

# Monitor recovery progress
while true; do
    if psql -U postgres -c "SELECT pg_is_in_recovery();" | grep -q "f"; then
        echo "Recovery completed"
        break
    fi
    sleep 10
done

# backup_manager.py
import schedule
import time
import subprocess
import logging
from datetime import datetime

class PostgreSQLBackupManager:
    def __init__(self):
        self.logger = logging.getLogger('backup_manager')
        self.setup_logging()
        
    def setup_logging(self):
        logging.basicConfig(
            filename='/var/log/postgresql/backup.log',
            level=logging.INFO,
            format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
        )
    
    def perform_physical_backup(self):
        try:
            subprocess.run(['/scripts/backup_physical.sh'], check=True)
            self.logger.info('Physical backup completed successfully')
        except subprocess.CalledProcessError as e:
            self.logger.error(f'Physical backup failed: {str(e)}')
    
    def perform_logical_backup(self):
        try:
            subprocess.run(['/scripts/backup_logical.sh'], check=True)
            self.logger.info('Logical backup completed successfully')
        except subprocess.CalledProcessError as e:
            self.logger.error(f'Logical backup failed: {str(e)}')
    
    def verify_backups(self):
        try:
            subprocess.run(['/scripts/verify_backups.sh'], check=True)
            self.logger.info('Backup verification completed')
        except subprocess.CalledProcessError as e:
            self.logger.error(f'Backup verification failed: {str(e)}')
    
    def cleanup_old_backups(self):
        try:
            subprocess.run(['/scripts/cleanup_backups.sh'], check=True)
            self.logger.info('Backup cleanup completed')
        except subprocess.CalledProcessError as e:
            self.logger.error(f'Backup cleanup failed: {str(e)}')

# Schedule backups
backup_manager = PostgreSQLBackupManager()

# Physical backups daily at 1 AM
schedule.every().day.at("01:00").do(backup_manager.perform_physical_backup)

# Logical backups daily at 2 AM
schedule.every().day.at("02:00").do(backup_manager.perform_logical_backup)

# Verify backups daily at 3 AM
schedule.every().day.at("03:00").do(backup_manager.verify_backups)

# Cleanup old backups weekly on Sunday at 4 AM
schedule.every().sunday.at("04:00").do(backup_manager.cleanup_old_backups)

while True:
    schedule.run_pending()
    time.sleep(60)

#!/bin/bash
# verify_backups.sh

# Configuration
BACKUP_DIR="/backup/postgresql"
TEST_DIR="/tmp/postgresql_test"
LOG_FILE="/var/log/postgresql/backup_verification.log"

log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> ${LOG_FILE}
}

verify_physical_backup() {
    local backup_file=$1
    
    # Create test directory
    mkdir -p ${TEST_DIR}
    
    # Extract backup
    tar xf ${backup_file} -C ${TEST_DIR}
    
    # Test PostgreSQL start
    pg_ctl -D ${TEST_DIR} start -w -t 60
    if [ $? -eq 0 ]; then
        log "Physical backup verification successful: ${backup_file}"
        pg_ctl -D ${TEST_DIR} stop
    else
        log "ERROR: Physical backup verification failed: ${backup_file}"
    fi
    
    # Cleanup
    rm -rf ${TEST_DIR}
}

verify_logical_backup() {
    local backup_file=$1
    local test_db="verify_$(date +%s)"
    
    # Create test database
    createdb ${test_db}
    
    # Restore backup
    pg_restore -d ${test_db} ${backup_file}
    if [ $? -eq 0 ]; then
        log "Logical backup verification successful: ${backup_file}"
    else
        log "ERROR: Logical backup verification failed: ${backup_file}"
    fi
    
    # Cleanup
    dropdb ${test_db}
}

# Verify recent backups
for backup in ${BACKUP_DIR}/base_*.tar.gz; do
    verify_physical_backup ${backup}
done

for backup in ${BACKUP_DIR}/*.custom; do
    verify_logical_backup ${backup}
done