pgmemento

Overview

Packages upgrade scripts from 0.7, 0.7.1, 0.7.2, and 0.7.3 to 0.7.4.

Version

Build

You can build the RPM / DEB packages for pgmemento using pig build:

pig build pkg pgmemento         # build RPM / DEB packages

Install

You can install pgmemento directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pgmemento;          # Install for current active PG version

pig ext install -y pgmemento -v 18  # PG 18
pig ext install -y pgmemento -v 17  # PG 17
pig ext install -y pgmemento -v 16  # PG 16
pig ext install -y pgmemento -v 15  # PG 15
pig ext install -y pgmemento -v 14  # PG 14

dnf install -y pgmemento_18       # PG 18
dnf install -y pgmemento_17       # PG 17
dnf install -y pgmemento_16       # PG 16
dnf install -y pgmemento_15       # PG 15
dnf install -y pgmemento_14       # PG 14

apt install -y postgresql-18-pgmemento   # PG 18
apt install -y postgresql-17-pgmemento   # PG 17
apt install -y postgresql-16-pgmemento   # PG 16
apt install -y postgresql-15-pgmemento   # PG 15
apt install -y postgresql-14-pgmemento   # PG 14

Create Extension:

CREATE EXTENSION pgmemento;

Usage

Sources:

• pgMemento v0.7.4 README
• pgMemento v0.7.4 control file
• pgMemento wiki
• Changes from v0.7.3 to v0.7.4

pgmemento is a trigger-based audit trail for PostgreSQL. It records DML changes as JSONB deltas, groups them by transaction and table event, tracks audited schema changes, and provides restore and revert helpers. Use it when row history and transaction context must be queried inside PostgreSQL.

Create and Initialize

CREATE EXTENSION pgmemento;
SELECT pgmemento.init('public');

init instruments eligible tables in the selected schema and adds the pgmemento_audit_id identity column used to track row history. Run it first on a staging copy: auditing changes table definitions and installs event and row triggers.

Use start and stop to control auditing for a schema, and use the documented drop function when intentionally removing pgMemento’s instrumentation. Do not manually delete extension triggers or audit identifiers.

Inspect the Audit Trail

The central data model includes:

• transaction_log: transaction metadata and optional application context.
• table_event_log: table-level events within a transaction.
• row_log: JSONB row deltas linked to a table event.
• audited_schema and audited_table metadata: tracked schemas, tables, columns, and versions.

A typical investigation joins a transaction to its table events and row deltas:

SELECT t.id,
       t.txid_time,
       e.table_operation,
       r.audit_id,
       r.old_data,
       r.new_data
FROM pgmemento.transaction_log AS t
JOIN pgmemento.table_event_log AS e
  ON e.transaction_id = t.id
JOIN pgmemento.row_log AS r
  ON r.event_key = e.event_key
WHERE t.id = 12345;

Inspect the installed views and column names before embedding this query because audit schema details can differ across pgmemento versions.

Restore and Revert

pgmemento provides restore functions that reconstruct table state from the audit trail and revert_transaction or related helpers that apply compensating changes. Treat these as recovery operations:

1. take and verify a backup;
2. identify the exact transaction and dependent changes;
3. preview reconstructed data where possible;
4. run the operation in a controlled transaction;
5. validate constraints, sequences, and application invariants.

Version 0.7.4

Version 0.7.4 changes row serialization to avoid PostgreSQL’s jsonb_build_object argument limit for very wide payloads and adds PostgreSQL 15 support. Upgrade using ALTER EXTENSION pgmemento UPDATE only after testing the version-specific upgrade script.

Operational Boundaries

• Audit triggers add latency and write volume to every tracked change. Monitor row_log growth and index maintenance.
• The audit trail resides in the same database and is not a substitute for backups, WAL archives, or tamper-resistant external audit storage.
• Schema initialization and DDL tracking alter application tables. Coordinate migrations with pgmemento rather than bypassing its event triggers.
• Limit direct writes to the pgmemento schema and protect any transaction metadata that can contain user or application information.