emaj

emaj : Enables fine-grained write logging and time travel on subsets of the database.

Overview

Packages

Source

pig build pkg emaj;		# build deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install emaj;		# install via package name, for the active PG version

pig install emaj -v 18;   # install for PG 18
pig install emaj -v 17;   # install for PG 17
pig install emaj -v 16;   # install for PG 16
pig install emaj -v 15;   # install for PG 15
pig install emaj -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION emaj CASCADE; -- requires dblink, btree_gist

Usage

E-Maj: logs and rollbacks table content changes

Documentation | Emaj_web GUI

E-Maj logs table changes (Inserts, Updates, Deletes, Truncates) performed on one or several sets of tables, and can efficiently cancel these changes if needed, resetting a tables set to a predefined stable state.

In development environments, it helps testing applications by providing an easy way to rollback all updates generated by program execution, and replay processing as many times as needed.

In production environments, it provides:

• History of changes performed on tables for examination
• Inter-batch savepoints on groups of tables
• Easy “restore” of table groups to a stable state without stopping the cluster
• Multiple savepoints during batch windows, each usable as a restore point

Concepts

Tables Group

A tables group is a set of application tables that live at the same rhythm — their content can be restored as a whole if needed. A group can include tables and sequences across different schemas. Each group operates in one of two states: LOGGING or IDLE, and can be designated as:

• ROLLBACKABLE (standard) — supports both logging and rollback
• AUDIT_ONLY — allows change recording without rollback capability, even for tables without primary keys or UNLOGGED tables

Mark

A mark represents a snapshot moment in a tables group’s lifecycle, capturing a stable state across all group members. Marks have unique names within a group.

Rollback

Rollback operations restore tables and sequences to their state when a specific mark was established:

• Unlogged rollback — cancelled updates leave no trace
• Logged rollback — cancellations are recorded, allowing subsequent reversal

Note: E-Maj rollback differs fundamentally from PostgreSQL’s native transaction rollback.

Main Functions

Start a Tables Group

SELECT emaj.emaj_start_group('my_group', 'mark_1');

Activates update recording. The group must be in IDLE state. Automatically creates an initial mark.

Set an Intermediate Mark

SELECT emaj.emaj_set_mark_group('my_group', 'mark_2');

Records a point-in-time snapshot of the application state. The group must be in LOGGING state.

Rollback a Tables Group

Unlogged rollback (restores tables, no trace of cancellation):

SELECT * FROM emaj.emaj_rollback_group('my_group', 'mark_1');

Logged rollback (permits reverting the rollback itself):

SELECT * FROM emaj.emaj_logged_rollback_group('my_group', 'mark_1');

Both support the '_EMAJ_LAST_MARK_' keyword for targeting the most recent mark.

Stop a Tables Group

SELECT emaj.emaj_stop_group('my_group');

Deactivates logging, changing the group state from LOGGING to IDLE.

Multi-Group Operations

Functions support batch operations on multiple groups simultaneously:

SELECT emaj.emaj_start_groups('{"group1","group2"}', 'multi_mark');
SELECT emaj.emaj_set_mark_groups('{"group1","group2"}', 'common_mark');
SELECT * FROM emaj.emaj_rollback_groups('{"group1","group2"}', 'common_mark');
SELECT emaj.emaj_stop_groups('{"group1","group2"}');

Examining Changes

E-Maj provides functions to count and examine data content changes between marks, and to generate SQL scripts that replay logged changes. This is useful for auditing and debugging.

Emaj_web

Emaj_web is a PHP-based web GUI tool for user-friendly E-Maj administration. It is available on GitHub and described in the documentation.