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 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?