Repmgr 清理触发器

管理片段

repmgr 清理触发器

适用于 PostgreSQL

9.0+

编写语言

PL/pgSQL

依赖于

repmgr

repmgr 的 repmgrd 守护进程可以选择将复制延迟历史记录存储在 repl_monitor 表中；该表可能增长很快，需要定期修剪。这可以通过执行 'repmgr cluster cleanup' 的 cronjob 来实现；或者可以使用以下触发器（仅在 repmgr 1.2.0 上测试过）

清理触发器

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;