Old school auditing with the new JSONB stuff: pgMemento


It's actually quite simple: If fields are updated or rows deleted, a trigger is called storing the changes in one audit table as JSONB. These snippets can be queried efficiently using GIN indexing and we can put them back together to create historical replicas of rows / tables / databases.

Metadata about corresponding transactions and events are stored in seperate logging tables, which is useful to revert changes caused by certain transactions. pgMemento is written in PL/pgSQL and therefore pretty easy to use with every postgres instance (v.9.4 … soon 9.5) incl. extensions like PostGIS.

Repo: https://github.com/pgmemento

Slides (External URL)

Session details
Speaker(s): Session Type: Experience level:
Track: Tags:
Schedule info
Session Time Slot(s):
Tuesday, May 3, 2016 - 10:30 to 11:05