table_log
record table modification logs and PITR for table/row
Repository
df7cb/table_log
https://github.com/df7cb/table_log
Source
table_log-0.6.4.tar.gz
table_log-0.6.4.tar.gz
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
table_log | 0.6.4 | ADMIN | PostgreSQL | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 5860 | table_log | No | Yes | No | Yes | No | No | - |
| Related | temporal_tables emaj pg_drop_events pg_auditor pg_upless pg_savior pgaudit pgauditlogtofile |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | MIXED | 0.6.4 | 1817161514 | table_log | - |
| RPM | PIGSTY | 0.6.4 | 1817161514 | table_log_$v | - |
| DEB | PGDG | 0.6.4 | 1817161514 | postgresql-$v-tablelog | - |
Build
You can build the RPM packages for table_log using pig build:
pig build pkg table_log # build RPM packages
Install
You can install table_log 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:
pig install table_log; # Install for current active PG version
pig ext install -y table_log -v 18 # PG 18
pig ext install -y table_log -v 17 # PG 17
pig ext install -y table_log -v 16 # PG 16
pig ext install -y table_log -v 15 # PG 15
pig ext install -y table_log -v 14 # PG 14
dnf install -y table_log_18 # PG 18
dnf install -y table_log_17 # PG 17
dnf install -y table_log_16 # PG 16
dnf install -y table_log_15 # PG 15
dnf install -y table_log_14 # PG 14
apt install -y postgresql-18-tablelog # PG 18
apt install -y postgresql-17-tablelog # PG 17
apt install -y postgresql-16-tablelog # PG 16
apt install -y postgresql-15-tablelog # PG 15
apt install -y postgresql-14-tablelog # PG 14
Create Extension:
CREATE EXTENSION table_log;
Usage
table_log: record table modification logs and PITR for table/row
The table_log extension records INSERT, UPDATE, and DELETE operations on a table into a separate log table, enabling point-in-time recovery at the table or row level.
Initialize Logging
CREATE EXTENSION table_log;
-- Basic setup: creates a log table and trigger for 'my_table'
-- Level 5 = log trigger_id + trigger_user + trigger columns
SELECT table_log_init(5, 'my_table');
-- With explicit log schema
SELECT table_log_init(5, 'my_table', 'log_schema');
-- Full form with all options
SELECT table_log_init(
5, -- level: 3=minimal, 4=+user, 5=+id+user
'public', -- source schema
'my_table', -- source table
'log_schema', -- log table schema
'my_table_log', -- log table name (default: {table}_log)
'SINGLE', -- partition mode: 'SINGLE' or 'PARTITION'
false, -- basic_mode (simpler trigger)
'{INSERT, UPDATE, DELETE}'::text[] -- actions to log
);
Log Table Structure
The log table mirrors the original table columns plus metadata:
| Column | Description |
|---|---|
trigger_mode | Operation type: INSERT, UPDATE, DELETE |
trigger_tuple | Tuple version: ‘old’ or ’new’ |
trigger_changed | Timestamp of the change |
trigger_id | Sequential ID (level 4+) |
trigger_user | User who made the change (level 5) |
Point-in-Time Restore
-- Restore table to a specific point in time
SELECT table_log_restore_table(
'my_table', -- original table name
'my_table_log', -- log table name
'id', -- primary key column
'trigger_changed', -- timestamp column in log
'trigger_tuple', -- tuple type column in log
'2024-01-15 10:30:00' -- restore to this timestamp
);
Trigger Functions
| Function | Description |
|---|---|
table_log() | Full trigger function logging all columns |
table_log_basic() | Basic trigger function with simpler logging |
table_log_restore_table(...) | Restore table state to a given timestamp |
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.