HeliosDB Backup & Recovery Guide

Version: 1.0 Last Updated: 2025-11-30

Backup Strategies

Full Backup

-- One-time full backup
SELECT pg_basebackup(
  directory => '/backups/full_backup',
  format => 'tar',
  compression => 'gzip'
);

-- Scheduled backups
CREATE BACKUP SCHEDULE daily_backup AS
  FREQUENCY = '1 day'
  START TIME = '02:00'
  RETENTION = '30 days'
  STORAGE LOCATION = '/backups/daily';

Incremental Backup

-- Incremental backups (faster)
ALTER BACKUP SCHEDULE daily_backup SET
  TYPE = 'INCREMENTAL',
  FULL_BACKUP_FREQUENCY = '7 days';

PITR (Point-in-Time Recovery)

-- Enable WAL archiving for PITR
ALTER SYSTEM SET
  wal_level = replica,
  archive_mode = on,
  archive_command = 'cp %p /archive/%f';

SELECT pg_reload_conf();

Restore Operations

Full Restore

# Stop the server
systemctl stop postgresql

# Restore from backup
pg_basebackup -D /var/lib/postgresql/data -r /backups/full_backup

# Start server
systemctl start postgresql

Point-in-Time Restore

-- Restore to specific time
SELECT restore_to_point_in_time(
  target_timeline => 'latest',
  target_time => '2025-01-15 14:30:00'::TIMESTAMP,
  recovery_target_action => 'promote'
);

-- Restart with recovery
pg_ctl restart -D /data -c recovery_target_timeline=latest

Selective Restore

-- Restore specific table
SELECT restore_table(
  table_name => 'important_data',
  from_backup => '/backups/before_incident.tar',
  to_timestamp => '2025-01-15 10:00:00'::TIMESTAMP
);

Monitoring Backups

-- Backup history
SELECT
  backup_id,
  backup_type,
  backup_size_gb,
  backup_time,
  backup_duration_minutes,
  status,
  retention_until
FROM backup_history
ORDER BY backup_time DESC;

-- Backup health check
SELECT * FROM backup_health_check;

Testing Backups

-- Verify backup integrity
SELECT verify_backup('/backups/full_backup');

-- Test restore (non-destructive)
SELECT test_restore_backup('/backups/full_backup');

Best Practices

Regular full backups (daily)
Incremental backups (hourly)
Test restores monthly
Store backups off-site
Monitor backup size
Verify backup integrity
Document restore procedures

Related Documentation: