如何查看Postgres写入/修改的数据？日志未捕获全部查询如何解决

Postgres本身提供多种数据变更记录能力，你遇到的全量语句日志无法捕获的问题，可按以下方向排查解决：

一、现有日志配置排查方案 #

你已将全局log_statement设为all，正常情况下会记录所有执行的SQL语句，出现漏日志的常见原因及修复方式如下：

• 检查数据库角色级的参数覆盖：Postgres支持角色、库级别的参数配置，优先级高于全局postgresql.conf配置。执行以下SQL查询所有角色的自定义配置：

  SELECT rolname, rolconfig FROM pg_roles;
  

  如果Web应用使用的数据库账号的rolconfig里包含log_statement=none或其他非all的配置，执行以下语句修改即可：

  ALTER ROLE 你的Web应用数据库账号 SET log_statement = 'all';
  

  修改后不需要重启实例，Web应用断开重连后即可生效。
• 检查日志轮转规则：你当前配置的log_rotation_size为10KB，日志写入速度快的情况下会频繁生成新的日志文件，容易出现查找的日志不在当前打开的日志文件中的情况。可先调大轮转阈值测试，修改postgresql.conf中参数为log_rotation_size = 102400（即100MB），重启后验证。
• 检查日志信息完整性：你的log_line_prefix为空，建议添加必要的前缀字段方便排查，比如设置为'%t %u %d %h '，可记录语句执行时间、操作用户、所属库、客户端IP，避免日志信息不全无法匹配操作。

二、更适配你的场景的变更追踪方案 #

因为你的实例表数量很少（总共不到15张表），单表数据量也极小，比全量打印SQL更轻量化的变更追踪方案是使用触发器做审计：

1. 先创建统一的审计日志表存储变更记录：

CREATE TABLE public.data_change_log (
    id serial PRIMARY KEY,
    schema_name text NOT NULL,
    table_name text NOT NULL,
    op_type char(1) NOT NULL, -- I=插入,U=更新,D=删除
    op_time timestamptz NOT NULL DEFAULT now(),
    op_user text NOT NULL,
    old_data jsonb,
    new_data jsonb
);

2. 创建通用的变更记录触发器函数：

CREATE OR REPLACE FUNCTION public.record_data_change()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO public.data_change_log(schema_name, table_name, op_type, op_user, new_data)
        VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, 'I', session_user, to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO public.data_change_log(schema_name, table_name, op_type, op_user, old_data, new_data)
        VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, 'U', session_user, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO public.data_change_log(schema_name, table_name, op_type, op_user, old_data)
        VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, 'D', session_user, to_jsonb(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

3. 给每张需要监控的表绑定触发器即可：

-- 替换为你实际的schema和表名
CREATE TRIGGER trigger_your_table_change
AFTER INSERT OR UPDATE OR DELETE ON 你的schema名.你的表名
FOR EACH ROW EXECUTE FUNCTION public.record_data_change();

该方案不需要依赖日志文件，可直接用SQL查询所有变更记录，查询效率远高于翻日志，完全适配你的业务规模。

内容的提问来源于stack exchange，提问作者Viki