Database replication and high availability strategies

-- Primary server configuration (postgresql.conf)
-- wal_level = replica
-- max_wal_senders = 10
-- wal_keep_size = 64MB
-- hot_standby = on

-- Create replication user on primary
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'password';

-- pg_hba.conf on primary: Allow replication connections
-- host replication replicator replica_ip/32 md5

-- On replica: Create base backup
-- pg_basebackup -h primary_host -D /var/lib/postgresql/data -U replicator -P -R

-- Check replication status on primary
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  sync_state,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

-- Check replication lag in seconds
SELECT
  EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))
  AS replication_lag_seconds;

-- Promote replica to primary (failover)
-- pg_ctl promote -D /var/lib/postgresql/data

-- Or use SQL (PostgreSQL 12+)
SELECT pg_promote();

-- Logical replication setup
-- On primary: Create publication
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- Or publish all tables
CREATE PUBLICATION all_tables FOR ALL TABLES;

-- On subscriber: Create subscription
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=primary_host dbname=mydb user=replicator password=pass'
  PUBLICATION my_publication;

-- Check subscription status
SELECT * FROM pg_stat_subscription;

-- Monitoring replication slots
SELECT
  slot_name,
  slot_type,
  database,
  active,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots;

-- Synchronous replication configuration
-- postgresql.conf:
-- synchronous_commit = on
-- synchronous_standby_names = 'replica1,replica2'

-- This ensures commits wait for replica confirmation