postgresql 监控表的改动 添加log

需求是这样的, 尝试使用odoo开源系统, 但是需要对接自己的模块.
想偷个懒就直接改odoo的数据库了, 但是虽然能看到调拨记录, 不过却没有刷新记录个数, 导致在自己直接改数据库之后, 在odoo正常操作会导致key_id重复报错
想了两个解决办法, 第一是直接找odoo的源码, 找到插入时检查最新key_id的方法, 直接改了
不过看源码有点头疼
第二个就是希望直接监控数据库的改动, 把所有操作log出来, 然后在直接改数据库的补上漏掉的部分
搜了一下, 可以通过给每个表加触发器的方法实现

CREATE SCHEMA logging;
CREATE TABLE logging.t_history (
        id             serial,
        tstamp         timestamp DEFAULT now(),
        schemaname     text,
        tabname        text,
        operation      text,
        who            text DEFAULT current_user,
        new_val        json,
        old_val        json
);
--------------------------------------------------------
CREATE FUNCTION change_trigger() RETURNS trigger AS $$
       BEGIN
         IF TG_OP = 'INSERT'
         THEN INSERT INTO logging.t_history (
                tabname, schemaname, operation, new_val
              ) VALUES (
                TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW)
              );
           RETURN NEW;
         ELSIF  TG_OP = 'UPDATE'
         THEN
           INSERT INTO logging.t_history (
             tabname, schemaname, operation, new_val, old_val
           )
           VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), row_to_json(OLD));
           RETURN NEW;
         ELSIF TG_OP = 'DELETE'
         THEN
           INSERT INTO logging.t_history
             (tabname, schemaname, operation, old_val)
             VALUES (
               TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD)
             );
             RETURN OLD;
         END IF;
       END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
-----------------------------------------------------
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON 你的表名
        FOR EACH ROW EXECUTE PROCEDURE change_trigger();

要给每个表都添加trigger, 可以先select到所有表名, 然后批量添加trigger

select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c 
where  relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname

最后可以看到效果:
参考链接:https://stackoverflow.com/questions/13785855/how-to-log-data-change-in-postgresql