Skip to content

TimescaleDB Backup and Recovery

This guide covers comprehensive backup and recovery strategies for TimescaleDB in production environments with zero data loss requirements.

Overview

The PRS system implements a multi-layered backup strategy designed for: - Zero data loss tolerance - Multiple backup formats for maximum recovery options - Long-term data preservation - Backups designed for years of data retention - Production reliability - Automated backup procedures with verification - Fast recovery - Optimized restore procedures for minimal downtime

Backup Strategy

1. Multiple Backup Formats

The system creates multiple backup formats for maximum flexibility:

Backup Type Format Use Case Recovery Speed
Binary Backup .dump (pg_dump -Fc) Full database restore Fast
SQL Backup .sql (compressed) Selective restore, debugging Medium
Schema-Only .sql Structure recreation Fast
Data-Only .sql Data-only restore Medium

2. Backup Frequency

Backup Type Frequency Retention Purpose
Full Backup Daily 30 days Complete system recovery
Incremental Every 6 hours 7 days Point-in-time recovery
Schema Backup Weekly 90 days Structure changes tracking
Compressed Archive Monthly 1 year Long-term storage

Backup Commands

1. Standard Backup Operations

Bash
1
2
3
4
5
6
7
# Create full backup (recommended daily)
./scripts/deploy-ec2.sh timescaledb-backup

# Advanced backup with specific type
./scripts/timescaledb-production-backup.sh full    # Complete backup
./scripts/timescaledb-production-backup.sh schema  # Schema only
./scripts/timescaledb-production-backup.sh data    # Data only

2. Maintenance Backup Integration

Bash
1
2
3
4
5
6
7
8
# Backup as part of maintenance cycle
./scripts/deploy-ec2.sh timescaledb-maintenance full-maintenance

# This includes:
# - Compression optimization
# - Performance tuning
# - Backup creation
# - Health verification

3. Manual Backup Procedures

Full Database Backup

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# Create comprehensive backup
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale pg_dump \
    -U "${POSTGRES_USER}" \
    -d "${POSTGRES_DB}" \
    -Fc \
    --no-owner \
    --no-privileges \
    -f "/tmp/manual_backup_$(date +%Y%m%d_%H%M%S).dump"

# Copy from container
docker cp prs-ec2-postgres-timescale:/tmp/manual_backup_*.dump ./backups/

Schema-Only Backup

Bash
1
2
3
4
5
6
7
8
# Backup database structure
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale pg_dump \
    -U "${POSTGRES_USER}" \
    -d "${POSTGRES_DB}" \
    --schema-only \
    --no-owner \
    --no-privileges \
    -f "/tmp/schema_backup_$(date +%Y%m%d_%H%M%S).sql"

Specific Table Backup

Bash
1
2
3
4
5
6
7
# Backup specific hypertable
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale pg_dump \
    -U "${POSTGRES_USER}" \
    -d "${POSTGRES_DB}" \
    -t "audit_logs" \
    --data-only \
    -f "/tmp/audit_logs_backup_$(date +%Y%m%d_%H%M%S).sql"

Recovery Procedures

1. Full Database Recovery

From Binary Backup

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
# Stop application services
./scripts/deploy-ec2.sh stop

# Drop and recreate database
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "postgres" \
    -c "DROP DATABASE IF EXISTS ${POSTGRES_DB};"

docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "postgres" \
    -c "CREATE DATABASE ${POSTGRES_DB};"

# Restore from binary backup
docker cp ./backups/binary_YYYYMMDD_HHMMSS.dump prs-ec2-postgres-timescale:/tmp/restore.dump

docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    pg_restore -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    --no-owner --no-privileges /tmp/restore.dump

# Restart services
./scripts/deploy-ec2.sh start

From SQL Backup

Bash
1
2
3
4
# Restore from compressed SQL backup
gunzip -c ./backups/full_YYYYMMDD_HHMMSS.sql.gz | \
docker exec -i prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"

2. Selective Recovery

Schema-Only Recovery

Bash
1
2
3
4
# Restore only database structure
gunzip -c ./backups/schema_YYYYMMDD_HHMMSS.sql.gz | \
docker exec -i prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"

Table-Specific Recovery

Bash
1
2
3
4
5
6
7
8
9
# Restore specific table data
docker exec -i prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "TRUNCATE TABLE audit_logs CASCADE;"

gunzip -c ./backups/data_YYYYMMDD_HHMMSS.sql.gz | \
grep -A 10000 "COPY public.audit_logs" | \
docker exec -i prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"

3. Point-in-Time Recovery

For point-in-time recovery, combine base backup with WAL files:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# Enable WAL archiving (add to postgresql.conf)
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
wal_level = replica

# Restore to specific point in time
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    pg_restore -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    --no-owner --no-privileges \
    /tmp/base_backup.dump

# Apply WAL files up to specific time
# (This requires WAL archiving to be configured)

Backup Verification

1. Automated Verification

The backup scripts include automatic verification:

Bash
1
2
3
4
5
6
7
8
# Verify backup integrity
./scripts/timescaledb-production-backup.sh full

# Output includes:
# - Backup file sizes
# - Compression ratios
# - Integrity checks
# - Restore command examples

2. Manual Verification

Test Backup Integrity

Bash
1
2
3
4
5
6
# Test binary backup
docker exec prs-ec2-postgres-timescale \
    pg_restore --list /tmp/backup.dump > /dev/null

# Test SQL backup
gunzip -t ./backups/full_backup.sql.gz

Verify Backup Contents

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# Check backup metadata
docker exec prs-ec2-postgres-timescale \
    pg_restore --list /tmp/backup.dump | head -20

# Verify table counts
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del
        FROM pg_stat_user_tables
        ORDER BY n_tup_ins DESC;"

3. Recovery Testing

Test Recovery in Staging

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
# Create test database
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "postgres" \
    -c "CREATE DATABASE test_restore;"

# Restore backup to test database
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    pg_restore -U "${POSTGRES_USER}" -d "test_restore" \
    --no-owner --no-privileges /tmp/backup.dump

# Verify data integrity
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "test_restore" \
    -c "SELECT COUNT(*) FROM audit_logs;"

# Cleanup test database
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "postgres" \
    -c "DROP DATABASE test_restore;"

Automated Backup Schedule

1. Cron Job Configuration

Add to production server crontab (crontab -e):

Bash
1
2
3
4
5
6
7
8
# Daily full backup at 1 AM
0 1 * * * cd /home/ubuntu/prs-prod/prs-production-deployment/ec2-graviton-setup && ./scripts/deploy-ec2.sh timescaledb-backup >> /var/log/timescaledb-backup.log 2>&1

# Weekly schema backup on Sundays at 2 AM
0 2 * * 0 cd /home/ubuntu/prs-prod/prs-production-deployment/ec2-graviton-setup && ./scripts/timescaledb-production-backup.sh schema >> /var/log/timescaledb-schema-backup.log 2>&1

# Monthly comprehensive backup on 1st of month at 3 AM
0 3 1 * * cd /home/ubuntu/prs-prod/prs-production-deployment/ec2-graviton-setup && ./scripts/timescaledb-production-backup.sh full >> /var/log/timescaledb-monthly-backup.log 2>&1

2. Backup Retention Management

Bash
1
2
3
4
5
6
7
8
# Automatic cleanup of old backups (included in backup scripts)
# Daily backups: Keep 30 days
# Weekly backups: Keep 12 weeks
# Monthly backups: Keep 12 months

# Manual cleanup if needed
find ./backups -name "*.dump" -mtime +30 -delete
find ./backups -name "*.sql.gz" -mtime +30 -delete

Backup Monitoring

1. Backup Health Checks

Bash
1
2
3
4
5
6
# Check backup freshness (included in health monitoring)
./scripts/timescaledb-alerts.sh

# Manual backup verification
ls -la ./backups/ | head -10
du -sh ./backups/

2. Backup Size Monitoring

Bash
1
2
3
4
5
# Monitor backup size trends
./scripts/timescaledb-maintenance.sh storage

# Check compression effectiveness
ls -lh ./backups/*.gz | awk '{print $5, $9}' | sort -hr

3. Recovery Time Testing

Bash
1
2
3
4
5
6
7
# Measure recovery time (for capacity planning)
time ./scripts/restore-test.sh backup_file.dump

# Document recovery times for different backup sizes
# - Small backup (< 1GB): ~5-10 minutes
# - Medium backup (1-10GB): ~15-30 minutes
# - Large backup (> 10GB): ~30+ minutes

Disaster Recovery

1. Complete System Recovery

Recovery Checklist

  • Assess damage and data loss scope
  • Identify most recent valid backup
  • Prepare clean environment
  • Restore database from backup
  • Verify data integrity
  • Restart application services
  • Validate system functionality
  • Document recovery process

Emergency Recovery Procedure

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# 1. Stop all services
./scripts/deploy-ec2.sh stop

# 2. Backup current state (if possible)
./scripts/deploy-ec2.sh timescaledb-backup

# 3. Restore from latest backup
./scripts/restore-from-backup.sh latest

# 4. Verify restoration
./scripts/deploy-ec2.sh timescaledb-status

# 5. Restart services
./scripts/deploy-ec2.sh start

# 6. Run health checks
./scripts/timescaledb-alerts.sh

2. Partial Recovery Scenarios

Corrupted Table Recovery

Bash
1
2
3
4
5
6
7
8
# Identify corrupted table
./scripts/deploy-ec2.sh timescaledb-status

# Restore specific table from backup
./scripts/restore-table.sh table_name backup_file.dump

# Verify table integrity
./scripts/verify-table.sh table_name

Missing Data Recovery

Bash
1
2
3
4
5
# Identify missing data range
SELECT MIN(created_at), MAX(created_at) FROM audit_logs;

# Restore data from specific time range
./scripts/restore-time-range.sh "2024-01-01" "2024-01-31" backup_file.dump

Comprehensive backup and recovery procedures ensure your TimescaleDB data is protected and recoverable under any circumstances!