1

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.

  • 1
    What is your exact Postgres version? – a_horse_with_no_name Jan 28 '16 at 20:44
  • The version is 9.1.18 – Arcana Jan 28 '16 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.