Repmgr cleanup trigger

From PostgreSQL wiki
Jump to navigationJump to search

Administrative Snippets

repmgr cleanup trigger

Works with PostgreSQL


Written in


Depends on


repmgr's repmgrd daemon can optionally store replication lag history in the repl_monitor table; this table can grow quite quickly and requires regular pruning. This can be achieved by a cronjob which executes 'repmgr cluster cleanup'; alternatively following trigger could be used (only tested on repmgr 1.2.0):

Cleanup trigger

SET search_path=repmgr_CLUSTERNAME; -- change CLUSTERNAME to your repmgr cluster name

CREATE OR REPLACE FUNCTION repl_cleanup_proc() RETURNS trigger LANGUAGE plpgsql
SET search_path=repmgr_CLUSTERNAME
AS $$
  cleanup_age interval = '1 day'; -- how many records to keep
  cleanup_batch interval = '1 hour'; -- how many records to delete in one shot
  IF EXISTS(SELECT * FROM repl_monitor WHERE last_monitor_time < (now() - cleanup_age - cleanup_batch)) THEN
    DELETE FROM repl_monitor WHERE last_monitor_time < (now() - cleanup_age);
  RETURN new;

CREATE TRIGGER repl_cleanup_trigger AFTER INSERT ON repl_monitor FOR EACH STATEMENT EXECUTE PROCEDURE repl_cleanup_proc();

-- Test the trigger (does not insert any data, but triggers deletion if necessary)
INSERT INTO repl_monitor (primary_node) SELECT null WHERE false;