In this article
- What you will learn
- Prerequisites
- How it works
- Build a small lab
- Look at the WAL settings and files
- LSNs: addresses inside the WAL
- Segments: switching, recycling and size
- wal_level: how much the WAL records
- See WAL do its job: crash recovery
- Put WAL on its own disk
- Common mistakes and how to fix them
- Practice exercise
- Quick reference
- Summary and what's next
This is Part 15 of the PostgreSQL DBA series. WAL (write-ahead log) is the record PostgreSQL writes of every change before it changes the real table and index files. It is the reason a committed transaction survives a power cut, and it is also the raw material for backups, point-in-time recovery and replication. Think of it as the notebook PostgreSQL writes in first: if the server crashes, it reads the notebook and redoes anything the data files missed.
In Part 14 we covered transaction ID wraparound and freezing. In this part you will find the WAL files on disk, read WAL positions (LSNs), measure how much WAL your statements produce, look inside WAL records with pg_waldump, change wal_level correctly, and watch PostgreSQL use WAL to recover from a crash. Every command was run on PostgreSQL 18 on Ubuntu, and the outputs are real.
What you will learn
- What WAL is, why PostgreSQL writes it first, and what a commit really waits for
- Where WAL lives (
pg_wal), how segment files are named and why they are 16 MB - How to read an LSN and turn it into a WAL file name with
pg_walfile_name() - How to measure the WAL a statement generates, and why the first change after a checkpoint costs more (full page writes)
- The three
wal_levelvalues (minimal, replica, logical), which one to use, and how to change it safely - How to watch WAL volume with
pg_stat_walandpg_stat_io, and what keeps old WAL files on disk
Prerequisites
- Parts 1 to 14 of this series, especially Part 3 (the data directory) and Part 5 (postgresql.conf, reload vs restart).
- One Ubuntu 24.04 LTS server with PostgreSQL 18 from the PGDG repository, cluster 18 main running on port 5432, installed as in Part 2. We call this server db1.
- A user with sudo rights. SQL is run in psql as the postgres operating system user.
- A test server, not production. Steps 13, 16 and 18 restart or stop the cluster, and Step 16 simulates a crash.
How it works
Tables and indexes live in data files made of 8 kB pages, as you saw in Part 3. When you change a row, PostgreSQL changes the page in shared memory (shared_buffers) first. Writing that page back to its data file straight away, for every commit, would mean many small random disk writes. PostgreSQL does something cheaper.
Before a changed page may be written to its data file, a WAL record describing the change must already be safely on disk. WAL records are appended one after another to WAL files, so writing them is fast sequential I/O. At commit, PostgreSQL only has to flush the WAL up to the commit record. The changed data pages can be written later, in the background.
If the server crashes, the data files may be missing recent changes. On startup PostgreSQL reads the WAL from the last checkpoint (a point where all earlier changes are known to be in the data files) and replays every record after it. The documentation calls this roll-forward recovery, or REDO.
client: UPDATE ... ; COMMIT
|
v
shared_buffers (page changed in memory)
|
| 1. WAL record written and flushed at COMMIT
v
pg_wal/000000010000000000000001 (sequential, fsync'd)
|
| 2. later: checkpointer / background writer
v
base/16384/16386 (table data file, written in the background)Four terms you will see throughout this part:
| Term | Meaning |
|---|---|
| WAL record | One entry describing one change, for example "insert tuple 41 into block 8 of relation 16386". |
| LSN (Log Sequence Number) | The byte position of a record in the WAL, shown like 0/1BCC3B0. It only ever grows, so the difference between two LSNs is the number of WAL bytes written between them. |
| WAL segment | One file in pg_wal. By default each segment is 16 MB. When one fills up, PostgreSQL moves on to the next. |
| Full page write (FPW, or FPI for full page image) | A copy of a whole 8 kB page written into the WAL the first time that page changes after a checkpoint. It protects against a page being only half written during a crash. |
WAL is also what makes the later parts of this series possible. Copying WAL files elsewhere is WAL archiving (Part 19). Replaying archived WAL on top of an old backup is point-in-time recovery (Part 20). Streaming WAL to another server is replication (Part 33). How much detail the WAL contains for these uses is controlled by wal_level.
Build a small lab
Step 1: Create the lab database and an orders table
On db1, as the postgres user (start psql with sudo -u postgres psql):
CREATE DATABASE wallab;
\c wallab
CREATE TABLE orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer text NOT NULL,
amount numeric(10,2) NOT NULL,
status text NOT NULL DEFAULT 'new',
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders (customer, amount)
SELECT 'customer_' || g, (g % 500) + 0.99
FROM generate_series(1, 1000) AS g;CREATE DATABASE
You are now connected to database "wallab" as user "postgres".
CREATE TABLE
INSERT 0 1000Check: SELECT count(*) FROM orders; returns 1000. The LSNs and file numbers in the following steps come from a freshly created cluster. Yours will be different, but the pattern will be the same.
Look at the WAL settings and files
Step 2: Check the WAL settings
On db1, in psql as postgres. The context column tells you how a setting can be changed (Part 5): postmaster needs a restart, sighup needs a reload, internal was fixed when the cluster was created.
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('wal_level','wal_segment_size','wal_block_size',
'min_wal_size','max_wal_size','wal_keep_size',
'wal_buffers','full_page_writes','wal_compression',
'fsync','synchronous_commit','max_wal_senders',
'archive_mode')
ORDER BY name; name | setting | unit | context
--------------------+----------+------+------------
archive_mode | off | | postmaster
fsync | on | | sighup
full_page_writes | on | | sighup
max_wal_senders | 10 | | postmaster
max_wal_size | 1024 | MB | sighup
min_wal_size | 80 | MB | sighup
synchronous_commit | on | | user
wal_block_size | 8192 | | internal
wal_buffers | 512 | 8kB | postmaster
wal_compression | off | | superuser
wal_keep_size | 0 | MB | sighup
wal_level | replica | | postmaster
wal_segment_size | 16777216 | B | internal
(13 rows)These are the PostgreSQL 18 defaults on a fresh cluster. wal_buffers shows 512 blocks of 8 kB, which is 4 MB: the default of -1 picks 1/32 of shared_buffers (128 MB here), with a floor of 64 kB and a ceiling of one WAL segment. SHOW wal_buffers; prints it as 4MB.
Step 3: Look inside pg_wal
On db1, as a sudo user in the shell. WAL lives in the pg_wal directory inside the data directory.
sudo ls -l /var/lib/postgresql/18/main/pg_waltotal 16392
-rw------- 1 postgres postgres 16777216 Nov 10 12:47 000000010000000000000001
drwx------ 2 postgres postgres 4096 Nov 10 12:47 archive_status
drwx------ 2 postgres postgres 4096 Nov 10 12:47 summariesOne 16 MB segment so far. archive_status holds the .ready and .done marker files used by WAL archiving (Part 19), and summaries is used by WAL summarization for incremental backups. You can list the same files from SQL without shell access:
SELECT name, size, modification FROM pg_ls_waldir() ORDER BY name; name | size | modification
--------------------------+----------+------------------------
000000010000000000000001 | 16777216 | 2026-11-10 12:47:46+00
(1 row)pg_ls_waldir() is restricted to superusers and members of the pg_monitor role by default, so it is a good choice for monitoring scripts.
LSNs: addresses inside the WAL
Step 4: Read the current LSN and find its file
On db1, in psql as postgres, connected to wallab.
SELECT pg_current_wal_lsn(), pg_current_wal_insert_lsn(),
pg_current_wal_flush_lsn();
SELECT pg_walfile_name(pg_current_wal_lsn());
SELECT * FROM pg_walfile_name_offset(pg_current_wal_lsn());
SELECT * FROM pg_split_walfile_name('000000010000000000000001'); pg_current_wal_lsn | pg_current_wal_insert_lsn | pg_current_wal_flush_lsn
--------------------+---------------------------+--------------------------
0/1BCC3B0 | 0/1BCC3B0 | 0/1BCC3B0
(1 row)
pg_walfile_name
--------------------------
000000010000000000000001
(1 row)
file_name | file_offset
--------------------------+-------------
000000010000000000000001 | 12370864
(1 row)
segment_number | timeline_id
----------------+-------------
1 | 1
(1 row)| Function | What it returns (from the documentation) |
|---|---|
| pg_current_wal_insert_lsn() | The insert location: the logical end of the WAL right now. |
| pg_current_wal_lsn() | The write location: the end of what has been written out from the server's internal buffers. |
| pg_current_wal_flush_lsn() | The flush location: the end of what is known to be on durable storage. |
| pg_walfile_name(lsn) | The name of the WAL file holding that location. |
| pg_walfile_name_offset(lsn) | The file name plus the byte offset inside that file. |
| pg_split_walfile_name(name) | The segment number and timeline ID from a WAL file name. |
On an idle server all three LSNs are equal. Under load the insert LSN runs slightly ahead of the write and flush LSNs.
Read 0/1BCC3B0 as two hexadecimal numbers. 0x1BCC3B0 is 29,148,080 bytes from the start of the WAL. Segment 1 covers bytes 16,777,216 up to 33,554,431, so the record sits 29,148,080 minus 16,777,216 = 12,370,864 bytes into that file, exactly the file_offset shown.
The 24-character file name is three 8-character hex fields: the timeline (00000001, which changes after a point-in-time recovery or a standby promotion, Parts 20 and 36), then the part of the LSN before the slash (00000000), then the segment number within that 4 GB range (00000001). File names never wrap around; they just keep increasing.
Step 5: Measure the WAL of one UPDATE, twice
On db1, in psql as postgres, connected to wallab. \gset is a psql meta-command (Part 4) that stores a query result in a psql variable, so you can subtract two LSNs with pg_wal_lsn_diff().
CHECKPOINT;
SELECT pg_current_wal_lsn() AS before_lsn \gset
UPDATE orders SET status = 'paid' WHERE order_id = 42;
SELECT pg_current_wal_lsn() AS after_lsn \gset
SELECT :'before_lsn' AS before_lsn, :'after_lsn' AS after_lsn,
pg_wal_lsn_diff(:'after_lsn', :'before_lsn') AS wal_bytes;
SELECT pg_current_wal_lsn() AS before_lsn \gset
UPDATE orders SET status = 'shipped' WHERE order_id = 43;
SELECT pg_current_wal_lsn() AS after_lsn \gset
SELECT :'before_lsn' AS before_lsn, :'after_lsn' AS after_lsn,
pg_wal_lsn_diff(:'after_lsn', :'before_lsn') AS wal_bytes; before_lsn | after_lsn | wal_bytes
------------+-----------+-----------
0/1BCC4B8 | 0/1BD0DB0 | 18680
(1 row)
before_lsn | after_lsn | wal_bytes
------------+-----------+-----------
0/1BD0DB0 | 0/1BD0F08 | 344
(1 row)Two almost identical one-row updates: the first wrote 18,680 bytes of WAL, the second only 344. The difference is full page writes. The CHECKPOINT command forced a checkpoint, and the first change to each page after a checkpoint copies the whole 8 kB page into the WAL. The second update touched the same pages again, so only the small change records were needed.
Why bother? A crash in the middle of writing an 8 kB page can leave a page that is half old and half new, called a torn page. A small change record cannot repair that, but a full page image can. Because replay always starts at a checkpoint, one image per page per checkpoint cycle is enough. This is why checkpoints that run too often make WAL volume grow, which is the subject of Part 16.
Check: SELECT order_id, status FROM orders WHERE order_id IN (42, 43); shows paid and shipped.
Step 6: Read the WAL records with pg_waldump
On db1, as the postgres operating system user. pg_waldump prints WAL records in readable form. It needs read access to the data directory, so run it as postgres. It lives in the version's bin directory and is not on the default PATH on Ubuntu.
sudo -u postgres /usr/lib/postgresql/18/bin/pg_waldump \
-p /var/lib/postgresql/18/main/pg_wal \
-s 0/1BCC4B8 -e 0/1BD0F08| Option | What it does |
|---|---|
| -p path | Directory to search for WAL segment files. |
| -s start | WAL location at which to start reading. |
| -e end | Stop reading at this WAL location instead of the end of the log. |
rmgr: Heap len (rec/tot): 59/ 8243, tx: 755, lsn: 0/01BCC4B8, prev 0/01BCC440, desc: LOCK xmax: 755, off: 42, infobits: [LOCK_ONLY, EXCL_LOCK], flags: 0x00, blkref #0: rel 1663/16384/16386 blk 0 FPW
rmgr: Heap len (rec/tot): 73/ 2885, tx: 755, lsn: 0/01BCE508, prev 0/01BCC4B8, desc: UPDATE old_xmax: 755, old_off: 42, old_infobits: [], flags: 0x00, new_xmax: 0, new_off: 41, blkref #0: rel 1663/16384/16386 blk 8 FPW, blkref #1: rel 1663/16384/16386 blk 0
rmgr: Btree len (rec/tot): 53/ 7453, tx: 755, lsn: 0/01BCF050, prev 0/01BCE508, desc: INSERT_LEAF off: 44, blkref #0: rel 1663/16384/16398 blk 1 FPW
rmgr: Transaction len (rec/tot): 34/ 34, tx: 755, lsn: 0/01BD0D88, prev 0/01BCF050, desc: COMMIT 2026-11-10 12:48:53.626145 UTC
rmgr: Heap2 len (rec/tot): 56/ 56, tx: 0, lsn: 0/01BD0DB0, prev 0/01BD0D88, desc: PRUNE_ON_ACCESS snapshotConflictHorizon: 755, isCatalogRel: F, nplans: 0, nredirected: 0, ndead: 1, nunused: 0, dead: [42], blkref #0: rel 1663/16384/16386 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 756, lsn: 0/01BD0DE8, prev 0/01BD0DB0, desc: LOCK xmax: 756, off: 43, infobits: [LOCK_ONLY, EXCL_LOCK], flags: 0x00, blkref #0: rel 1663/16384/16386 blk 0
rmgr: Heap len (rec/tot): 122/ 122, tx: 756, lsn: 0/01BD0E20, prev 0/01BD0DE8, desc: UPDATE old_xmax: 756, old_off: 43, old_infobits: [], flags: 0x00, new_xmax: 0, new_off: 42, blkref #0: rel 1663/16384/16386 blk 8, blkref #1: rel 1663/16384/16386 blk 0
rmgr: Btree len (rec/tot): 64/ 64, tx: 756, lsn: 0/01BD0EA0, prev 0/01BD0E20, desc: INSERT_LEAF off: 46, blkref #0: rel 1663/16384/16398 blk 1
rmgr: Transaction len (rec/tot): 34/ 34, tx: 756, lsn: 0/01BD0EE0, prev 0/01BD0EA0, desc: COMMIT 2026-11-10 12:48:53.626989 UTCHow to read it:
- rmgr is the resource manager, the part of PostgreSQL that wrote the record: Heap for table rows, Btree for the primary key index, Transaction for the commit.
- tx is the transaction ID (Part 11). Transaction 755 is the first UPDATE and 756 the second.
- rel 1663/16384/16386 is tablespace OID / database OID / file number, the same numbers you saw as the path base/16384/16386 in Part 3. 16386 is orders and 16398 its primary key index.
- FPW marks a record that carries a full page image. Transaction 755 has three (heap block 0, heap block 8 and index block 1), and the total length jumps to thousands of bytes. Transaction 756 has none.
- Block 0 was full, so the new row version went to block 8 and needed a new index entry. In Part 13 terms this was not a HOT update.
The COMMIT record is what the client waited for. Once that record was flushed, the UPDATE was durable, even though block 8 itself was still only in memory.
Step 7: Measure a bulk insert and summarise it
On db1, in psql as postgres, connected to wallab.
CHECKPOINT;
SELECT pg_current_wal_lsn() AS before_lsn \gset
INSERT INTO orders (customer, amount)
SELECT 'customer_' || g, (g % 500) + 0.99
FROM generate_series(1001, 101000) AS g;
SELECT pg_current_wal_lsn() AS after_lsn \gset
SELECT :'before_lsn' AS before_lsn, :'after_lsn' AS after_lsn,
pg_size_pretty(pg_wal_lsn_diff(:'after_lsn', :'before_lsn')) AS wal_generated,
pg_size_pretty(pg_relation_size('orders')) AS table_size,
pg_size_pretty(pg_indexes_size('orders')) AS index_size; before_lsn | after_lsn | wal_generated | table_size | index_size
------------+-----------+---------------+------------+------------
0/1BD5C80 | 0/2C6B198 | 17 MB | 7544 kB | 2224 kB
(1 row)100,000 rows produced 17 MB of WAL for about 9.6 MB of table plus index. That surprises many people, but every row is logged once for the table and once for the index, and each record has its own header. Now ask pg_waldump for a summary instead of individual records (run as postgres in the shell):
sudo -u postgres /usr/lib/postgresql/18/bin/pg_waldump \
-p /var/lib/postgresql/18/main/pg_wal \
-s 0/1BD5C80 -e 0/2C6B198 -z| Option | What it does |
|---|---|
| -z, --stats | Display summary statistics (number and size of records and full-page images) instead of individual records. |
WAL statistics between 0/1BD5C80 and 0/2C6B198:
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
XLOG 1 ( 0.00) 49 ( 0.00) 8192 ( 49.09) 8241 ( 0.05)
Transaction 1 ( 0.00) 34 ( 0.00) 0 ( 0.00) 34 ( 0.00)
...
Heap 100000 ( 49.19) 10298375 ( 59.85) 2964 ( 17.76) 10301339 ( 59.81)
Btree 100273 ( 49.32) 6607458 ( 38.40) 5532 ( 33.15) 6612990 ( 38.40)
...
Sequence 3031 ( 1.49) 300069 ( 1.74) 0 ( 0.00) 300069 ( 1.74)
...
Total 203306 17205985 [99.90%] 16688 [0.10%] 17222673 [100%]One Heap record per row, one Btree record per index entry (plus a few page splits), and Sequence records for the identity column. Full page images are almost nothing here, because new pages created by an insert do not need an earlier image. The -z summary is the quickest way to answer "what is generating all this WAL?" on a real server.
Check: SELECT name, size FROM pg_ls_waldir() ORDER BY name; now lists two segments, 000000010000000000000001 and 000000010000000000000002, because the insert filled the first one.
Step 8: Watch WAL volume with pg_stat_wal and pg_stat_io
On db1, in psql as postgres. The cumulative statistics views give you WAL totals for the whole server without reading any files. Here the counters were reset, a workload was run, and the views were read from a new session.
SELECT pg_stat_reset_shared('wal');
\c wallab
INSERT INTO orders (customer, amount)
SELECT 'customer_' || g, 10.00 FROM generate_series(1, 50000) AS g;
UPDATE orders SET status = 'paid' WHERE order_id <= 20000;
\c postgres
SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal_bytes,
wal_buffers_full
FROM pg_stat_wal;
SELECT backend_type, writes, pg_size_pretty(write_bytes) AS written, fsyncs
FROM pg_stat_io
WHERE object = 'wal' AND writes > 0
ORDER BY write_bytes DESC; wal_records | wal_fpi | wal_bytes | wal_buffers_full
-------------+---------+-----------+------------------
158267 | 266 | 14 MB | 835
(1 row)
backend_type | writes | written | fsyncs
----------------+--------+------------+--------
client backend | 839 | 15 MB | 3
checkpointer | 1 | 8192 bytes | 1
walwriter | 1 | 8192 bytes | 1
(3 rows)wal_fpi counts full page images and wal_buffers_full counts how often WAL had to be written to disk because the WAL buffers were full. Here two large statements filled the 4 MB of WAL buffers 835 times, so the client backend wrote the WAL itself, as the pg_stat_io row shows. On a busy server with many committing clients, a steadily rising wal_buffers_full is the signal to consider a larger wal_buffers (a restart setting).
Segments: switching, recycling and size
Step 9: Switch to a new segment with pg_switch_wal()
On db1, in psql as postgres. pg_switch_wal() forces the server to close the current segment and start a new one, so the closed file can be archived. You will use it when testing WAL archiving in Part 19.
SELECT pg_walfile_name(pg_current_wal_lsn()) AS current_file;
SELECT pg_switch_wal();
SELECT pg_walfile_name(pg_current_wal_lsn()) AS current_file;
SELECT pg_switch_wal();
SELECT name, size FROM pg_ls_waldir() ORDER BY name; current_file
--------------------------
000000010000000000000005
(1 row)
pg_switch_wal
---------------
0/57D59F0
(1 row)
current_file
--------------------------
000000010000000000000006
(1 row)
pg_switch_wal
---------------
0/6000000
(1 row)
name | size
--------------------------+----------
000000010000000000000005 | 16777216
000000010000000000000006 | 16777216
000000010000000000000007 | 16777216
(3 rows)The first call returned the end of segment 5, and the server moved to segment 6. The second call found no new WAL since the switch, so, as the documentation says, it did nothing and returned the start of the current file (0/6000000). Segments 1 to 4 are gone: once a checkpoint no longer needs them, old segments are removed or recycled, which means renamed to become future segments. That is why segment 7 already exists before any WAL has been written to it.
Every switched segment is still 16 MB on disk, even if it holds only a few bytes of WAL. Do not call pg_switch_wal() in a tight loop.
Step 10: Set how much WAL is kept between checkpoints
The number of segment files in pg_wal depends on min_wal_size, max_wal_size and how much WAL recent checkpoint cycles generated:
| Setting | Default | What it does |
|---|---|---|
| max_wal_size | 1GB | Maximum size to let WAL grow during automatic checkpoints. A soft limit: heavy load, a failing archive_command or a high wal_keep_size can push past it. Larger values mean longer crash recovery. |
| min_wal_size | 80MB | While WAL disk use stays below this, old files are always recycled for future use instead of removed. |
| wal_keep_size | 0 | Minimum size of past WAL kept for standby servers that may need it (Part 33). |
All three can be changed with a reload. On db1, as a sudo user, edit the file:
sudo nano /etc/postgresql/18/main/postgresql.conf# before
max_wal_size = 1GB
min_wal_size = 80MB
# after
max_wal_size = 4GB
min_wal_size = 80MBApply it with a reload (no restart needed), then check:
sudo systemctl reload postgresql@18-main
sudo -u postgres psql -c "SELECT name, setting, unit, pending_restart FROM pg_settings WHERE name IN ('max_wal_size', 'min_wal_size');" name | setting | unit | pending_restart
--------------+---------+------+-----------------
max_wal_size | 4096 | MB | f
min_wal_size | 80 | MB | f
(2 rows)The server log also confirms it: LOG: parameter "max_wal_size" changed to "4GB". Ubuntu's postgresql.conf already contains uncommented max_wal_size = 1GB and min_wal_size = 80MB lines, so edit those rather than adding a second copy at the end of the file. How to size max_wal_size from your checkpoint pattern is covered in Part 16.
Undo: set the line back to max_wal_size = 1GB and reload again. SHOW max_wal_size; returns 1GB.
Step 11: Choose a segment size when you create a cluster
wal_segment_size showed context internal in Step 2. The documentation for initdb's --wal-segsize option says the size must be a power of 2 between 1 and 1024 MB, can only be set during initialization, and cannot be changed later. Bigger segments mean fewer files on servers that generate a lot of WAL. On Ubuntu, options after -- are passed to initdb. On db1, as a sudo user, create a throwaway cluster to see it:
sudo pg_createcluster 18 segtest --port 5434 -- --wal-segsize=64
sudo pg_ctlcluster 18 segtest start
sudo -u postgres psql -p 5434 -c "SHOW wal_segment_size;"
sudo ls -l /var/lib/postgresql/18/segtest/pg_wal| Option | What it does |
|---|---|
| 18 segtest | PostgreSQL version and the new cluster's name. |
| --port 5434 | Port for the new cluster, so it does not clash with 18 main on 5432. |
| -- --wal-segsize=64 | Everything after -- goes to initdb; this sets 64 MB WAL segments. |
wal_segment_size
------------------
64MB
(1 row)
total 65544
-rw------- 1 postgres postgres 67108864 Nov 10 12:50 000000010000000000000001
drwx------ 2 postgres postgres 4096 Nov 10 12:50 archive_status
drwx------ 2 postgres postgres 4096 Nov 10 12:50 summariessudo pg_dropcluster 18 segtest --stop
pg_lsclustersCheck: pg_lsclusters no longer lists segtest.
wal_level: how much the WAL records
The documentation describes three levels. Each level includes everything logged by the levels below it.
| wal_level | What it logs | Use it when |
|---|---|---|
| minimal | Only what is needed to recover from a crash or immediate shutdown. Skips row data for tables created or rewritten in the same transaction (CREATE TABLE, TRUNCATE, CLUSTER, REINDEX and similar). | Single servers with no archiving and no replication, for example a throwaway data-loading server. Needs max_wal_senders = 0. |
| replica (default) | Enough for WAL archiving, point-in-time recovery and streaming replication, including read-only queries on a standby. | Almost every production server. |
| logical | Everything in replica, plus what logical decoding needs to extract row changes. | Logical replication (Part 37) and change data capture tools. |
wal_level can only be set at server start. The old values archive and hot_standby from before PostgreSQL 9.6 are still accepted and mapped to replica. The documentation also warns that switching to minimal makes earlier base backups unusable for point-in-time recovery and for standby servers.
Step 12: Measure full page images with and without wal_compression
Before changing wal_level, take a baseline at replica. This also shows wal_compression, which compresses the full page images in WAL (off by default; the methods are pglz, lz4 and zstd, and the PGDG packages are built with lz4 and zstd support). A superuser can set it per session, so you can compare without touching the configuration. On db1, in psql as postgres, connected to wallab:
CREATE TABLE orders_a AS SELECT * FROM orders WHERE order_id <= 101000;
CREATE TABLE orders_b AS SELECT * FROM orders WHERE order_id <= 101000;
VACUUM orders_a, orders_b;
CHECKPOINT;
SELECT pg_current_wal_lsn() AS before_lsn \gset
UPDATE orders_a SET amount = amount + 1 WHERE order_id % 10 = 0;
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'before_lsn'))
AS wal_compression_off;
SET wal_compression = lz4;
CHECKPOINT;
SELECT pg_current_wal_lsn() AS before_lsn \gset
UPDATE orders_b SET amount = amount + 1 WHERE order_id % 10 = 0;
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'before_lsn'))
AS wal_compression_lz4;
RESET wal_compression; wal_compression_off
---------------------
2619 kB
(1 row)
wal_compression_lz4
---------------------
2041 kB
(1 row)The two tables are identical copies of the first 101,000 orders, and each update touches every page right after a checkpoint, so the WAL is mostly full page images. With lz4 the same update wrote about 22% less WAL. The documentation describes the trade-off: less WAL without extra risk of corruption, paid for with CPU for compression when writing WAL and decompression during replay. Your saving depends on how compressible your pages are, so measure it on your own data before setting it in postgresql.conf (it is reloadable). Keep orders_a and orders_b for now: 2619 kB is the baseline for Step 14.
Step 13: Change wal_level to logical (restart required)
On db1, as a sudo user. Edit the configuration file. On Ubuntu the line is commented out, which means the default (replica) is in use.
# before
#wal_level = replica # minimal, replica, or logical
# after
wal_level = logical # minimal, replica, or logicalA reload is not enough. If you try it, the server log says LOG: parameter "wal_level" cannot be changed without restarting the server and pg_settings shows pending_restart as true:
sudo systemctl reload postgresql@18-main
sudo -u postgres psql -c "SELECT name, setting, pending_restart FROM pg_settings WHERE name = 'wal_level';" name | setting | pending_restart
-----------+---------+-----------------
wal_level | replica | t
(1 row)Restart the cluster. This disconnects every session, so on a real server plan it in a maintenance window:
sudo systemctl restart postgresql@18-main
sudo -u postgres psql -c "SHOW wal_level;" wal_level
-----------
logical
(1 row)Step 14: Measure the extra WAL at logical
On db1, in psql as postgres, connected to wallab. Repeat Step 12's update on a third identical copy. REPLICA IDENTITY FULL makes the WAL carry the complete old row for every UPDATE and DELETE, which logical decoding uses when a table has no primary key. The documentation names it as the main reason logical produces more WAL.
CREATE TABLE orders_c AS SELECT * FROM orders WHERE order_id <= 101000;
ALTER TABLE orders_c REPLICA IDENTITY FULL;
VACUUM orders_c;
CHECKPOINT;
SELECT pg_current_wal_lsn() AS before_lsn \gset
UPDATE orders_c SET amount = amount + 1 WHERE order_id % 10 = 0;
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'before_lsn'))
AS wal_logical_full; wal_logical_full
------------------
3110 kB
(1 row)The same update wrote 3110 kB at logical with REPLICA IDENTITY FULL, against 2619 kB at replica in Step 12, about 19% more. With a primary key and the default replica identity the difference is smaller. Only use logical when something actually reads logical changes.
Clean up the copies: DROP TABLE orders_a, orders_b, orders_c;
Step 15: Undo: return wal_level to replica
On db1, as a sudo user. Put the comment marker back (or write wal_level = replica), then restart and check:
#wal_level = replica # minimal, replica, or logicalsudo systemctl restart postgresql@18-main
sudo -u postgres psql -c "SHOW wal_level;" wal_level
-----------
replica
(1 row)On a server that uses logical replication, lowering wal_level breaks it: logical slots need logical. Before lowering it, check SELECT slot_name, slot_type FROM pg_replication_slots; for rows with slot_type logical. Part 34 covers slots.
See WAL do its job: crash recovery
Step 16: Commit a row and crash the server
On db1, in psql as postgres, connected to wallab:
CHECKPOINT;
INSERT INTO orders (customer, amount) VALUES ('crash_test', 250.00);
SELECT customer, amount FROM orders WHERE customer = 'crash_test';
SELECT pg_current_wal_lsn();CHECKPOINT
INSERT 0 1
customer | amount
------------+--------
crash_test | 250.00
(1 row)
pg_current_wal_lsn
--------------------
0/B5AAA80
(1 row)The INSERT committed after the last checkpoint, so its data page has very likely not been written to the table file yet. Its only durable copy is in the WAL. On db1, as a sudo user, crash the cluster and read the control file with pg_controldata:
sudo pg_ctlcluster 18 main stop -m immediate
sudo -u postgres /usr/lib/postgresql/18/bin/pg_controldata -D /var/lib/postgresql/18/main \
| grep -E "cluster state|Latest checkpoint location|REDO location|wal_level setting|Bytes per WAL segment"| Option | What it does |
|---|---|
| stop -m immediate | pg_ctlcluster passes the shutdown mode to pg_ctl; immediate aborts all server processes without a shutdown checkpoint. |
| -D | pg_controldata: the data directory whose pg_control file to read. |
Database cluster state: in production
Latest checkpoint location: 0/B5A7298
Latest checkpoint's REDO location: 0/B5A7240
wal_level setting: replica
Bytes per WAL segment: 16777216The server is down but the control file still says "in production": it was never shut down cleanly. The REDO location (0/B5A7240) is where replay will start. Our INSERT is after it, before 0/B5AAA80.
Step 17: Start the server and read the recovery messages
On db1, as a sudo user:
sudo systemctl start postgresql@18-main
sudo tail -8 /var/log/postgresql/postgresql-18-main.log2026-11-10 12:56:08.845 UTC [3468] LOG: database system was interrupted; last known up at 2026-11-10 12:56:08 UTC
2026-11-10 12:56:08.891 UTC [3468] LOG: database system was not properly shut down; automatic recovery in progress
2026-11-10 12:56:08.892 UTC [3468] LOG: redo starts at 0/B5A7240
2026-11-10 12:56:08.893 UTC [3468] LOG: invalid record length at 0/B5AAA80: expected at least 24, got 0
2026-11-10 12:56:08.893 UTC [3468] LOG: redo done at 0/B5AAA58 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2026-11-10 12:56:08.895 UTC [3466] LOG: checkpoint starting: end-of-recovery immediate wait
2026-11-10 12:56:08.898 UTC [3466] LOG: checkpoint complete: wrote 4 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.005 s; sync files=6, longest=0.001 s, average=0.001 s; distance=14 kB, estimate=14 kB; lsn=0/B5AAA80, redo lsn=0/B5AAA80
2026-11-10 12:56:08.900 UTC [3462] LOG: database system is ready to accept connections- redo starts at 0/B5A7240: exactly the REDO location from pg_controldata.
- invalid record length at 0/B5AAA80 ... got 0: this looks alarming but is normal. Replay reads until it finds no more valid records, and 0/B5AAA80 is the LSN we noted before the crash.
- redo done and an end-of-recovery checkpoint: replayed changes are now in the data files.
Check: the committed row survived.
SELECT customer, amount FROM orders WHERE customer = 'crash_test'; customer | amount
------------+--------
crash_test | 250.00
(1 row)One side effect: an immediate shutdown or crash resets the cumulative statistics, so pg_stat_wal starts again from zero after this step.
Put WAL on its own disk
The documentation recommends keeping WAL on a different disk from the data files: WAL writes are sequential and every commit waits for them, so they should not compete with random data-file I/O. A separate volume also keeps a WAL pile-up from filling the data disk. For a new cluster, pass initdb's --waldir option (for example sudo pg_createcluster 18 main -- --waldir=/pgwal/18-main). For an existing cluster, the documented method is to move pg_wal while the server is shut down and leave a symbolic link behind.
Step 18: Move pg_wal to /pgwal and link it
On db1, as a sudo user (the mv and ln commands run as the postgres user so the files keep their owner):
sudo systemctl stop postgresql@18-main
sudo mkdir -p /pgwal
sudo chown postgres:postgres /pgwal
sudo chmod 700 /pgwal
sudo -u postgres mv /var/lib/postgresql/18/main/pg_wal /pgwal/18-main
sudo -u postgres ln -s /pgwal/18-main /var/lib/postgresql/18/main/pg_wal
sudo ls -ld /var/lib/postgresql/18/main/pg_wal
sudo systemctl start postgresql@18-main
pg_lsclusterslrwxrwxrwx 1 postgres postgres 14 Nov 10 12:50 /var/lib/postgresql/18/main/pg_wal -> /pgwal/18-main
Ver Cluster Port Status Owner Data directory Log file
18 main 5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.logpg_wal is now a symbolic link and the cluster is online. Check: sudo ls -l /pgwal/18-main lists the segment files, and SELECT count(*) FROM pg_ls_waldir(); in psql still returns them through the link.
Undo (lab): stop the cluster, remove the link, move the directory back, start again.
sudo systemctl stop postgresql@18-main
sudo -u postgres rm /var/lib/postgresql/18/main/pg_wal
sudo -u postgres mv /pgwal/18-main /var/lib/postgresql/18/main/pg_wal
sudo systemctl start postgresql@18-mainCommon mistakes and how to fix them
Deleting files from pg_wal to free disk space
pg_wal looks like a folder of old logs, but PostgreSQL needs every file from the last checkpoint onwards to start. On a throwaway cluster that was stopped uncleanly, removing its segment files gave this at startup:
PANIC: could not locate a valid checkpoint record at 0/175EC38
pg_ctl: could not start serverFix: never delete or move files in pg_wal by hand. PostgreSQL removes and recycles them itself after checkpoints. If pg_wal is growing, find what is holding WAL back (next mistake). If the files are already gone, restore from backup; Part 47 covers disk-full emergencies.
WAL files are not being removed
The documentation lists what keeps old segments in pg_wal: a failing or slow archive_command, a replication slot whose consumer is behind or gone, a large wal_keep_size, and WAL summarization that has not caught up. Check the first two:
SELECT archived_count, failed_count, last_failed_wal FROM pg_stat_archiver;
SELECT slot_name, active, wal_status,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots; archived_count | failed_count | last_failed_wal
----------------+--------------+-----------------
0 | 0 |
(1 row)
slot_name | active | wal_status | retained
-----------+--------+------------+----------
(0 rows)Fix: a rising failed_count means fix the archive command (Part 19). An inactive slot with a large retained value is a stale slot; drop it only when you are sure nothing will use it (Part 34).
FATAL: WAL streaming ("max_wal_senders" > 0) requires "wal_level" to be "replica" or "logical"
Setting wal_level = minimal and restarting gave this, and the cluster stayed down:
FATAL: WAL streaming ("max_wal_senders" > 0) requires "wal_level" to be "replica" or "logical"
pg_ctl: could not start serverFix: minimal also needs max_wal_senders = 0 (a restart setting). On most servers the real fix is to go back to wal_level = replica and start again. Archiving needs replica as well: archive_mode cannot be enabled at minimal.
ERROR: logical decoding requires "wal_level" >= "logical"
Creating a logical replication slot at the default level fails:
SELECT pg_create_logical_replication_slot('test_slot', 'pgoutput');ERROR: logical decoding requires "wal_level" >= "logical"Fix: set wal_level = logical and restart, as in Step 13. Logical replication subscriptions and change data capture tools give the same error for the same reason.
pg_waldump: error: could not locate WAL file
Running pg_waldump on a segment that has already been removed or recycled:
pg_waldump: error: could not locate WAL file "000000010000000000000001"Fix: use SELECT name FROM pg_ls_waldir() ORDER BY name; to see which segments still exist, or start from the current position with -s. Also remember that pg_waldump needs the postgres OS user, and the documentation notes it can give wrong results when the server is running, so treat output from a live server as a guide.
Turning off fsync or full_page_writes for speed
Both settings are reloadable, which makes them tempting. The documentation is clear that turning off fsync can cause unrecoverable data corruption after a power failure or OS crash, and that turning off full_page_writes carries similar risks. Only do it for a database you can rebuild completely from elsewhere. If you need faster commits for non-critical data, SET synchronous_commit = off in that session is the documented safer alternative: a crash may lose the last few commits (at most three times wal_writer_delay), but the database stays consistent.
Practice exercise
- In wallab, run
CHECKPOINT;, thenUPDATE orders SET status = 'cancelled' WHERE order_id BETWEEN 5001 AND 6000;and measure the WAL it generated withpg_current_wal_lsn(),\gsetandpg_wal_lsn_diff(). Run the same UPDATE again straight away and measure again. Which run wrote more WAL, and why? - Find the WAL file name and byte offset of your current LSN, and confirm that the file appears in
pg_ls_waldir().
Quick reference
# WAL settings and their context (restart, reload, fixed)
sudo -u postgres psql -c "SELECT name, setting, unit, context FROM pg_settings WHERE name LIKE '%wal%' ORDER BY name;"
# List WAL segment files (shell or SQL)
sudo ls -l /var/lib/postgresql/18/main/pg_wal
sudo -u postgres psql -c "SELECT name, size FROM pg_ls_waldir() ORDER BY name;"
# Current LSN, its file and offset
sudo -u postgres psql -c "SELECT pg_current_wal_lsn(), * FROM pg_walfile_name_offset(pg_current_wal_lsn());"
# WAL bytes between two LSNs
sudo -u postgres psql -c "SELECT pg_wal_lsn_diff('0/1BD0DB0', '0/1BCC4B8');"
# Read WAL records / summarise an LSN range
sudo -u postgres /usr/lib/postgresql/18/bin/pg_waldump -p /var/lib/postgresql/18/main/pg_wal -s 0/1BCC4B8 -e 0/1BD0F08
sudo -u postgres /usr/lib/postgresql/18/bin/pg_waldump -p /var/lib/postgresql/18/main/pg_wal -s 0/1BD5C80 -e 0/2C6B198 -z
# Server-wide WAL statistics (PostgreSQL 18)
sudo -u postgres psql -c "SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full FROM pg_stat_wal;"
sudo -u postgres psql -c "SELECT backend_type, writes, write_bytes, fsyncs FROM pg_stat_io WHERE object = 'wal';"
# Force a segment switch
sudo -u postgres psql -c "SELECT pg_switch_wal();"
# Reloadable WAL settings (max_wal_size, min_wal_size, wal_keep_size, wal_compression)
sudo nano /etc/postgresql/18/main/postgresql.conf
sudo systemctl reload postgresql@18-main
# wal_level needs a restart
sudo systemctl restart postgresql@18-main
sudo -u postgres psql -c "SHOW wal_level;"
# New cluster with 64 MB segments (throwaway) and its cleanup
sudo pg_createcluster 18 segtest --port 5434 -- --wal-segsize=64
sudo pg_dropcluster 18 segtest --stop
# Control file: checkpoint and REDO locations
sudo -u postgres /usr/lib/postgresql/18/bin/pg_controldata -D /var/lib/postgresql/18/main
# What is holding WAL back
sudo -u postgres psql -c "SELECT failed_count, last_failed_wal FROM pg_stat_archiver;"
sudo -u postgres psql -c "SELECT slot_name, active, wal_status, restart_lsn FROM pg_replication_slots;"Summary and what's next
- WAL records every change before the data files are written; a commit waits only for its WAL to be flushed.
- WAL lives in /var/lib/postgresql/18/main/pg_wal as 16 MB segments named timeline + LSN high part + segment number.
- An LSN is a byte position;
pg_wal_lsn_diff()turns two LSNs into WAL bytes, andpg_walfile_name()finds the file. - The first change to a page after a checkpoint writes a full page image;
wal_compressioncan shrink those images. - wal_level replica is the right default; logical adds data for logical decoding; both need a restart to change.
pg_stat_waland, new in PostgreSQL 18,pg_stat_iorows for WAL show how much WAL the server writes.- Never delete files from pg_wal; find the archive failure or stale slot that is holding them instead.
- Crash recovery replays WAL from the REDO location in pg_control, and committed rows survive.
In Part 16 we look at checkpoints and the background writer, and how to tune them for steady performance.