I run a small website which uses a Postgres database. There are regular back ups based on the amount of traffic we have every few days but there was a recent surge of activity just before a power cut which caused 24 user accounts (of our website) to become corrupted.
I followed the instructions set forth on the Postgres site for corruption, made a file system backup, repaired the ext file system, and did what I could for the database.
There are three columns in a single table however that are vital for user authentication and these columns remain corrupted.
Upon trying to select data from one of these columns, the following error is returned:
ERROR: missing chunk number 0 for toast value 16935 in pg_toast_2619
Attempting pg_dump returns:
pg_dump: SQL command failed pg_dump: Error message from server: ERROR: missing chunk number 0 for toast value 11651 in pg_toast_2619 pg_dump: The command was: SELECT tableoid, oid, proname, prolang, pronargs, proargtypes, prorettype, proacl, pronamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = proowner) AS rolname FROM pg_proc p WHERE NOT proisagg AND (pronamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'))
A VACUUM ANALYSE of the table was attempted resulting in the following:
PANIC: corrupted item pointer: offset = 8112, size = 1056 server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed.
I also attempted to
$ reindexdb dbname but this also failed when reaching the table with the corrupt columns:
reindexdb: reindexing of database "dbname" failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
I'm quite at a loss right now, I've been searching and reviewing the documentation because I honestly believe those columns are recoverable, or at least some of the data in them is.
It only affects 24 rows in a single table, with three columns, but due to the small scale the website currently operates on, this was a really nice increase in users which I'd prefer to recover if I could.
If all else fails, the email data isn't corrupt so we could issue them with password reset emails but we'd prefer not to do this if possible.
UPDATE I have managed to locate the missing data physically on the system in the xlog files. My plan of action is recovering from the log file via a script matching the missing data with the known usernames, restoring the database from the backup from a few days ago, and building an insert command to insert the repaired records.
This seems a much better option than trying to repair the database or recover via psql.