ddl_historization

ddl_historization : Historize the ddl changes inside PostgreSQL database

Overview

Packages

Source

pig build pkg ddl_historization;		# build rpm/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 ddl_historization;		# install via package name, for the active PG version

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

Create this extension with:

CREATE EXTENSION ddl_historization CASCADE; -- requires plpgsql

Usage

ddl_historization: Track all DDL changes in a PostgreSQL database

Records all DDL changes (CREATE, ALTER, DROP, etc.) made on a database into a historization table for auditing and tracking purposes.

Setup

CREATE EXTENSION ddl_historization;

The extension installs event triggers that automatically capture DDL statements and store them in the historization table.

Querying DDL History

After installation, all DDL changes are logged automatically. Query the history table to see what changes have been made:

SELECT * FROM ddl_history ORDER BY ddl_date DESC;

Integration with pg_tle

For AWS RDS environments, the extension can be deployed via pg_tle:

-- Build the pg_tle deployment file
-- $ make pgtle
-- Then execute pgtle.ddl_historization-0.3.sql on your instance

Notes

• DDL statements are captured via PostgreSQL event triggers
• Works with CREATE, ALTER, DROP, and other DDL commands
• Used as a dependency by the schedoc extension for schema documentation