There are times due to one reason or other Postgres database might report errors like below:
invalid page in block 1 of relation base/*/*This indicates the one of the databases on the server was corrupted due to mostly a hardware failure e.g. bad sectors in a hard disk. It is always better to try to recover the data or repair the hard disk as soon as this is detected; the chances to recover the data decreases over time. Follow the steps below to recover the databases with as little data loss as possible.
- Login to the postgresql cli
- Run the below code one after the other
- SET zero_damaged_pages=on;
- VACUUM FULL;
- VACUUM FREEZE;
- REINDEX database databasename;
When you run VACCUM commands below warnings will show once the bad sector/data is encountered.
WARNING: invalid page in block 1 of relation base/*/*; zeroing out page
ERROR: index "indexname" contains unexpected zero page at block 1
HINT: Please REINDEX it.Once the reindex step mentioned above is run, this will fix itself.
Repeat the process for all the databases on the server. Once complete the Postgres database should be back online with minimal data loses.
Below are few advanced articles you can follow to have more granular control of the recovery process.
Tracking Down Database Corruption With psql.
S6: Invalid page / page verification failed (data corrupted)