重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: PostgreSQL 逻辑复制避坑：无主键表必看的 REPLICA IDENTITY FULL 配置 [打印本页]

作者: ZhangQi 时间: 2025-12-26 17:35
标题: PostgreSQL 逻辑复制避坑：无主键表必看的 REPLICA IDENTITY FULL 配置
一、问题根源：逻辑复制需要 “行标识”

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;

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)