PostgreSQL Internals - Module 9: Replication

In the previous blog, we discussed about the "Concurrency & Locking" and explored how PostgreSQL manages concurrent access at every level from the 8-mode table lock hierarchy and its conflict matrix.

In this blog, we are going to discuss about the "Replication" and how it helps the PostgreSQL to keep the data safe across multiple machines. A single server in a system is always a single point of failure and replication is what turns PostgreSQL into a high-availability system. In this we are going to discuss that exact mechanics of replication: how WAL bytes flow from primary to standby, how logical decoding transforms those bytes into row-level changes, how replication slots prevent data loss, and what happens at every layer when you commit a transaction on a synchronous replica setup.

Part 1: Replication Fundamentals

PostgreSQL supports two fundamentally different replication approaches:

1. Physical/Streaming Replication

2. Logical Replication

Physical Replication

Ships raw WAL bytes from primary to standby. The standby replays the exact same WAL records the primary wrote - byte for byte. The result is a binary-identical copy of the primary.

Primary                           Standby
  │                                  │
  │─ WAL record (INSERT, page  X) ─>│
  │─ WAL record (UPDATE, page  Y) ─>│─ replay → apply to data files
  │─ WAL record (COMMIT, xid=5001)─>│
  │<─ acknowledgement (LSN) ─────│

Characteristics:

• Replicates everything: all databases, all tables, system catalogs

• Standby is identical at the block level: cannot have different indexes or schema

• Standby can serve read-only queries (hot standby)

Logical Replication

Decodes WAL records into SQL-level row changes (INSERT/UPDATE/DELETE) and ships those instead. The subscriber applies the changes to its own tables.

Primary                              Subscriber
  │                                     │
  │── WAL record (heap INSERT) ─>     │
  │   logical decoding  ────────>  │
  │   row: {id=42, name='alice'}  ───>│── apply INSERT
  │<── confirmation  ───────────│

Characteristics:

• Replicates specific tables or publications, not the whole cluster

• Subscriber can have different schema, indexes, additional columns

• Can replicate between different PostgreSQL major versions

• Powers CDC (change data capture) pipelines

• Cannot replicate DDL automatically

Part 2: Physical Replication - Deep Internals

Setting up streaming replication

-- On Primary: postgresql.conf
wal_level = replica    -- minimum for replication
max_wal_senders = 5    -- max concurrent WAL sender processes
wal_keep_size = 1GB    -- Keep at-least 1GB of WAL for lagging standbys

-- Create replication role
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secret';

-- pg_hba.conf
host replication replicator standby_ip/32 scram-sha-256

-- On Standby: create base-backup
pg_basebackup -h primary_ip -U replicator -D /var/lib/postgresql/data \
     -P -Xs -R

-- -Xs = stream WAL during backup
-- -R = create standby.signal and postgresql.auto.conf automatically

The WAL sender: WAL receiver pipeline

Primary process tree:

• Postmaster

  • WAL Sender (one per standby connection)

    • reads WAL from pg_wal/

    • sends to standby over TCP

    • tracks standby's replay position

Standby process treee:

• Postmaster

  • Startup process (replays WAL in recovery mode)

  • WAL receiver (receives WAL from primary)

    • writes to pg_wal/

    • signals startup process: new WAL available

The Replication Protocol - Exact Sequence

Standby → Primary:  START_REPLICATION LSN timeline
Primary → Standby:  XLogData (WAL bytes, starting_lsn, current_lsn, send_time)
Standby → Primary:  Standby Status Update (every wal_receiver_status_interval)
         write_lsn   — written to pg_wal on standby
         flush_lsn   — fsynced on standby
         replay_lsn  — applied to data files on standby
         reply_time

Hot Standby: Read queries on standy

The standby's startup process replays WAL while simultaneously serving read-only queries. This works because:

Standby maintains its own:

• shared buffer pool

• MVCC snapshot (based on replayed transactions)

• pg_stat_activity for its own connections

• local lock table

Standby does NOT have:

• writable data files (read-only replay)

• its own transaction IDs (read use primary's XIDs)

• ability to run VACUUM independently

-- On standy: read-only queries work fine
SELECT count(*) FROM orders; -- OK

-- Writes are rejected:
INSERT INTO orders VALUES (...);
-- ERROR: cannot execute INSERT in a read-only transaction

Part 3: Replication Slots

Replication slots solve a critical problem: What if the standby falls behind?

Without replication slots, if a standby disconnects, the primary might delete WAL files the standby still needs. When the standby reconnects, it cannot continue - it has to do a full base backup again.

How replication slots work

-- Create a physical replication slot:
SELECT pg_create_physical_replication_slot('standby_slot');

-- The slot records:
-- restart_lsn: the oldest WAL position the standby still needs
-- confirmed_flush_lsn: the latest position the standby confirmed

-- Primary will NOT delete WAL files before restart_lsn
-- even if the standby is disconnected for days/weeks

-- Monitor replication slots:
SELECT
    slot_name,
    slot_type,              -- physical or logical
    active,                 -- is a consumer currently connected?
    restart_lsn,            -- oldest WAL the slot needs
    confirmed_flush_lsn,    -- last position confirmed by consumer
    pg_wal_lsn_diff(
        pg_current_wal_lsn(),
        restart_lsn
    ) AS retained_wal_bytes,
    pg_size_pretty(pg_wal_lsn_diff(
        pg_current_wal_lsn(),
        restart_lsn
    )) AS retained_wal
FROM pg_replication_slots;

The replication slot danger

If a replication slot's consumer goes offline and never comes back, the slot continues to hold WAL. pg_wal/ grows indefinitely. When the disk fills: PRIMARY CRASHES.

Real scenario:

• Day 1: slot created for subscriber

• Day 3: subscriber host dies

• Day 10: primary disk is full

• Day 10: primary crashes: could not write to file pg_wal/...

Monitoring

-- CRITICAL monitoring query — run this every 5 minutes:
SELECT
    slot_name,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    ) AS wal_retained,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots
ORDER BY retained_bytes DESC;

-- Alert if retained_bytes > 10GB
-- Drop dead slots immediately:
SELECT pg_drop_replication_slot('dead_slot');

Part 4: Synchronous v/s Asynchronous Replication

Asynchronous replication (default)

Primary: COMMIT → WAL written + fsynced locally → SUCCESS returned to client

Standby: receives WAL asynchronously (milliseconds to seconds later)

Risk: if primary crashes immediately after COMMIT, standby may not have received that WAL yet means data loss of committed transactions

Synchronous replication

-- postgresql.conf on primary:
synchronous_standby_names = 'standby1'

-- or for multiple:
synchronous_standby_names = 'FIRST 1 (standby1, standby2)'  -- any one of them
synchronous_standby_names = 'ALL (standby1, standby2)'      -- all of them

Primary: when COMMIT, it follows:

• write WAL locally

• send WAL to standby

• WAIT for acknowledgement from standby

• standby confirms flush_lsn >= commit_lsn

• SUCCESS returned to client

Guarantee: no committed transaction can be lost (both primary and standby must fail simultaneously)

Cost: COMMIT latency += network round trip to standby (~0.5ms on LAN, ~5ms on WAN, ~50ms cross-region)

synchronous_commit levels

-- Per-transaction control:
SET synchronous_commit = on;           -- wait for standby flush (default)
SET synchronous_commit = remote_apply; -- wait for standby to replay
SET synchronous_commit = remote_write; -- wait for standby OS write (no fsync)
SET synchronous_commit = local;        -- only wait for local fsync
SET synchronous_commit = off;          -- don't wait for anything

-- Use off for bulk loads where some data loss is acceptable:
BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO events SELECT * FROM staging_events;
COMMIT;  -- returns immediately, standby catches up later

Part 5: Logical Replication - Deep Internals

The logical decoding pipeline

WAL stream
  │
  V
Output Plugin (pgoutput, wal2json, decoderbufs...)
  │  reads WAL records
  │  filters by publication tables
  │  reconstructs row images from before/after
  │  emits logical change messages:
  │    BEGIN xid=5001
  │    INSERT relation=orders tuple=(42,'alice',100.00)
  │    UPDATE relation=orders old=(42,'alice',100.00) new=(42,'alice',150.00)
  │    COMMIT xid=5001 lsn=0/1A3F058
  V
Replication Protocol
  │
  V
Subscriber (apply worker)
  applies changes to subscriber tables

Publications and subscription

On publisher (primary)

-- Publish all tables:
CREATE PUBLICATION my_pub FOR ALL TABLES;

-- Publish specific tables:
CREATE PUBLICATION orders_pub FOR TABLE orders, customers;

-- Publish specific operations:
CREATE PUBLICATION insert_only_pub FOR TABLE events
    WITH (publish = 'insert');  -- only INSERT, not UPDATE/DELETE

-- See what's published:
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

On subscriber

-- Create subscription:
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=primary_ip dbname=mydb user=replicator password=secret'
    PUBLICATION my_pub;

-- PostgreSQL automatically:
-- 1. Creates a replication slot on the publisher
-- 2. Takes a snapshot of current data
-- 3. Copies existing table data (initial sync)
-- 4. Starts streaming new changes

-- Detailed per-table sync status:
SELECT * FROM pg_subscription_rel;
-- srsubstate: i=initializing, d=data copy, s=synchronized, r=ready

REPLICA IDENTITY - how UPDATE/DELETE know what changed

For UPDATE and DELETE, the subscriber needs to identify which row to modify. This requires a REPLICA IDENTITY:

-- Default: REPLICA IDENTITY DEFAULT
-- Uses primary key columns as identifier
-- If no PK: cannot replicate UPDATE/DELETE

-- FULL: send entire old row (expensive, no PK needed)
ALTER TABLE orders REPLICA IDENTITY FULL;

-- USING INDEX: use a unique index
ALTER TABLE orders REPLICA IDENTITY USING INDEX idx_orders_uuid;

-- Check:
SELECT relname, relreplident FROM pg_class WHERE relname = 'orders';
-- d = default (PK), f = full, i = index, n = nothing

Part 6: Logical Replication Slots - The Decoding Engine

A logical replication slot is more complex than a physical one:

-- Create a logical slot manually (for CDC, not subscription):
SELECT pg_create_logical_replication_slot('my_slot', 'pgoutput');
SELECT pg_create_logical_replication_slot('cdc_slot', 'wal2json');

-- Peek at decoded changes without consuming:
SELECT * FROM pg_logical_slot_peek_changes('my_slot', NULL, NULL);

-- Consume changes (advances the slot):
SELECT * FROM pg_logical_slot_get_changes('my_slot', NULL, NULL);

-- Output with wal2json (JSON format for CDC pipelines):
SELECT * FROM pg_logical_slot_get_changes(
    'cdc_slot', NULL, NULL,
    'pretty-print', '1',
    'include-timestamp', '1'
);

The logical slot stores:

• restart_lsn : WAL must be kept from here (same as physical)

• confirmed_flush_lsn : last change confirmed consumed by client

• catalog_xmin : oldest XID needed for catalog lookups during decoding

The catalog_xmin is a second wraparound risk specific to logical slots - it prevents VACUUM from cleaning system catalog dead tuples. Monitor it:

SELECT slot_name, catalog_xmin, age(catalog_xmin)
FROM pg_replication_slots
WHERE slot_type = 'logical';
-- age > 500M is dangerous

Part 7: Cascading Replication and Standby Promotion

Cascading Replication

A standby can itself have standbys - WAL flows from primary → standby 1 → standby 2:

Primary ── WAL──> Standby 1 ──WAL──> Standby 2
                       │                    │
                   (hot standby)        (hot standby)
                    reads OK             reads OK

-- On Standby 1: allow WAL senders to Standby 2
-- postgresql.conf:
max_wal_senders = 3
hot_standby = on

-- Standby 2 connects to Standby 1 as its upstream
-- primary_conninfo = 'host=standby1_ip ...'

Standby promotion

When the primary fails, a standby is promoted to become the new primary:

# Promote standby to primary:
pg_ctl promote -D /var/lib/postgresql/data

# Or create a trigger file (older method):
touch /var/lib/postgresql/data/failover.trigger

What happens internally:

• Startup process stops replaying WAL

• Standby completes any in-progress WAL record

• Writes a new timeline history files to pg_wal/

• Increments timeline ID (e.g., timeline 1 -> timeline 2)

• Opens for read-write connections

• Other standbys detect timeline change

  • reconnect to new primary

  • start replicating from the divergence point

Part 8: pg_basebackup and Point-in-Time Recovery (PITR)

Base backup

# Full base backup:
pg_basebackup \
    -h primary_ip \
    -U replicator \
    -D /backup/basebackup_$(date +%Y%m%d) \
    -Ft \          # tar format
    -z \           # gzip compress
    -Xs \          # stream WAL
    -P             # show progress

# Backup manifest (PG 13+): verifies backup integrity
pg_verifybackup /backup/basebackup_20240115/

WAL archiving for PITR

-- postgresql.conf:
archive_mode = on
archive_command = 'aws s3 cp %p s3://my-wal-archive/%f'
-- %p = full path to WAL file
-- %f = filename only

-- Monitor archiving:
SELECT archived_count, failed_count,
       last_archived_wal, last_archived_time,
       last_failed_wal, last_failed_time
FROM pg_stat_archiver;

Point-in-time recovery

# recovery.conf (or postgresql.conf in PG 12+):
restore_command = 'aws s3 cp s3://my-wal-archive/%f %p'
recovery_target_time = '2024-01-15 14:30:00 UTC'
recovery_target_action = 'promote'   # open for writes after reaching target

# PostgreSQL replays WAL until the target time
# Stops at the first commit after target_time
# Then promotes (or pauses for inspection)

-- During recovery, monitor progress:
SELECT pg_last_xact_replay_timestamp();
-- Watch this advance toward your target time

-- After recovery: verify you're at the right point
SELECT max(created_at) FROM orders;  -- did you recover enough?

Part 9: Hands-On Lab

-- ═══ 1. Set up logical replication locally ═══

-- Publisher database (run in psql -d publisher_db):
CREATE TABLE products (
    id bigserial primary key,
    name text,
    price numeric,
    updated_at timestamptz default now()
);
INSERT INTO products(name, price)
VALUES ('Widget A', 9.99), ('Widget B', 19.99), ('Widget C', 4.99);

CREATE PUBLICATION products_pub FOR TABLE products;

SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

-- ═══ 2. Create subscriber ═══

-- Subscriber database (run in psql -d subscriber_db):
CREATE TABLE products (
    id bigint primary key,
    name text,
    price numeric,
    updated_at timestamptz
    -- note: no serial, subscriber doesn't generate IDs
);

CREATE SUBSCRIPTION products_sub
    CONNECTION 'host=localhost dbname=publisher_db user=replicator'
    PUBLICATION products_pub;

-- Check initial sync:
SELECT * FROM products;
-- Should have 3 rows copied from publisher

-- ═══ 3. Watch changes replicate ═══

-- Publisher:
INSERT INTO products(name, price) VALUES ('Widget D', 29.99);
UPDATE products SET price = 11.99 WHERE name = 'Widget A';
DELETE FROM products WHERE name = 'Widget C';

-- Subscriber (immediately after):
SELECT * FROM products;
-- Should reflect all three changes

-- ═══ 4. Monitor the logical slot ═══

-- Publisher:
SELECT slot_name, confirmed_flush_lsn, restart_lsn,
       pg_size_pretty(
           pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal
FROM pg_replication_slots;

-- ═══ 5. Peek at raw logical changes ═══

SELECT pg_create_logical_replication_slot('inspect_slot', 'pgoutput');

-- Make some changes:
INSERT INTO products(name, price) VALUES ('Widget E', 39.99);

-- Peek at the decoded stream:
SELECT lsn, xid, data
FROM pg_logical_slot_peek_binary_changes(
    'inspect_slot', NULL, NULL,
    'proto_version', '1',
    'publication_names', 'products_pub'
);

-- Clean up:
SELECT pg_drop_replication_slot('inspect_slot');

-- ═══ 6. Simulate replication lag ═══

-- Pause WAL replay on standby:
SELECT pg_wal_replay_pause();

-- Make changes on primary...
INSERT INTO products(name, price)
SELECT 'Lag test ' || g, g FROM generate_series(1,1000) g;

-- Check lag:
SELECT
    pg_wal_lsn_diff(
        pg_last_wal_receive_lsn(),
        pg_last_wal_replay_lsn()
    ) AS lag_bytes;

-- Resume replay:
SELECT pg_wal_replay_resume();

-- Watch lag decrease:
SELECT pg_wal_lsn_diff(
    pg_last_wal_receive_lsn(),
    pg_last_wal_replay_lsn()
) AS lag_bytes;

-- ═══ 7. PITR practice ═══

-- Record a restore target:
SELECT now() AS restore_target, pg_current_wal_lsn() AS restore_lsn;
-- Save these values

-- Make destructive change:
DELETE FROM products WHERE price < 10;

-- Recover to saved LSN:
-- recovery_target_lsn = '0/1A3F058'  (your saved LSN)
-- recovery_target_action = 'promote'

Conclusion

This is the final module of the PostgreSQL Internals series. We have now traced the complete lifecycle of data in PostgreSQL from the moment SQL text arrives over the wire, through the parser, analyzer, rewriter, planner, and executor; through MVCC visibility checks reading xmin and xmax on heap pages; through WAL records being written and fsynced on COMMIT; through VACUUM reclaiming dead tuples and setting visibility map bits; through lock acquisition and deadlock detection and now through WAL bytes flowing across the network to replicas, being decoded into row-level changes, and powering high-availability and CDC pipelines.