Contents

• postgresql-data-historization
  • How to historize a table
    • Initialize the historization
    • Start the historization
    • Stop the historization
  • Creating the partitions
  • Dropping the partitions
  • Create partition with pg_cron

postgresql-data-historization

PLPGSQL Script to historize data in partitionned table

How to historize a 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.

Initialize the historization

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');

Start the historization

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');

Stop the historization

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

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

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

Creating the partitions

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

Dropping the partitions

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

Create partition with pg_cron

If you want to automatically create partition with (pg_cron)[https://github.com/citusdata/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');