Implement TimescaleDB backup strategies and point-in-time recovery with automated failover

Install TimescaleDB and backup dependencies

Install TimescaleDB extension and PostgreSQL backup utilities for comprehensive backup management.

curl -s https://packagecloud.io/install/repositories/timescale/timescaledb/script.deb.sh | sudo bash
sudo apt update
sudo apt install -y timescaledb-2-postgresql-16 postgresql-client-common pigz pv

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y timescaledb_16 postgresql16-server pigz pv

Configure PostgreSQL for WAL archiving

Enable continuous WAL archiving to support point-in-time recovery and streaming replication.

# WAL configuration for continuous archiving
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 300

# Checkpoint and WAL settings
checkpoint_timeout = 5min
max_wal_size = 2GB
min_wal_size = 80MB
checkpoint_completion_target = 0.9

# Logging for backup monitoring
log_destination = 'csvlog'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB

Create WAL archive directory structure

Set up directories for WAL archives and backups with proper permissions for the postgres user.

sudo mkdir -p /var/lib/postgresql/wal_archive
sudo mkdir -p /var/lib/postgresql/backups
sudo mkdir -p /var/lib/postgresql/pitr_recovery
sudo chown -R postgres:postgres /var/lib/postgresql/wal_archive
sudo chown -R postgres:postgres /var/lib/postgresql/backups
sudo chown -R postgres:postgres /var/lib/postgresql/pitr_recovery
sudo chmod 750 /var/lib/postgresql/wal_archive
sudo chmod 750 /var/lib/postgresql/backups

Configure TimescaleDB backup script

Create a comprehensive backup script that handles base backups with compression and metadata logging.

#!/bin/bash

# TimescaleDB Backup Script with Compression and Validation
set -euo pipefail

# Configuration
BACKUP_DIR="/var/lib/postgresql/backups"
WAL_ARCHIVE_DIR="/var/lib/postgresql/wal_archive"
DB_NAME="${1:-timescaledb}"
RETENTION_DAYS=7
COMPRESSION_LEVEL=6
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_NAME="${DB_NAME}_backup_${TIMESTAMP}"
LOG_FILE="/var/log/postgresql/backup_${TIMESTAMP}.log"

# Logging function
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

# Error handling
trap 'log "ERROR: Backup failed at line $LINENO"' ERR

log "Starting TimescaleDB backup: $BACKUP_NAME"

# Create backup directory
mkdir -p "${BACKUP_DIR}/${BACKUP_NAME}"

# Perform base backup with compression
log "Creating base backup with pg_basebackup"
pg_basebackup -D "${BACKUP_DIR}/${BACKUP_NAME}" \
              -Ft -z -Z$COMPRESSION_LEVEL \
              -P -v -W \
              -X stream \
              --label="TimescaleDB_backup_${TIMESTAMP}" 2>&1 | tee -a "$LOG_FILE"

# Create backup metadata
log "Creating backup metadata"
cat > "${BACKUP_DIR}/${BACKUP_NAME}/backup_info.txt" << EOF
Backup Name: $BACKUP_NAME
Database: $DB_NAME
Timestamp: $TIMESTAMP
Backup Type: Full Base Backup
Compression: Level $COMPRESSION_LEVEL
WAL Archive Location: $WAL_ARCHIVE_DIR
Backup Size: $(du -sh "${BACKUP_DIR}/${BACKUP_NAME}" | cut -f1)
PostgreSQL Version: $(psql -t -c "SELECT version();" | head -1)
TimescaleDB Version: $(psql -d $DB_NAME -t -c "SELECT extversion FROM pg_extension WHERE extname='timescaledb';" | head -1)
EOF

# Validate backup integrity
log "Validating backup integrity"
if [ -f "${BACKUP_DIR}/${BACKUP_NAME}/base.tar.gz" ]; then
    tar -tzf "${BACKUP_DIR}/${BACKUP_NAME}/base.tar.gz" >/dev/null 2>&1
    log "Base backup validation: PASSED"
else
    log "ERROR: Base backup file not found"
    exit 1
fi

# Cleanup old backups
log "Cleaning up backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR" -name "${DB_NAME}_backup_*" -type d -mtime +$RETENTION_DAYS -exec rm -rf {} +

# Cleanup old WAL archives (keep 2x retention period)
find "$WAL_ARCHIVE_DIR" -name "*.gz" -type f -mtime +$((RETENTION_DAYS * 2)) -delete

log "Backup completed successfully: $BACKUP_NAME"
log "Total backup size: $(du -sh "${BACKUP_DIR}/${BACKUP_NAME}" | cut -f1)"

exit 0

sudo chmod 755 /usr/local/bin/timescale-backup.sh
sudo chown postgres:postgres /usr/local/bin/timescale-backup.sh

Implement point-in-time recovery script

Create an automated PITR script that can restore to any point in time within the WAL archive retention period.

#!/bin/bash

# TimescaleDB Point-in-Time Recovery Script
set -euo pipefail

# Configuration
BACKUP_DIR="/var/lib/postgresql/backups"
WAL_ARCHIVE_DIR="/var/lib/postgresql/wal_archive"
RECOVERY_DIR="/var/lib/postgresql/pitr_recovery"
POSTGRES_DATA_DIR="/var/lib/postgresql/16/main"
TARGET_TIME="${1:-}"
BACKUP_NAME="${2:-}"
RECOVERY_TARGET_ACTION="promote"

# Validation
if [[ -z "$TARGET_TIME" ]]; then
    echo "Usage: $0 'YYYY-MM-DD HH:MM:SS' [backup_name]"
    echo "Example: $0 '2024-01-15 14:30:00'"
    exit 1
fi

# Get latest backup if not specified
if [[ -z "$BACKUP_NAME" ]]; then
    BACKUP_NAME=$(find "$BACKUP_DIR" -name "*_backup_*" -type d | sort | tail -1 | basename)
    if [[ -z "$BACKUP_NAME" ]]; then
        echo "ERROR: No backups found in $BACKUP_DIR"
        exit 1
    fi
fi

echo "Starting Point-in-Time Recovery"
echo "Target Time: $TARGET_TIME"
echo "Using Backup: $BACKUP_NAME"
echo "Recovery Directory: $RECOVERY_DIR"

# Stop PostgreSQL service
echo "Stopping PostgreSQL service"
sudo systemctl stop postgresql

# Clean recovery directory
echo "Preparing recovery directory"
rm -rf "$RECOVERY_DIR"/*
mkdir -p "$RECOVERY_DIR"

# Extract base backup
echo "Extracting base backup"
cd "$RECOVERY_DIR"
tar -xzf "${BACKUP_DIR}/${BACKUP_NAME}/base.tar.gz"
if [ -f "${BACKUP_DIR}/${BACKUP_NAME}/pg_wal.tar.gz" ]; then
    mkdir -p pg_wal
    tar -xzf "${BACKUP_DIR}/${BACKUP_NAME}/pg_wal.tar.gz" -C pg_wal
fi

# Create recovery configuration
echo "Creating recovery configuration"
cat > "$RECOVERY_DIR/postgresql.auto.conf" << EOF
# Point-in-Time Recovery Configuration
restore_command = 'cp $WAL_ARCHIVE_DIR/%f %p'
recovery_target_time = '$TARGET_TIME'
recovery_target_action = '$RECOVERY_TARGET_ACTION'
EOF

# Create recovery signal file
touch "$RECOVERY_DIR/recovery.signal"

# Set proper permissions
chown -R postgres:postgres "$RECOVERY_DIR"
chmod 700 "$RECOVERY_DIR"

echo "Recovery setup completed"
echo "To complete recovery:"
echo "1. Backup current data directory: sudo mv $POSTGRES_DATA_DIR ${POSTGRES_DATA_DIR}.backup"
echo "2. Move recovery directory: sudo mv $RECOVERY_DIR $POSTGRES_DATA_DIR"
echo "3. Start PostgreSQL: sudo systemctl start postgresql"
echo "4. Monitor recovery in PostgreSQL logs"

exit 0

sudo chmod 755 /usr/local/bin/timescale-pitr.sh
sudo chown postgres:postgres /usr/local/bin/timescale-pitr.sh

Configure automated backup scheduling

Set up systemd service and timer for automated daily backups with proper logging and error handling.

[Unit]
Description=TimescaleDB Automated Backup Service
After=postgresql.service
Requires=postgresql.service

[Service]
Type=oneshot
User=postgres
Group=postgres
ExecStart=/usr/local/bin/timescale-backup.sh timescaledb
Environment=PGUSER=postgres
Environment=PGDATABASE=timescaledb
StandardOutput=journal
StandardError=journal

[Install]
WantedBy=multi-user.target

[Unit]
Description=TimescaleDB Backup Timer
Requires=timescale-backup.service

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true
RandomizedDelaySec=300

[Install]
WantedBy=timers.target

sudo systemctl daemon-reload
sudo systemctl enable timescale-backup.timer
sudo systemctl start timescale-backup.timer

Implement backup monitoring and alerting

Create monitoring script to validate backup integrity and send alerts on failures.

#!/bin/bash

# TimescaleDB Backup Monitoring Script
set -euo pipefail

BACKUP_DIR="/var/lib/postgresql/backups"
WAL_ARCHIVE_DIR="/var/lib/postgresql/wal_archive"
MAX_BACKUP_AGE_HOURS=26  # Allow up to 26 hours for daily backups
MAX_WAL_AGE_MINUTES=10   # WAL should be archived within 10 minutes
ALERT_EMAIL="admin@example.com"
SMTP_SERVER="localhost"

# Check latest backup age
check_backup_age() {
    local latest_backup
    latest_backup=$(find "$BACKUP_DIR" -name "*_backup_*" -type d -printf '%T@ %p\n' | sort -n | tail -1 | cut -d' ' -f2-)
    
    if [[ -z "$latest_backup" ]]; then
        echo "CRITICAL: No backups found in $BACKUP_DIR"
        return 1
    fi
    
    local backup_time
    backup_time=$(stat -c %Y "$latest_backup")
    local current_time
    current_time=$(date +%s)
    local age_hours
    age_hours=$(( (current_time - backup_time) / 3600 ))
    
    if [[ $age_hours -gt $MAX_BACKUP_AGE_HOURS ]]; then
        echo "CRITICAL: Latest backup is $age_hours hours old (max: $MAX_BACKUP_AGE_HOURS)"
        return 1
    fi
    
    echo "OK: Latest backup is $age_hours hours old"
    return 0
}

# Check WAL archiving
check_wal_archiving() {
    local latest_wal
    latest_wal=$(find "$WAL_ARCHIVE_DIR" -name "*.gz" -type f -printf '%T@ %p\n' | sort -n | tail -1 | cut -d' ' -f2-)
    
    if [[ -z "$latest_wal" ]]; then
        echo "WARNING: No WAL archives found"
        return 1
    fi
    
    local wal_time
    wal_time=$(stat -c %Y "$latest_wal")
    local current_time
    current_time=$(date +%s)
    local age_minutes
    age_minutes=$(( (current_time - wal_time) / 60 ))
    
    if [[ $age_minutes -gt $MAX_WAL_AGE_MINUTES ]]; then
        echo "WARNING: Latest WAL archive is $age_minutes minutes old (max: $MAX_WAL_AGE_MINUTES)"
        return 1
    fi
    
    echo "OK: WAL archiving is current ($age_minutes minutes ago)"
    return 0
}

# Send alert email
send_alert() {
    local subject="$1"
    local message="$2"
    
    if command -v mail >/dev/null 2>&1; then
        echo "$message" | mail -s "$subject" "$ALERT_EMAIL"
    elif command -v sendmail >/dev/null 2>&1; then
        {
            echo "Subject: $subject"
            echo "To: $ALERT_EMAIL"
            echo ""
            echo "$message"
        } | sendmail "$ALERT_EMAIL"
    else
        echo "No mail command available for alerts"
    fi
}

# Main monitoring logic
main() {
    local backup_status=0
    local wal_status=0
    local backup_result
    local wal_result
    
    backup_result=$(check_backup_age) || backup_status=1
    wal_result=$(check_wal_archiving) || wal_status=1
    
    echo "Backup Status: $backup_result"
    echo "WAL Status: $wal_result"
    
    if [[ $backup_status -ne 0 ]] || [[ $wal_status -ne 0 ]]; then
        local alert_message
        alert_message="TimescaleDB Backup Alert\n\nBackup Status: $backup_result\nWAL Status: $wal_result\n\nTime: $(date)\nHost: $(hostname)"
        send_alert "TimescaleDB Backup Alert - $(hostname)" "$alert_message"
        exit 1
    fi
    
    echo "All backup systems operating normally"
    exit 0
}

main "$@"

sudo chmod 755 /usr/local/bin/timescale-backup-monitor.sh
sudo chown postgres:postgres /usr/local/bin/timescale-backup-monitor.sh

Configure monitoring systemd service

Set up automated monitoring checks every hour to ensure backup systems are functioning properly.

[Unit]
Description=TimescaleDB Backup Monitoring Service
After=postgresql.service

[Service]
Type=oneshot
User=postgres
Group=postgres
ExecStart=/usr/local/bin/timescale-backup-monitor.sh
StandardOutput=journal
StandardError=journal

[Unit]
Description=TimescaleDB Backup Monitoring Timer
Requires=timescale-backup-monitor.service

[Timer]
OnBootSec=15min
OnUnitActiveSec=1h
Persistent=true

[Install]
WantedBy=timers.target

sudo systemctl daemon-reload
sudo systemctl enable timescale-backup-monitor.timer
sudo systemctl start timescale-backup-monitor.timer

Restart PostgreSQL with new configuration

Apply the WAL archiving configuration by restarting the PostgreSQL service.

sudo systemctl restart postgresql
sudo systemctl status postgresql

Initialize TimescaleDB in test database

Create a test database with TimescaleDB extension to verify backup functionality.

sudo -u postgres createdb timescaledb
sudo -u postgres psql -d timescaledb -c "CREATE EXTENSION IF NOT EXISTS timescaledb;"
sudo -u postgres psql -d timescaledb -c "SELECT timescaledb_pre_restore();"
sudo -u postgres psql -d timescaledb -c "SELECT timescaledb_post_restore();"

Perform initial backup test

Execute the backup script manually to verify functionality and generate baseline backup.

sudo -u postgres /usr/local/bin/timescale-backup.sh timescaledb

Verify WAL archiving

Check that WAL files are being archived automatically by PostgreSQL.

sudo -u postgres psql -c "SELECT pg_switch_wal();"
sleep 30
ls -la /var/lib/postgresql/wal_archive/

Test point-in-time recovery setup

Prepare PITR configuration without actually performing recovery to validate the process.

sudo -u postgres /usr/local/bin/timescale-pitr.sh "$(date '+%Y-%m-%d %H:%M:%S')" --dry-run

Implement advanced WAL cleanup

Create intelligent WAL archive cleanup that respects backup retention periods and recovery requirements.

#!/bin/bash

# TimescaleDB WAL Archive Cleanup Script
set -euo pipefail

WAL_ARCHIVE_DIR="/var/lib/postgresql/wal_archive"
BACKUP_DIR="/var/lib/postgresql/backups"
RETENTION_DAYS=14
DRY_RUN=${1:-false}

# Find oldest backup to determine safe cleanup point
oldest_backup=$(find "$BACKUP_DIR" -name "*_backup_*" -type d -printf '%T@ %p\n' | sort -n | head -1 | cut -d' ' -f2-)

if [[ -n "$oldest_backup" ]]; then
    # Get backup start WAL from backup_label
    backup_start_wal=$(find "$oldest_backup" -name "backup_label*" -exec cat {} \; | grep "START WAL LOCATION" | awk '{print $4}' | head -1)
    
    if [[ -n "$backup_start_wal" ]]; then
        echo "Oldest backup start WAL: $backup_start_wal"
        
        # Remove WAL files older than oldest backup (safely)
        find "$WAL_ARCHIVE_DIR" -name "*.gz" -type f -mtime +$RETENTION_DAYS | while read -r wal_file; do
            if [[ "$DRY_RUN" == "true" ]]; then
                echo "Would remove: $wal_file"
            else
                echo "Removing old WAL: $wal_file"
                rm -f "$wal_file"
            fi
        done
    fi
fi

echo "WAL cleanup completed"

sudo chmod 755 /usr/local/bin/timescale-wal-cleanup.sh
sudo chown postgres:postgres /usr/local/bin/timescale-wal-cleanup.sh

Schedule WAL cleanup automation

Configure daily WAL cleanup to maintain optimal archive sizes while preserving recovery capabilities.

[Unit]
Description=TimescaleDB WAL Archive Cleanup Service
After=postgresql.service

[Service]
Type=oneshot
User=postgres
Group=postgres
ExecStart=/usr/local/bin/timescale-wal-cleanup.sh
StandardOutput=journal
StandardError=journal

[Unit]
Description=TimescaleDB WAL Cleanup Timer
Requires=timescale-wal-cleanup.service

[Timer]
OnCalendar=*-*-* 03:00:00
Persistent=true
RandomizedDelaySec=600