CertGrid CertGrid
Hands-on Lab·PostgreSQL

PostgreSQL Crash Recovery and Data Checksums

`kill -9` the whole cluster with a transaction open, then read what recovery did. Five committed rows survived, the sixth was never there.

Troubleshooting Guide 47 of 47 Advanced

Written against the versions above. `pg_checksums` can also enable checksums on an existing cluster offline, from PostgreSQL 12. Before that it was initdb-time only.

Every command on this page ran on db-b01.
Server NameIP AddressOSRolesCPURAMHDD
db-b01192.168.0.82Ubuntu 26.04 LTSStandby / Replica / Replica Set Member 22 Core4 GB50 GB

Before you start

  1. What the control file says while the server is up

    data_checksums is on - every data page carries a checksum verified on read, which is how torn or silently corrupted pages get caught rather than served.

    pg_controldata reads the 8 kB control file directly, no server needed:

    Database cluster state: in production - the flag recovery keys off. Set on start, cleared on clean shutdown.

    Latest checkpoint's REDO location: 0/17000028 - where recovery would begin. Everything written after this point exists only in WAL until the next checkpoint.

    TimeLineID: 2 - this host is a promoted standby, so it is on the second timeline.

    bash Example session 
    sudo -u postgres psql -c "SHOW data_checksums" data_checksums---------------- on(1 row)sudo -u postgres /usr/lib/postgresql/18/bin/pg_controldata /var/lib/postgresql/18/main | head -12pg_control version number:            1800Catalog version number:               202506291Database system identifier:           7678708104239117136Database cluster state:               in productionpg_control last modified:             Thu 27 Aug 2026 04:37:17 PM UTCLatest checkpoint location:           0/17000028Latest checkpoint's REDO location:    0/17000028Latest checkpoint's REDO WAL file:    000000020000000000000017Latest checkpoint's TimeLineID:       2Latest checkpoint's PrevTimeLineID:   2Latest checkpoint's full_page_writes: onLatest checkpoint's NextXID:          0:849

    Expected resultChecksums on, state in production, timeline 2.

    Success conditionYou can read a cluster's recovery state without connecting to it.

  2. Write something committed and something not

    Five rows inserted and committed. Then a second session runs a file that opens a transaction, inserts a sixth row and sleeps inside it.

    The other session sees 5. The sixth row exists in that backend's snapshot and in WAL, and in nobody else's view of the world - which is exactly what an uncommitted row is.

    Running the SQL from a file rather than -c avoids nesting quotes through sudo, bash -c and psql. That is a practical point rather than a PostgreSQL one, and it prevents a whole family of "column does not exist" errors caused by shell quoting turning a string literal into an identifier.

    bash Example session 
    sudo -u postgres psql -d appdb -c "CREATE TABLE crashtest (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, note text)"CREATE TABLEsudo -u postgres psql -d appdb -c "INSERT INTO crashtest (note) SELECT 'committed row ' || g FROM generate_series(1,5) g"INSERT 0 5sudo -u postgres psql -d appdb -c "SELECT count(*) AS committed_rows FROM crashtest" committed_rows----------------              5(1 row)sudo -u postgres tee /tmp/uncommitted.sql > /dev/null <<'SQL'BEGIN;INSERT INTO crashtest (note) VALUES ('uncommitted row');SELECT pg_sleep(300);SQLsudo cat /tmp/uncommitted.sqlBEGIN;INSERT INTO crashtest (note) VALUES ('uncommitted row');SELECT pg_sleep(300);sudo bash -c 'nohup sudo -u postgres psql -d appdb -f /tmp/uncommitted.sql >/tmp/uncommitted.log 2>&1 & sleep 3; echo holder started'holder startedsudo -u postgres psql -d appdb -c "SELECT count(*) AS visible_to_others FROM crashtest" visible_to_others-------------------                 5(1 row)

    Expected resultFive rows visible, a sixth in flight.

    Success conditionYou have committed and uncommitted work in the same table.

  3. Kill it

    pkill -9 -x postgres kills the postmaster and every backend with SIGKILL. There is no shutdown, no final checkpoint, no chance to flush. It is the closest thing to pulling the power cable.

    systemd notices: is-active returns failed.

    Starting it produces the four lines that describe a crash recovery:

    database system was interrupted; last known up at 16:37:17

    database system was not properly shut down; automatic recovery in progress - the control file said in production, so the shutdown was not clean.

    redo starts at 0/170000A0 - the last checkpoint's REDO location, exactly as pg_controldata reported it.

    redo done at 0/17028FD8 - 166 kB of WAL replayed, in under a millisecond.

    Then ready to accept connections. Nothing was asked of the operator. Recovery is automatic and not optional; there is no flag to skip it. Recovery time is bounded by how much WAL was written since the last checkpoint, which is what max_wal_size and checkpoint_timeout really control.

    bash Example session 
    sudo pkill -9 -x postgressystemctl is-active postgresql@18-mainfailed[exit 3]sudo systemctl start postgresql@18-mainsudo grep -E "was interrupted|not properly shut down|redo starts at|redo done|ready to accept" /var/log/postgresql/postgresql-18-main.log | tail -52026-08-27 16:38:09.739 UTC [23474] LOG:  database system was interrupted; last known up at 2026-08-27 16:37:17 UTC2026-08-27 16:38:10.614 UTC [23474] LOG:  database system was not properly shut down; automatic recovery in progress2026-08-27 16:38:10.617 UTC [23474] LOG:  redo starts at 0/170000A02026-08-27 16:38:10.618 UTC [23474] LOG:  redo done at 0/17028FD8 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s2026-08-27 16:38:10.671 UTC [23468] LOG:  database system is ready to accept connections

    Expected resultautomatic recovery in progress, redo start and end, then ready.

    Success conditionYou can read a crash recovery in the log and know it completed.

  4. Check what survived

    Five rows. The five committed ones, ids 1 to 5, exactly as written.

    The sixth is gone, and its session's log shows why: BEGIN, INSERT 0 1, then server closed the connection unexpectedly. The insert *succeeded*. It was in WAL. Recovery replayed it and then rolled it back, because the transaction never committed.

    That is durability stated precisely: committed means it survives, and nothing else does. INSERT 0 1 is not a promise; COMMIT is.

    The identity column is worth noticing too - the sixth row consumed an id, and that id is not reused. Sequences are deliberately not transactional, which is why identity columns have gaps after any rollback, crash or otherwise.

    pg_control_checkpoint() shows the checkpoint written after recovery finished.

    bash Example session 
    sudo -u postgres psql -d appdb -c "SELECT count(*) AS rows_after_crash FROM crashtest" rows_after_crash------------------                5(1 row)sudo -u postgres psql -d appdb -c "SELECT id, note FROM crashtest ORDER BY id" id |      note----+-----------------  1 | committed row 1  2 | committed row 2  3 | committed row 3  4 | committed row 4  5 | committed row 5(5 rows)cat /tmp/uncommitted.logBEGININSERT 0 1psql:/tmp/uncommitted.sql:3: server closed the connection unexpectedly	This probably means the server terminated abnormally	before or while processing the request.psql:/tmp/uncommitted.sql:3: error: connection to server was lostsudo -u postgres psql -c "SELECT checkpoint_lsn, redo_lsn FROM pg_control_checkpoint()" checkpoint_lsn |  redo_lsn----------------+------------ 0/17029000     | 0/17029000(1 row)

    Expected resultFive rows; INSERT 0 1 in the log of the row that no longer exists.

    Success conditionYou can state exactly what a crash does and does not lose.

  5. Verify every page on disk

    With the cluster stopped, pg_controldata reports Database cluster state: shut down - the clean-shutdown flag. Compare that with in production earlier; that one field is the whole basis for deciding whether to run recovery.

    pg_checksums --check then reads every page:

    Files scanned: 1576 / Blocks scanned: 4993 / Bad checksums: 0

    A hard kill mid-transaction damaged nothing, which is what full page writes and WAL exist to guarantee.

    It requires the cluster to be stopped - it reads files directly and a running server would be writing underneath it. That makes it a maintenance-window tool, and the one worth reaching for after a storage incident, a restore from an unfamiliar backup, or any "the disk did something odd" report.

    Starting the cluster sets the state back to in production, and the next clean shutdown will clear it again.

    bash Example session 
    sudo systemctl stop postgresql@18-mainsudo -u postgres /usr/lib/postgresql/18/bin/pg_controldata /var/lib/postgresql/18/main | grep -E "cluster state|checksum"Database cluster state:               shut downData page checksum version:           1sudo -u postgres /usr/lib/postgresql/18/bin/pg_checksums --check -D /var/lib/postgresql/18/mainChecksum operation completedFiles scanned:   1576Blocks scanned:  4993Bad checksums:  0Data checksum version: 1sudo -u postgres /usr/lib/postgresql/18/bin/pg_controldata /var/lib/postgresql/18/main | grep -E "cluster state"Database cluster state:               in productionsudo -u postgres psql -d appdb -c "DROP TABLE crashtest"DROP TABLE

    Expected resultshut down, 4,993 blocks scanned, zero bad checksums.

    Success conditionYou can prove a cluster's pages are intact after an incident.

Troubleshooting

Official sources

← All PostgreSQL guides