0Pricing
SQL Academy · Lesson

Streaming Replication and WAL

Configure streaming physical replication based on WAL, monitor lag, and set up synchronous replicas for zero-data-loss writes.

Streaming Replication and WAL is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Replicate?

Replication serves several goals at once:

  • HA — survive a primary failure
  • Read scaling — offload SELECTs to replicas
  • Backup / PITR — continuous archiving
  • Geo-distribution — replicas near users

WAL: Write-Ahead Log

Every write goes to the WAL first. WAL guarantees durability and is the foundation of replication: stream WAL to a replica → replica replays WAL → replica is up-to-date.

Physical Streaming Replication

Byte-for-byte WAL stream from primary to replica:

  • Replica is a binary copy of the primary
  • Same Postgres version
  • Whole cluster replicated (all databases, all tables)
  • Read-only

Setting Up: Primary

Configure the primary in postgresql.conf:

wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

-- pg_hba.conf: allow replication connections
host replication replicator 10.0.0.0/24 md5

Setting Up: Replica

Create the base backup, start with primary_conninfo:

pg_basebackup -h primary -D /var/lib/postgresql/data -U replicator -W -X stream -P

-- postgresql.conf on replica:
primary_conninfo = 'host=primary port=5432 user=replicator password=...'

-- Touch standby.signal file and start:
touch /var/lib/postgresql/data/standby.signal

Synchronous vs Asynchronous

  • Async (default) — primary commits without waiting; replica might lag
  • Sync — primary waits for replica to confirm before COMMIT; zero data loss but slower
-- Synchronous configuration on primary:
synchronous_commit = on
synchronous_standby_names = 'replica1, replica2'

Replication Lag

Async replicas trail the primary. Monitor:

SELECT client_addr, state, sent_lsn, write_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

Replication Slots

Slots ensure the primary retains WAL until the replica has consumed it:

SELECT pg_create_physical_replication_slot('replica1');

-- On replica:
primary_slot_name = 'replica1'

Slot Pitfall

If a replica disconnects and never reconnects, its slot prevents WAL cleanup — disk fills up. Drop unused slots:

SELECT pg_drop_replication_slot('replica1');

Cascading Replicas

A replica can itself stream WAL to other replicas — reduces load on the primary.

Hot Standby

By default replicas accept read-only queries (hot_standby = on). Long-running read queries on the replica can delay WAL replay; tune max_standby_streaming_delay.

Promotion

To make a replica the new primary:

pg_ctl promote -D /var/lib/postgresql/data

-- Or in SQL:
SELECT pg_promote();

Recap

Streaming replication = WAL ship + replay.

  • Physical, binary-identical
  • Async by default; sync for zero data loss
  • Slots retain WAL but require care
  • Promotion turns a replica into a primary

Quick Check

What's the main risk of NOT using a replication slot for a streaming replica?

Frequently asked questions

Is the “Streaming Replication and WAL” lesson free?

Yes — the full text of “Streaming Replication and WAL” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Streaming Replication and WAL”?

Configure streaming physical replication based on WAL, monitor lag, and set up synchronous replicas for zero-data-loss writes. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Streaming Replication and WAL” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Streaming Replication and WAL
  2. Logical Replication for Sharding
  3. Failover and Leader Election (Patroni, Stolon)
  4. Read Replicas and Connection Routing
← Back to SQL Academy