Re: Creating complex track changes database - challenge! - Mailing list pgsql-general

Łukasz Jarych schrieb am 26.02.2018 um 11:44:
> i would like to ask you for help with track changes to my database. 
> I am new to PosgtreeSQL but i have to learn it quickly because of my boss. 
> 
> I have to:
> 
> 1. Keep all changes within table including:
> -adding rows
> -deleting
> -editing
>
> 2. Save table with specific state and recover specific state (so go back to previous table versions) including
comparingtables.
 

There are several generic auditing triggers that can do that:

* http://cjauvin.blogspot.de/2013/05/impossibly-lean-audit-system-for.html
* https://eager.io/blog/audit-postgres/
* http://okbob.blogspot.de/2015/01/most-simply-implementation-of-history.html
* http://8kb.co.uk/blog/2015/01/19/copying-pavel-stehules-simple-history-table-but-with-the-jsonb-type/
* https://www.garysieling.com/blog/auditing-data-changes-postgres
* https://github.com/wingspan/wingspan-auditing
* https://wiki.postgresql.org/wiki/Audit_trigger_91plus

> 3. Track all DLL and DML changes with possibility to ho back to previous version. 

That will be very tricky, especially the "go back to previous version" part. 

But in general DDL changes can be tracked using event triggers. 

Thomas