How to fix Data corrupted in PostgreSQL

Connect to the database and check if checksums are enabled:

SHOW data_checksums;

If the result is off, your cluster was not initialized with checksums and you may already have undetected corruption. If on, you have corruption detection enabled and should investigate further.

If you have a PostgreSQL replica/follower, the corrupted page may not exist there (unless the replica was created from a bad base backup). Verify the replica is healthy:

# On replica, check replication lag
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

# Try the failing query on the replica
SELECT ... FROM <corrupted_table> LIMIT 1;

If the replica is healthy and the query succeeds, fail over to the replica immediately.

Make a file-system-level copy of your data directory before attempting any repairs. This is crucial:

sudo systemctl stop postgresql
# Or for older systems:
sudo service postgresql stop

# Make a copy of the entire data directory
sudo cp -r /var/lib/postgresql/12/main /var/lib/postgresql/12/main.backup

Do not use pg_dump for this—file-system copies preserve the ability to replay WAL.

If you maintain base backups and WAL archives:

# Restore to a point before corruption occurred
cp -r /path/to/base/backup /var/lib/postgresql/12/main
cp /path/to/wal/archives/* /var/lib/postgresql/12/main/pg_wal/

# Start the server
sudo systemctl start postgresql

Try the failing query. If successful, you've recovered from backup. If not, try an older base backup.

If recovery from backups is not an option and the corrupted data exists elsewhere, configure PostgreSQL to skip damaged pages:

# Edit postgresql.conf
sudo nano /etc/postgresql/12/main/postgresql.conf

# Add or modify these settings:
zero_damaged_pages = on
ignore_checksum_failure = on

Start the server and run VACUUM to identify damaged pages:

sudo systemctl start postgresql

# Connect and vacuum the corrupted table
VACUUM FULL VERBOSE ANALYZE <corrupted_table>;

# Reindex the database
REINDEX DATABASE <database_name>;

Note: This approach may return incorrect data from damaged pages before they are zeroed.

After recovery, check for additional corruption using pg_amcheck (PostgreSQL 13+):

# Create the amcheck extension if not present
sudo -u postgres psql -d <database> -c "CREATE EXTENSION IF NOT EXISTS amcheck;"

# Run corruption checks in parallel
pg_amcheck -d <database> --heapallindexed -j 4

# Or check a specific table
SELECT * FROM verify_heapam('<table_name>'::regclass);

Any errors from amcheck indicate remaining corruption that needs investigation.