PLPGSQL Script to historize data in partitionned table

Data historization process needs 2 steps, a first one to initialize, this step will create the necessary objects. The second step launch the historization by setting up the triggers.

The following function will set up all necessary object to historize data on a table, no data will be stored after this step

SELECT historize_table_init('public','alpha');

The function will set up trigger on the source table, once the start step is done data will be collected in the table suffix with _log

SELECT historize_table_start('public','alpha');

The function will remove trigger dans function and stop to store changes in log table

SELECT historize_table_stop('public','alpha');

The function will remove the cron entries and columns created on source tablr

SELECT historize_table_reset('public','alpha');

The function will remove the table log, after this step there is no trace of the historization

SELECT historize_table_clean('public','alpha');

SELECT historize_create_partition('public', 'alpha_log', 0);

SELECT historize_drop_partition('public', 'alpha_log', 0);

The data are stored in a partitioned table to ease the removal of old data, be sure to create enough partition.

If you want to automatically create partition with pg_cron you can add the following commands

SELECT cron.schedule_in_database(
  'create-part_1', '00 08 * * *',
  $$SELECT historize_create_partition('my_table', generate_series(1, 4) )$$,
  'my_database');

SELECT cron.schedule_in_database(
  'create-part_1', '00 08 * * *',
  $$SELECT historize_drop_partition('my_table', generate_series(-8, -4) )$$,
  'my_database');

In case of the extension pg_cron is installed in another database you can automaticcaly create the entries through foreign data wrapper.

Be aware of adding the right search_path option if the pg_cron extension is not set in public schema. By default pg_cron is installed in the schema named cron

CREATE EXTENSION dblink;
CREATE EXTENSION postgres_fdw;

CREATE SERVER historize_foreign_cron
        FOREIGN DATA WRAPPER dblink_fdw
        OPTIONS (host 'localhost', port '5432', dbname 'postgres', options '-csearch_path=cron');

CREATE USER MAPPING FOR local_user
        SERVER historize_foreign_cron
        OPTIONS (user 'foreign_user', password 'password');

In this example we will upgrade the extension from version 1.0.0 to 1.0.1

Run the upgrade script in your instance and do an ALTER EXTENSION

user@database=> \i pgtle.data_historization-1.0.0--1.0.1.sql
 install_update_path
---------------------
 t
(1 row)

user@database=> ALTER EXTENSION data_historization UPDATE TO "1.0.1";
ALTER EXTENSION