I lost a lot of data in my postgresql database and do not have a dump file to restore backup from, but I heard that in /var/lib/postgresql/9.4/main there are pg_xlog and pg_clog and that those directories could be used to restore previous points in the db. Could someone clarify this and how to do it? The importance is major.

FYI everything is stopped. What happened is that there is only a very old dump from February since then a lot has changed in the database and in 3rd of July at 2PM executing a python script it changed a lot of the data in the database(data changed is the clients assigned to a specific shop). There is no data loss but it would be needed to revert those transactions. In the pg_xlog folder the transaction logs that have not been archived are from 2hours before that moment it all changed.

I desperately need to revert those changes. Is there any possible way to revert changes?

I have a system backup of that virtual machine that is moments few hours after and can try solutions many times.

  • 6
    I'd strongly suggest stopping all work on it and calling in professional help. If the data is really important, don't try to fix it yourself. Read this first: wiki.postgresql.org/wiki/Corruption – dezso Jul 14 '15 at 13:42
  • 3
    I really, really hope you stopped the database immediately. You might have a hope of recovery by twiddling bits in pg_clog to mark the transactions that deleted the unwanted data as aborted instead of committed, so long as the old versionsof the tuples weren't VACUUMed away. It's going to require a source-code-level understanding of PostgreSQL, quite a bit of analysis, and possibly a custom-written utility. I suggest starting with postgresql.org/support/professional_support (Disclosure: I work for one of the listed companies). – Craig Ringer Jul 15 '15 at 4:14
  • Note, seems to be X-posted to stackoverflow.com/q/31406852/398670 – Craig Ringer Jul 15 '15 at 4:16

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.