Data integrity depends on preserving accuracy, consistency, and recoverability as data moves through storage, migration, and operations. A careless encoding conversion can accept characters that cannot be decoded back, causing irreversible loss. section 10.3
Protect integrity during a PostgreSQL migration by following a controlled path:
- Identify the source encoding and export using that encoding. section 10.3
- Convert the export to UTF-8, remove characters PostgreSQL cannot accept, and configure the target database with UTF8 and an appropriate collation. section 10.3
- Import only after testing with real or realistic data; tools such as iconv, pgloader, and ora2pg can assist. section 10.3
An encoding failure during migration
A staging restore can fail with `ERROR: invalid byte sequence for encoding "UTF8": 0xe9` or a null-byte error. Convert the dump with `iconv -f latin1 -t utf8`, remove null bytes with `tr` or `sed`, then import the cleaned file after validating representative data. section 10.3
Integrity threats also appear during routine administration. Ignored PostgreSQL logs may expose filesystem or hardware faults before they become major data-loss incidents; block-read errors and invalid pages warrant hardware checks and diagnostic tools such as pg_checksums. 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 diagnostic log showing a failed block read, invalid page, affected relation, and the recommendation to check hardware and run corruption diagnostics. section 7.3.4
