Data corruption is the unintended alteration of stored data so that it no longer represents its intended content. In PostgreSQL, ignored log errors can expose filesystem, hardware, or software problems before they become major data-loss incidents. section 7.3.4
Common paths to corruption and the practices that limit its impact include:
- Investigate messages such as invalid pages or short block reads; check the underlying hardware and consider pg_checksums or other diagnostic tools. section 7.3.4
- Use PostgreSQL-aware, automated backups with redundant copies, test complete restores, and use continuous archiving with Point-in-Time Recovery to return to a state before damage. section 9.7
- During migration, preserve the source encoding, convert deliberately to UTF-8, remove unsupported characters, configure the target correctly, and test with realistic data. section 10.3
A log entry worth investigating
A query against audit.audit_log reports that PostgreSQL could read none of the expected 8192 bytes from a relation block, followed by “invalid page.” Treat this as a possible corruption signal rather than an isolated query failure: inspect hardware and run appropriate diagnostics before the problem escalates. section 7.3.4
2024-09-21 14:45:11.172 BST [3281235] ERROR: could not read block 8185 in file "base/16384/2613": read only 0 of 8192 bytes 2024-09-21 14:45:11.172 BST [3281235] CONTEXT: while reading block 8185 of relation base/16384/2613 2024-09-21 14:45:11.172 BST [3281235] STATEMENT: SELECT * FROM audit.audit_log WHERE tstamp > '2024-09-21'; 2024-09-21 14:45:11.173 BST [3281235] LOG: invalid page in block 8185 of relation base/16384/2613 2024-09-21 14:45:11.173 BST [3281235] HINT: This could be a corruption issue caused by hardware problems or a software bug. Check for hardware issues and consider running pg_checksums or other diagnostic tools.
Inspect the complete PostgreSQL error, context, statement, and diagnostic hint for a concrete corruption investigation. section 7.3.4
iconv -f latin1 -t utf8 prod_dump_latin1.sql -o prod_dump_utf8.sql
Use the encoding-conversion command as a concrete migration step before importing cleaned data into a UTF-8 database. section 10.3
