WAL and the Replication Protocol
Read the write-ahead log directly — its current position, how much a workload generates, and which files are safe to remove
Every change PostgreSQL makes to data on disk is first recorded in the write-ahead log — WAL — as a sequence of records identified by an ever-increasing Log Sequence Number, or LSN. Replication, at its core, is nothing more exotic than shipping this same log somewhere else and replaying it. wal_level controls how much extra information those records carry: minimal keeps just enough for crash recovery, replica adds what a physical standby needs, and logical adds what logical decoding needs to reconstruct individual row changes.\n\nThis lab treats WAL as a measurable, inspectable thing rather than an abstraction. You will read the current LSN before and after a real workload, compute exactly how many bytes of WAL it generated, and list the actual 16MB segment files sitting in pg_wal — the same files every later lab in this block will stream to a standby.
The Three Levels
ALTER SYSTEM SET wal_level = 'replica';
| Level | Adds | Needed for |
|-------|------|------------|
| minimal | Only what crash recovery itself needs | A standalone server with no replication at all |
| replica | Enough for a physical standby to replay every change | Streaming replication, pg_basebackup |
| logical | Enough to reconstruct individual row-level changes | CREATE PUBLICATION / logical decoding |
wal_level is postmaster-context — like shared_buffers and logging_collector earlier in this course, changing it needs a restart, not just a reload.
Reading the Current Position
SELECT pg_current_wal_lsn();
SELECT pg_walfile_name(pg_current_wal_lsn());
An LSN is a byte offset into the entire WAL stream since the cluster was created, printed as two hex numbers separated by a slash. pg_walfile_name() translates that same position into the actual 16MB segment file it falls inside — the same file you would find sitting in pg_wal.
Measuring What a Workload Actually Generates
A single psql -c invocation is one connection that closes when it finishes, so a session variable from \gset cannot survive to a later invocation. A plain table can:
CREATE TABLE wal_marker (start_lsn pg_lsn);
INSERT INTO wal_marker SELECT pg_current_wal_lsn();
-- ... run the workload ...
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), (SELECT start_lsn FROM wal_marker))) AS wal_generated;
What Is Actually Sitting in pg_wal
SELECT name, size, modification FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 5;
This is a SQL-level view of the same segment files ls would show under as-postgres — each one a fixed 16MB, regardless of how much of it is actually used. A segment is only safe to remove once nothing still needs it: not the local crash-recovery checkpoint, not any connected standby, and not any replication slot holding it open — the exact mechanism Lab 2.5.3 puts under real pressure.
LSN (Log Sequence Number)
A monotonically increasing byte position within the entire WAL stream since the cluster's creation, printed as two hexadecimal numbers separated by a slash (e.g. 0/8000078). Every WAL record has one, every replication connection reports one for what it has sent/written/flushed/replayed, and pg_wal_lsn_diff() computes the exact byte distance between any two of them.
wal_level
Controls how much information beyond bare crash-recovery is written into WAL records. Each level is a strict superset of the one below it — logical includes everything replica needs, which includes everything minimal needs — so raising the level costs some extra WAL volume but never breaks a use case the lower level supported.
📖 Read the Current wal_level
Check what this cluster is currently configured to record.
psql -U postgres -d beer_db -c "SHOW wal_level;"student@lab:~$ psql -U postgres -d beer_db -c "SHOW wal_level;" SET wal_level ----------- minimal (1 row)
⚙️ Raise It to replica
wal_level is postmaster-context — set it and restart.
as-postgres psql -U postgres -c "ALTER SYSTEM SET wal_level = 'replica';"as-postgres pg_ctl restart -D /var/lib/postgresql/18/data -wstudent@lab:~$ as-postgres psql -U postgres -c "ALTER SYSTEM SET wal_level = 'replica';" ALTER SYSTEM student@lab:~$ as-postgres pg_ctl restart -D /var/lib/postgresql/18/data -w waiting for server to shut down.... done server stopped waiting for server to start....2026-06-26 16:02:36.011 UTC [138] LOG: starting PostgreSQL 18.4 on i686-buildroot-linux-gnu, compiled by i686-buildroot-linux-gnu-gcc.br_real (Buildroot 2024.02.3) 12.3.0, 32-bit 2026-06-26 16:02:36.012 UTC [138] LOG: listening on IPv4 address "0.0.0.0", port 5432 2026-06-26 16:02:36.012 UTC [138] LOG: listening on IPv6 address "::", port 5432 2026-06-26 16:02:36.019 UTC [138] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2026-06-26 16:02:36.079 UTC [141] LOG: database system was shut down at 2026-06-26 16:02:35 UTC 2026-06-26 16:02:36.122 UTC [138] LOG: database system is ready to accept connections done server started
📍 Read the Current Position
Get the current WAL LSN and translate it into the segment file it falls inside.
psql -U postgres -d beer_db -c "SELECT pg_current_wal_lsn();"psql -U postgres -d beer_db -c "SELECT pg_walfile_name(pg_current_wal_lsn());"student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_current_wal_lsn();" SET pg_current_wal_lsn -------------------- 0/7916D80 (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_walfile_name(pg_current_wal_lsn());" SET pg_walfile_name -------------------------- 000000010000000000000007 (1 row)
📏 Mark the Starting Point
Since a plain psql -c connection closes when it finishes, store the starting LSN in a real table so a later, separate connection can read it back.
psql -U postgres -d beer_db -c "CREATE TABLE IF NOT EXISTS wal_demo (id serial PRIMARY KEY, payload text);" -c "CREATE TABLE IF NOT EXISTS wal_marker (start_lsn pg_lsn);" -c "DELETE FROM wal_marker;" -c "INSERT INTO wal_marker SELECT pg_current_wal_lsn();"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE IF NOT EXISTS wal_demo (id serial PRIMARY KEY, payload text);" -c "CREATE TABLE IF NOT EXISTS wal_marker (start_lsn pg_lsn);" -c "DELETE FROM wal_marker;" -c "INSERT INTO wal_marker SELECT pg_current_wal_lsn();" SET CREATE TABLE CREATE TABLE DELETE 0 INSERT 0 1
💪 Run the Workload and Measure
Insert and update 50,000 rows, then compute the exact WAL generated using the marker.
psql -U postgres -d beer_db -c "INSERT INTO wal_demo (payload) SELECT repeat('a',50) FROM generate_series(1,50000) g;" -c "UPDATE wal_demo SET payload = payload || 'x';"psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), (SELECT start_lsn FROM wal_marker))) AS wal_generated;"student@lab:~$ psql -U postgres -d beer_db -c "INSERT INTO wal_demo (payload) SELECT repeat('a',50) FROM generate_series(1,50000) g;" -c "UPDATE wal_demo SET payload = payload || 'x';" SET INSERT 0 50000 UPDATE 50000 student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), (SELECT start_lsn FROM wal_marker))) AS wal_generated;" SET wal_generated --------------- 23 MB (1 row)
🗂️ List What Is Actually on Disk
Query pg_ls_waldir() to see the real 16MB segment files backing this activity.
psql -U postgres -d beer_db -c "SELECT name, size, modification FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 5;"student@lab:~$ psql -U postgres -d beer_db -c "SELECT name, size, modification FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 5;" SET name | size | modification --------------------------+----------+------------------------ 000000010000000000000009 | 16777216 | 2026-06-26 16:02:57+00 000000010000000000000008 | 16777216 | 2026-06-26 16:02:56+00 000000010000000000000007 | 16777216 | 2026-06-26 16:02:45+00 00000001000000000000000B | 16777216 | 2026-06-22 17:05:15+00 00000001000000000000000D | 16777216 | 2026-06-26 16:02:56+00 (5 rows)
Lab 2.5.1 complete. You can now read the write-ahead log directly, independent of any replication setup:\n\n\n wal_level : ✅ read, raised to replica, restart confirmed\n pg_current_wal_lsn() : ✅ read the exact current position\n pg_wal_lsn_diff() : ✅ measured 23MB from a real 100k-row workload\n pg_ls_waldir() : ✅ listed the actual 16MB segment files\n
Enable JavaScript to run the live terminal and track your progress.