Due to a accidental config file check-in, a unit test wiped out our entire production database (did a drop, then re-created the tables). Trying to restore from backup revealed the recent backups were corrupted and the only valid backup we have is just over 1 year old.

What are my options for recovering the data (if any exist)? Autovacuum may have been turned on. I saw a tool pg_dirtyread, but it appears to require Postgres 9.1 or higher, and from what I can tell does not help with drop/create table scenarios.

  • 3
    Before/instead of doing anything on that machine, consult some professionals of saving PostgreSQL databases. – dezso Jan 27 '15 at 9:29
  • 2
    Best of luck... – Philᵀᴹ Jan 27 '15 at 10:00

Your only chance would be to restore that one year old backup and recover that using all the wall files that are archived since then. For that, you need to run archived log mode have all those files. With a little luck they are in filesystem backups. It would need some time to process.

Not the answer you're looking for? Browse other questions tagged or ask your own question.