2 ms·
If you have an "audit" table, where you write a copy of the data before updating it in the primary table, that's a decision you can make at any point. Of cours
by pavel_lishin 2y ago
If you have an "audit" table, where you write a copy of the data before updating it in the primary table, that's a decision you can make at any point.
Of course, you don't get that historical data, but you do get it going forward from there.
- tough 2y agosomething like https://www.pgaudit.org/ https://www.pgaudit.org/ ? Basically you keep an history of all changes so you can always roll-back / get that data if needed?
- pavel_lishin 2y agoThe last time we did this, we basically hand-rolled our own, with a database trigger to insert data into a different table whenever an `UPDATE` statement happened. But this seems like it's probably a better solution.
- tough 2y agonever had used pgaudit yet to vouch for it but have it on the backburner/log of things to try for such a use case! I think the real magic is it lleverages the WAL (write ahead logs) from pg engine itself, which you could certainly hook up into too, but im not a db expert here
- tough 2y agoI just found out about bemi dot io, seems like they're targeting this issue
- homebrewer 2y agoSQL 2011 defines temporal tables, which few FOSS databases support. I used it in mariadb: https://mariadb.com/kb/en/temporal-tables/ https://mariadb.com/kb/en/temporal-tables/ and if your schema doesn't change much, it's practically free to implement, much easier and simpler than copypasting audit tables, or relying on codegen to do the same.