Repmgr cleanup trigger

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(+test)
m (clarify)
Line 25: Line 25:
 
CREATE TRIGGER repl_cleanup_trigger AFTER INSERT ON repl_monitor FOR EACH STATEMENT EXECUTE PROCEDURE repl_cleanup_proc();
 
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)
+
-- Test the trigger (does not insert any data, but triggers deletion if necessary)
 
INSERT INTO repl_monitor (primary_node) SELECT null WHERE false;
 
INSERT INTO repl_monitor (primary_node) SELECT null WHERE false;
 
</source>
 
</source>
  
 
[[Category:Replication]]
 
[[Category:Replication]]

Revision as of 13:25, 9 October 2012

Administrative Snippets

repmgr cleanup trigger

Works with PostgreSQL

9.0+

Written in

PL/pgSQL

Depends on

repmgr

The repmgr replication system requires you to run daemons on each slave to populate the repl_monitor table with mostly useless state data. In addition, it requires you to run a separate "cluster cleanup" cron job on the master to delete this useless data (hey, the same daemon couldn't possibly do that, could it?)

To simplify deployment/maintenance, that cron job can be replaced with the following trigger. (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 $$
DECLARE
  cleanup_age interval = '1 day'; -- how many records to keep
  cleanup_batch interval = '1 hour'; -- how many records to delete in one shot
BEGIN
  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);
  END IF;
  RETURN new;
END;
$$;
 
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;
Personal tools