Replication: Sync, Async, Failover & Clustering

-- ASYNCHRONOUS REPLICATION
-- Primary commits immediately, replica catches up later
-- Fastest for writes, but replica may lag behind

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

-- Check lag on replica:
SELECT NOW() - pg_last_xact_replay_timestamp() AS replication_lag;

-- If lag > acceptable threshold, i
...

-- SYNCHRONOUS REPLICATION
-- Primary waits for replica to confirm before committing
-- Zero data loss, but slower writes

-- postgresql.conf on primary:
-- synchronous_standby_names = 'replica1'
-- synchronous_commit = on

-- Levels of synchronous commit:
-- | Level              | Speed  | Durability           |
-- |--------------------|--------|----------------------|
-- | off                | Fastest | May lose recent txns |
-- | local              | Fast    | Primary durable      |
-- | remo
...

-- LOGICAL REPLICATION: replicate specific tables, not entire DB
-- Useful for: selective sync, cross-version, data integration

-- On the PUBLISHER (source):
CREATE PUBLICATION my_publication
FOR TABLE customers, orders, products;

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

-- On the SUBSCRIBER (destination):
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=primary_ip dbname=mydb user=repl_user password=pwd'
PUBLICATION my_publication;

-- Check subscription
...

-- FAILOVER: promoting a replica to primary

-- Manual failover (PostgreSQL):
-- On the replica server:
-- pg_ctl promote -D /var/lib/postgresql/data
-- or:
SELECT pg_promote(wait := true, timeout := 60);

-- After promotion:
-- 1. Update application connection strings to point to new primary
-- 2. Verify the new primary is accepting writes:
SELECT pg_is_in_recovery();
-- FALSE = this is the primary (accepting writes)
-- TRUE = this is still a replica (read-only)

-- 3. Rebuild old primary as a 
...

-- READ REPLICAS: scale reads horizontally

-- Architecture:
-- Writes → Primary (single server)
-- Reads  → Load balancer → Replica 1, Replica 2, Replica 3

-- Connection routing in your application:
-- write_pool = connect("primary_host:5432")
-- read_pool  = connect("replica_lb:5432")

-- Route queries:
-- INSERT/UPDATE/DELETE → write_pool
-- SELECT → read_pool

-- ⚠️ CAUTION: Replica lag means reads might be stale
-- After a write, immediately reading from replica may not see the change

-- 
...