Repmgr cleanup trigger

From PostgreSQL wiki
Jump to: navigation, 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;