PostgreSQL 逻辑复制避坑：无主键表必看的 REPLICA IDENTITY FULL 配置

ZhangQi

一、问题根源：逻辑复制需要 “行标识”

PostgreSQL 逻辑复制基于数据变更日志（WAL）同步，复制 UPDATE/DELETE 操作时，必须精准定位 “哪一行被修改”。

-- 有主键的表：默认用主键作为行标识，高效且唯一。
-- 无主键的表：没有默认的唯一标识，PostgreSQL 无法定位目标行，导致复制失败。

二、REPLICA IDENTITY FULL：无主键表的兜底方案

REPLICA IDENTITY是 PostgreSQL 为逻辑复制定义的 “行标识规则”，其中FULL表示用整行所有列的值作为行标识，以此解决无主键表的定位问题。

三、配置步骤

1. 查询没主键的表

SELECT n.nspname AS "Schema", c.relname AS "TableName"
FROM pg_catalog.pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid
  AND n.nspname NOT IN ('information_schema', 'pg_catalog')
  AND c.relkind = 'r'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_index i
    WHERE c.oid = i.indrelid
      AND i.indisprimary = 't'
  );

2. 批量增加脚本

DO $$
DECLARE
    table_record RECORD;
BEGIN
    FOR table_record IN
        SELECT n.nspname AS schema_name, c.relname AS table_name
        FROM pg_catalog.pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        WHERE n.nspname NOT IN ('information_schema', 'pg_catalog')
            AND c.relkind = 'r'
            AND NOT EXISTS (
                SELECT 1
                FROM pg_index i
                WHERE c.oid = i.indrelid AND i.indisprimary = 't'
            )
    LOOP
        EXECUTE 'ALTER TABLE "' || table_record.schema_name || '"."' || table_record.table_name || '" REPLICA IDENTITY FULL';
        RAISE NOTICE '已设置表: %.%', table_record.schema_name, table_record.table_name;
    END LOOP;
END $$;

3. 详细查看无主键表的复制标识状态 (若 ReplicaIdentity 列值为 FULL(全表) 则添加成功)

SELECT
    n.nspname AS "Schema",
    c.relname AS "TableName",
    CASE c.relreplident
        WHEN 'd' THEN 'DEFAULT(主键)'
        WHEN 'n' THEN 'NOTHING(无)'
        WHEN 'f' THEN 'FULL(全表)'
        WHEN 'i' THEN 'INDEX(索引)'
    END AS "ReplicaIdentity",
    -- 是否有主键
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM pg_index i
            WHERE c.oid = i.indrelid AND i.indisprimary = 't'
        ) THEN '有'
        ELSE '无'
    END AS "是否有主键",
    -- 表大小
    pg_size_pretty(pg_total_relation_size(c.oid)) AS "表大小"
FROM pg_catalog.pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname NOT IN ('information_schema', 'pg_catalog')
    AND c.relkind = 'r'
    AND NOT EXISTS (
        SELECT 1
        FROM pg_index i
        WHERE c.oid = i.indrelid AND i.indisprimary = 't'
    )
ORDER BY
    c.relreplident DESC,  -- 先显示已设置FULL的表
    n.nspname,
c.relname;