Server command-line tools: pgbench, pg_waldump, pg_checksums, pg_amcheck and friends

conceptThe concept

whyWhy it matters

During incidents you need answers fast: was the server shut down cleanly, is that index corrupt, did the backup finish, why is WAL growing. Each question has a dedicated tool. Equally important is knowing which tools change data: pg_resetwal and pg_checksums --enable modify the data directory, and the first can make corruption permanent.

Use caseReal-world scenario

After a storage incident a replica logs a checksum failure. The DBA runs pg_amcheck --heapallindexed --jobs 4 on the primary to confirm the primary is clean, checks pg_stat_database.checksum_failures on both, rebuilds the replica from a fresh base backup, and verifies that backup with pg_verifybackup before trusting it.

Try itExamples

Run and edit these in the interactive lesson.

SELECT pg_control_version, catalog_version_no, system_identifier > 0 AS has_system_id
FROM pg_control_system();
SELECT checkpoint_lsn, redo_lsn, redo_wal_file, timeline_id, checkpoint_time IS NOT NULL AS has_time
FROM pg_control_checkpoint();
SELECT database_block_size, blocks_per_segment, wal_block_size, data_page_checksum_version
FROM pg_control_init();

SELECT pg_current_wal_lsn() AS current_lsn,
       pg_walfile_name(pg_current_wal_lsn()) AS current_file;
SELECT file_name, file_offset FROM pg_walfile_name_offset(pg_current_wal_lsn());
SELECT segment_number, timeline_id FROM pg_split_walfile_name(pg_walfile_name(pg_current_wal_lsn()));

SHOW data_checksums;
SELECT datname, checksum_failures, checksum_last_failure
FROM pg_stat_database
WHERE datname = current_database();

BEGIN;
CREATE EXTENSION amcheck;
SELECT c.relname AS index_name, bt_index_check(c.oid, true) IS NOT NULL AS checked
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am ON am.oid = c.relam
WHERE i.indrelid = 'orders'::regclass AND am.amname = 'btree'
ORDER BY c.relname;
SELECT count(*) AS heap_problems FROM verify_heapam('orders');
ROLLBACK;

# benchmark: initialise scale 100 (10 million accounts), then 32 clients for 10 minutes
pgbench -i -s 100 bench
pgbench -c 32 -j 8 -T 600 -P 30 bench
pgbench -c 32 -j 8 -T 600 -M prepared -f hot_query.sql bench   # your own workload

pg_controldata "$PGDATA" | grep -E 'cluster state|REDO location|TimeLineID'
pg_waldump --stats=record -p "$PGDATA/pg_wal" 00000001000000A20000004F
pg_checksums --check -D "$PGDATA"          # server must be cleanly stopped
pg_amcheck --all --heapallindexed --jobs 4
pg_verifybackup /backups/base/2026-10-08
vacuumdb --all --analyze-in-stages --jobs 8    # right after pg_upgrade
reindexdb --concurrently --jobs 4 shop
pg_test_fsync -f /pgdata/fsync_test
pg_isready -h db1 -p 5432 && echo accepting

internalsUnder the hood

The control file (global/pg_control, 8 kB) records the latest checkpoint location, the REDO pointer where crash recovery starts, the timeline, and compile-time parameters such as block and WAL segment size. WAL file names are 24 hex digits: timeline, then the high and low parts of the segment number. Checksums are computed per page when it is written out and verified when it is read in; a failure raises an error for that page rather than returning corrupted data. amcheck verifies B-tree invariants (keys in order within and across pages); with heapallindexed it also checks that every heap tuple has an index entry.

Avoid / preferDo this, not that

# "could not locate a valid checkpoint record"
pg_resetwal -f "$PGDATA"

# stop, copy the whole data directory aside, then
# restore from backup and replay WAL to the latest point
# pg_resetwal only on a copy, only to extract data with pg_dump

pgbench -c 1 -T 10 bench   # "the server does 900 TPS"

pgbench -i -s 100 bench
pgbench -c 32 -j 8 -T 600 -P 30 bench

PitfallsCommon mistakes

• Running pg_checksums on a running server; it requires a clean shutdown.
• Treating a backup as good without pg_verifybackup or a test restore.
• Using bt_index_parent_check on a busy primary; it takes a ShareLock that blocks writes.
• Skipping vacuumdb --analyze-in-stages after pg_upgrade, then wondering why every plan is bad.

LimitsWhen not to use it

Do not use pgbench numbers from the default TPC-B-like script to predict your application's throughput; it measures a tiny update-heavy workload. Use a custom script with your real queries. Do not schedule pg_amcheck with heapallindexed against every table nightly on a large database without measuring its I/O; it reads every heap page.

SecuritySecurity notes

These programs read the data directory directly and must run as the OS user that owns it, which is equivalent to superuser. Do not give that account to people who only need SQL access. pg_waldump output contains row data from WAL records; treat it like a database dump.

ChallengeTry it yourself

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), redo_lsn) AS bytes_since_redo,
       redo_wal_file
FROM pg_control_checkpoint();

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Upgrading a cluster
• pg_upgrade
• REINDEX