Backup, Restore & High Availability

-- LOGICAL BACKUPS: SQL dump files (portable, human-readable)

-- pg_dump: backup a single database
-- pg_dump -h localhost -U postgres -d mydb -F custom -f backup.dump
-- -F custom = compressed binary format (recommended)
-- -F plain  = SQL text (readable but larger)
-- -F directory = parallel dump to directory

-- Backup specific tables only:
-- pg_dump -t orders -t customers mydb > partial_backup.sql

-- Backup schema only (no data):
-- pg_dump --schema-only mydb > schema.sql

-- Backup data 
...

-- PHYSICAL BACKUPS: copy data files directly (faster for large DBs)

-- PostgreSQL: pg_basebackup
-- pg_basebackup -h localhost -U replication_user -D /backup/base
--   --checkpoint=fast --wal-method=stream -P

-- This copies the entire data directory + WAL files
-- Can be used to create replicas or point-in-time recovery

-- WAL (Write-Ahead Log) archiving for continuous backup:
-- postgresql.conf:
-- wal_level = replica
-- archive_mode = on
-- archive_command = 'cp %p /wal_archive/%f'

-- Poi
...

-- BACKUP STRATEGY: the 3-2-1 rule

-- 3 copies of your data
-- 2 different storage types (local + cloud)
-- 1 offsite copy (different region/provider)

-- Example production schedule:
-- | Backup Type    | Frequency    | Retention     |
-- |----------------|--------------|---------------|
-- | WAL archiving  | Continuous   | 7 days        |
-- | Full pg_dump   | Daily 2 AM   | 30 days       |
-- | Base backup    | Weekly       | 90 days       |
-- | Monthly export | 1st of month | 1 year       
...

-- TESTING BACKUPS: untested backups are worthless!

-- Restore to a test database and verify:
-- createdb test_restore
-- pg_restore -d test_restore production_backup.dump

-- Verification queries:
SELECT 'Table counts' AS check_type;
SELECT schemaname, tablename,
       (xpath('/row/count/text()',
        query_to_xml(format('SELECT COUNT(*) FROM %I.%I', schemaname, tablename),
        false, true, '')))[1]::text::bigint AS row_count
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablenam
...

-- HIGH AVAILABILITY: minimize downtime

-- Streaming Replication (hot standby):
-- Primary → Replica (real-time WAL streaming)
-- Replica can serve read queries
-- If primary fails → promote replica

-- Setup on PRIMARY:
-- postgresql.conf:
-- wal_level = replica
-- max_wal_senders = 5
-- synchronous_commit = on  -- for sync replication

-- pg_hba.conf:
-- host replication repl_user replica_ip/32 md5

-- Setup on REPLICA:
-- pg_basebackup -h primary_ip -U repl_user -D /data -P --slot=replica1
-
...