Postgres Ddl changes module

The module can be installed on a postgres database, after installation it records the changes to the table metadata (ddl) after the ddl has been released (CREATE TABLE, CREATE TABLE AS, ALTER TABLE, DROP TABLE).

Key features:

• Versioning schemas

• Registration of columns for schemas (name, position, type, max lenght, precision, scale)

• Capturing the timing of schema changes (when, from which, to which)

• Active schema indication

• Using table-level and column-level hashes

Important

The module does not retroactively record changes to the schema. The initial_load script can be used to load the current schema versions.

Components

Name	Type	Description
ddl_changes	schema create script	Create ddl_changes schema.
ddl_changes.ddl_changes_meta	table	Stores the metadata of the table versions.
ddl_changes.ddl_changes_columns	table	Store the table columns.
ddl_changes.ddl_changes_version_info	table	Store the ddl change history.
initial_load	onetime script	Make a snapshot from the ddl-s.
ddl_changes.f_log_ddl()	function	The function for recording ddl changes.
et_log_ddl_info	evenet trigger	Call the f_log_ddl() function after a CREATE or ALTER TABLE.
et_log_ddl_drop_info	evenet trigger	Call the f_log_ddl() function after a DROP TABLE.

Installation guide

Important

All scripts are Re-Runable

1. Run the schema create script

   1. code/schemas/ddl_changes.sql

2. Run the table create scripts

   1. code/tables/ddl_changes_meta.sql

   2. code/tables/ddl_changes_columns.sql

   3. code/tables/ddl_changes_version_info.sql

3. Run initial_load script

   1. code/onetime_scripts/initial_load.sql

4. Run the function create scripts

   1. code/functions/f_log_ddl.sql

5. Run the event triggger create scripts

   1. code/event_triggers/et_log_ddl_info.sql

   2. code/event_triggers/et_log_ddl_drop_info.sql