Streaming_replication

Chapter 41: Streaming Replication

Data Replication Fundamentals

---

41.1 Understanding Replication

Replication copies data from one database to another for redundancy and performance.

                    Replication Types
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                    PostgreSQL Replication                            │
    │                                                                      │
    │   Physical Streaming Replication:                                    │
    │   ─────────────────────────────────                                 │
    │   • Exact byte-for-byte copy                                       │
    │   • Low latency                                                    │
    │   • Used for HA and read scaling                                   │
    │   • Primary-Standby setup                                          │
    │                                                                      │
    │   Logical Replication:                                              │
    │   ────────────────────                                             │
    │   • Replicate specific tables/databases                            │
    │   • Different PostgreSQL versions possible                         │
    │   • Row-based filtering                                           │
    │                                                                      │
    │   Synchronous vs Asynchronous:                                     │
    │   ──────────────────────────────                                   │
    │   • Synchronous - waits for standby to confirm                    │
    │   • Asynchronous - fire and forget                                 │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

---

41.2 Setting Up Streaming Replication

-- On Primary:
-- 1. Enable replication
ALTER SYSTEM SET wal_level = replica;

-- 2. Set max_wal_senders
ALTER SYSTEM SET max_wal_senders = 10;

-- 3. Set replication connections
ALTER SYSTEM SET listen_addresses = '*';

-- 4. Create replication user
CREATE USER replicator WITH REPLICATION PASSWORD 'secret_password';

-- 5. Add to pg_hba.conf:
-- host replication replicator 10.0.0.0/24 md5

-- 6. Restart PostgreSQL
-- sudo systemctl restart postgresql

-- On Standby:
-- 1. Create base backup
pg_basebackup -h primary_host -D /var/lib/postgresql/15/main -P -U replicator

-- 2. Create recovery configuration
-- postgresql.conf:
-- primary_conninfo = 'host=primary_host port=5432 user=replicator password=secret_password'
-- restore_command = 'cp /wal_archive/%f %p'
-- hot_standby = on

-- 3. Create standby.signal
touch /var/lib/postgresql/15/main/standby.signal

-- 4. Start standby
-- sudo systemctl start postgresql

---

41.3 Monitoring Replication

-- Check replication status
SELECT * FROM pg_stat_replication;

-- Check wal sender status
SELECT * FROM pg_stat_wal_sender;

-- Check wal receiver status (on standby)
-- SELECT * FROM pg_stat_wal_receiver;

-- Check replication lag
SELECT
    client_addr,
    state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) as lag_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) as write_lag,
    pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) as flush_lag
FROM pg_stat_replication;

-- Check slot usage
SELECT * FROM pg_replication_slots;

---

Summary

Component	Description
Primary	Source database
Standby	Replica database
WAL	Write-Ahead Log
Replication slot	Tracks Standby progress

---

Next: Chapter 42: Logical Replication