pgMemento provides an audit trail for your data inside a PostgreSQL database using triggers and server-side functions written in PL/pgSQL. It also tracks DDL changes to enable schema versioning and offers powerful algorithms to restore or repair past revisions.
Only deltas of changes are logged and they are stored in one data log table as JSONB. Transaction and table event metadata is recorded in separate log tables making it easier to browse through past write operations.
To use pgMemento as an extension download the pgmemento-.zip archive either from the release on GitHub or PGXN, change to extracted folder and run the following commands from within a shell environment:
make sudo make install
Then, connect to your database where you want to use auditing and run:
CREATE EXTENSION pgmemento;
Alternatively, you can also add pgMemento to your database the classic way by running the INSTALL_PGMEMENTO.sql script, which simply executes all SQL files in the right order and updates the search path.
psql -h localhost -p 5432 -U my_user -d my_database -f INSTALL_PGMEMENTO.sql
All of pgMemento's log tables and functions are created in a separate
database schema called
pgmemento. Auditing can be started per schema
init command, e.g. for the
public schema it could be:
A new column
pgmemento_audit_id is added to every table in the given
schema to trace different row versions in the central data log - the
pgmemento.row_log table. Each write transaction is also logged in the
pgmemento.transaction_log which can consist of multiple table events
stored in the
pgmemento.table_event_log table. The
in the latter links to the entries in the data log.
Schema versioning takes place in the
pgmemento.audit_column_log. Transaction ranges show the life time of
an audited table and its columns. Changing the table schema, e.g.
altering data types or dropping entire columns will produce data logs as
well. Auditing can also be started automatically for newly created
tables in schemas where pgMemento has been initialized. This is tracked
(and configurable) in the
- PostgreSQL 9.5+
- PL/pgSQL language
Documentation can be found in the wiki section of this repository.
The scripts for pgMemento are open source under GNU Lesser General Public License Version 3.0. See the file LICENSE for more details.
pgMemento IS PROVIDED "AS IS" AND "WITH ALL FAULTS." I MAKE NO REPRESENTATIONS OR WARRANTIES OF ANY KIND CONCERNING THE QUALITY, SAFETY OR SUITABILITY OF THE SKRIPTS, EITHER EXPRESSED OR IMPLIED, INCLUDING WITHOUT LIMITATION ANY IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT.
IN NO EVENT WILL I BE LIABLE FOR ANY INDIRECT, PUNITIVE, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES HOWEVER THEY MAY ARISE AND EVEN IF I HAVE BEEN PREVIOUSLY ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. STILL, I WOULD FEEL SORRY.