Lesson 2: PostgreSQL Streaming Replication

$ ls -lh $PGDATA/pg_wal/
-rw------- 1 postgres postgres 16M Nov 24 10:00 000000010000000000000001
-rw------- 1 postgres postgres 16M Nov 24 10:15 000000010000000000000002
-rw------- 1 postgres postgres 16M Nov 24 10:30 000000010000000000000003

-- Check current LSN
SELECT pg_current_wal_lsn();  -- Primary
-- Output: 0/3000060

SELECT pg_last_wal_receive_lsn();  -- Standby (received)
SELECT pg_last_wal_replay_lsn();   -- Standby (applied)

# postgresql.conf

# WAL Settings
wal_level = replica              # minimal, replica, or logical
                                 # replica: for streaming replication

wal_log_hints = on              # Needed for pg_rewind

# WAL Writing
wal_buffers = 16MB              # WAL buffer size in shared memory
wal_writer_delay = 200ms        # WAL writer sleep time

# WAL Files Management
min_wal_size = 80MB            # Minimum WAL files to keep
max_wal_size = 1GB             # Trigger checkpoint when exceeded

# Checkpoints
checkpoint_timeout = 5min       # Maximum between 2 checkpoints
checkpoint_completion_target = 0.9  # Spread checkpoint writes

1. Server restart
2. PostgreSQL reads last checkpoint location
3. Replay all WAL records from checkpoint → crash point
4. Restore database to consistent state
5. Ready to accept connections

Timeline:
10:00 ─── Checkpoint ─── 10:05 ─── 10:08 (CRASH)
          (LSN: 0/1000)          (LSN: 0/3000)
          
Recovery:
- Start from LSN 0/1000
- Replay WAL → LSN 0/3000
- Database consistent at 10:08

# postgresql.conf (Primary)
synchronous_commit = off  # or local

# postgresql.conf (Primary)
synchronous_commit = on              # on, remote_write, remote_apply
synchronous_standby_names = 'standby1,standby2'  # Standby names

# recovery.conf or postgresql.auto.conf (Standby)
primary_conninfo = 'host=primary port=5432 user=replicator application_name=standby1'

# Wait for ANY 1 of 2 standbys
synchronous_standby_names = 'ANY 1 (standby1, standby2)'

# Wait for FIRST 2 of 3 standbys
synchronous_standby_names = 'FIRST 2 (standby1, standby2, standby3)'

# Wait for ALL standbys (same as old)
synchronous_standby_names = 'standby1, standby2'

3 Standbys: standby1 (DC1), standby2 (DC2), standby3 (DC3)

Transaction commits when:
✅ Primary committed + ANY 1 standby acknowledged

Scenario:
- standby1: ACK in 5ms
- standby2: ACK in 100ms (slow network)
- standby3: DOWN

→ Transaction commits after 5ms (waiting for standby1)
→ Good performance + Data safety

1. Primary generates WAL files
2. Checkpoint happens → Old WAL cleaned up
3. Standby offline for hours
4. Standby comes back online
5. ❌ Needed WAL files have been deleted
6. ❌ Standby cannot catch up
7. ❌ Need to rebuild Standby from scratch

-- Physical replication slot
SELECT * FROM pg_create_physical_replication_slot('standby1_slot');

-- View slot list
SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

-- Output:
 slot_name     | slot_type | active | restart_lsn | confirmed_flush_lsn
---------------+-----------+--------+-------------+--------------------
 standby1_slot | physical  | t      | 0/3000000   | NULL

# postgresql.auto.conf (Standby)
primary_slot_name = 'standby1_slot'

SELECT pg_drop_replication_slot('standby1_slot');

-- Check slot status
SELECT 
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained_wal
FROM pg_replication_slots;

-- Alert if retained_wal is too large (>10GB)

-- Set max WAL size to protect Primary
ALTER SYSTEM SET max_slot_wal_keep_size = '100GB';  -- PostgreSQL 13+

-- Or automatically drop inactive slot after 24h
SELECT pg_drop_replication_slot(slot_name)
FROM pg_replication_slots
WHERE NOT active 
  AND pg_current_wal_lsn() - restart_lsn > 100*1024*1024*1024;  -- 100GB

Primary:  192.168.1.101 (node1)
Standby:  192.168.1.102 (node2)
PostgreSQL: 14
OS: Ubuntu 22.04

# Install PostgreSQL 14
sudo apt update
sudo apt install -y postgresql-14 postgresql-contrib-14

# Stop service
sudo systemctl stop postgresql

sudo -u postgres psql

-- Create user for replication
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'repl_password';

-- Exit
\q

sudo nano /etc/postgresql/14/main/postgresql.conf

# Connection
listen_addresses = '*'
port = 5432

# Replication
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5
wal_keep_size = 1GB

# Hot Standby (not needed for primary but good to have)
hot_standby = on

# Archive (optional, recommended)
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/14/archive/%f && cp %p /var/lib/postgresql/14/archive/%f'

sudo mkdir -p /var/lib/postgresql/14/archive
sudo chown postgres:postgres /var/lib/postgresql/14/archive

sudo nano /etc/postgresql/14/main/pg_hba.conf

# Replication connections
host    replication     replicator      192.168.1.102/32        md5
host    replication     replicator      127.0.0.1/32            md5

sudo systemctl start postgresql
sudo systemctl status postgresql

sudo -u postgres psql

SELECT pg_create_physical_replication_slot('standby_slot');
SELECT * FROM pg_replication_slots;
\q

sudo systemctl stop postgresql
sudo mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main.bak

# Use pg_basebackup
sudo -u postgres pg_basebackup \
    -h 192.168.1.101 \
    -D /var/lib/postgresql/14/main \
    -U replicator \
    -P \
    -v \
    -R \
    -X stream \
    -C -S standby_slot

# Options explanation:
# -h: Primary host
# -D: Data directory
# -U: Replication user
# -P: Show progress
# -v: Verbose
# -R: Create standby.signal and postgresql.auto.conf
# -X stream: Stream WAL during backup
# -C: Create replication slot
# -S: Slot name

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created replication slot "standby_slot"
24567/24567 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000100
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

ls -l /var/lib/postgresql/14/main/standby.signal
# This file marks this as a standby server

sudo cat /var/lib/postgresql/14/main/postgresql.auto.conf

# Created automatically by pg_basebackup -R
primary_conninfo = 'user=replicator password=repl_password host=192.168.1.101 port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'standby_slot'

sudo systemctl start postgresql
sudo systemctl status postgresql

sudo -u postgres psql

-- Check replication status
SELECT 
    client_addr,
    state,
    sync_state,
    replay_lsn,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as lag
FROM pg_stat_replication;

-- Output:
 client_addr   |   state   | sync_state | replay_lsn  |  lag
---------------+-----------+------------+-------------+-------
 192.168.1.102 | streaming | async      | 0/3000060   | 0 bytes

sudo -u postgres psql

-- Check standby status
SELECT pg_is_in_recovery();  -- Should return 't' (true)

-- Check replication lag
SELECT 
    pg_last_wal_receive_lsn() AS receive,
    pg_last_wal_replay_lsn() AS replay,
    pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())) AS lag;

-- Output:
   receive   |   replay    |  lag
-------------+-------------+--------
 0/3000060   | 0/3000060   | 0 bytes

-- Create database and table
CREATE DATABASE testdb;
\c testdb

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (name) VALUES 
    ('Alice'),
    ('Bob'),
    ('Charlie');

SELECT * FROM users;

\c testdb

-- Read queries work
SELECT * FROM users;

-- Output:
 id |  name   |       created_at
----+---------+------------------------
  1 | Alice   | 2024-11-24 10:30:15
  2 | Bob     | 2024-11-24 10:30:15
  3 | Charlie | 2024-11-24 10:30:15

-- Write queries are rejected
INSERT INTO users (name) VALUES ('David');
-- ERROR: cannot execute INSERT in a read-only transaction

-- On Primary
CREATE OR REPLACE FUNCTION replication_lag_bytes()
RETURNS TABLE(client_addr INET, lag_bytes BIGINT) AS $
BEGIN
    RETURN QUERY
    SELECT 
        c.client_addr,
        pg_wal_lsn_diff(pg_current_wal_lsn(), c.replay_lsn)::BIGINT
    FROM pg_stat_replication c;
END;
$ LANGUAGE plpgsql;

-- Use
SELECT * FROM replication_lag_bytes();

SELECT client_addr, 
       pg_size_pretty(lag_bytes) as lag
FROM replication_lag_bytes()
WHERE lag_bytes > 10*1024*1024;

# Check logs
sudo tail -f /var/lib/postgresql/14/main/log/postgresql-*.log

# Common errors:
# - "FATAL: password authentication failed"
#   → Check pg_hba.conf and password

# - "FATAL: no pg_hba.conf entry for replication"
#   → Add replication entry to pg_hba.conf

# - Connection refused
#   → Check firewall, listen_addresses

-- Check WAL sender busy
SELECT * FROM pg_stat_activity 
WHERE backend_type = 'walsender';

-- Check I/O on Standby
SELECT * FROM pg_stat_bgwriter;

-- Check retained WAL
SELECT 
    slot_name,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained
FROM pg_replication_slots;

-- Drop inactive slot if needed
SELECT pg_drop_replication_slot('standby_slot');

# Primary - postgresql.conf

# Network buffer (if many standbys)
max_wal_senders = 10  # Depending on standbys + 2 backup
# WAL retention
wal_keep_size = 2GB  # Keep enough WAL for standby to catch up
max_slot_wal_keep_size = 10GB  # Limit slot retention (PG 13+)

# Archive (backup strategy)
archive_mode = on
archive_command = 'cp %p /backup/archive/%f'

# Checkpoint tuning
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

# Use SSL for replication
ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'

# Standby connection string
primary_conninfo = '... sslmode=require sslcompression=1'

# pg_hba.conf - Use hostssl
hostssl replication replicator 192.168.1.0/24 md5