selective undoing of commited transactions in PostgreSQL

Question

I have inherited a small (<1M rows) PostgreSQL DB which contains complex business rules split across several tables. Updates are very light, reads not so heavy, but the data is extremely valuable.

These rules are updated by a variety of complex, legacy, overlapping systems, all of which are non-trivial to modify. There are even some updates by humans using SQL templates.

And we really have had "panic why did you delete that customer's route? how will we undo this?" moments.

So is there an easy way to version these tables? To be able to manually review and decide to undo a particular transaction, and review which subsequent transactions rely upon it and so on?

I believe MSSQL and Oracle has versioning built-in. Does PostgreSQL support any similar 'change data capture' system?

I hesitate to roll my own trigger-based scripts. I can see that I can put the txid_current() into a 'shadow' table, but not how to record if a transaction commits or rolls back, for example. TableAudit/TableLog, for example, seems to be on the table-only level. If I had to roll my own, how would I do that and what aspects would I need to remember?

Are there tools or scripts for doing multi-table transaction tracking in PostgreSQL, and tools for recovery and undoing them?

Answer 1

You have a few options here. If it is just committed transactions you need to worry about I would use a trigger-based solution. There are already extensions to help with this. See, for example, e-maj and there is good documentation in how to roll your own, and 2ndQuadrant has released a version of an audit trigger on GitHub.

One of the key issues you may have is in determining how to handle rolling back of selective updates. Do you need to restore a table to a specific point in time? Or just a row? With any solution here you are likely to have to roll your own solution to this part. Tablelog does indeed appear to be dead, unfortunately.