Postgres 故障排查：修复重复主键行-未完纪

最近有人在 Postgres 邮件列表询问，如何删除表中不需要的重复行。这里的“不需要”，是指被指定为主键的列中，同一个值出现了多次。自从 glibc 改变排序方式后，此类问题有所增加：升级操作系统并改变底层 glibc 库，可能导致索引失效。

唯一索引损坏的主要后果之一，是允许插入本应被主键约束阻止的行。假设某表以 id 为主键，你可能看到：

-- Can you spot the problem?
SELECT id FROM mytable ORDER BY id LIMIT 5;

 id
----
  1
  2
  3
  3
  4
(5 rows)

在尚不了解更多信息时，第一步应该做什么？如果回答是备份，那就对了！怀疑数据库出现问题，或者准备修复这类问题之前，都应创建最新备份。

能否直接重建索引？不能。只要仍有重复行，唯一索引就无法创建或重建，Postgres 会拒绝违反唯一性约束的操作。因此，必须先从表中删除重复行。

下面是一套谨慎的处理流程。请按具体情况调整，慢慢执行，并理解每一步。生产环境尤其如此；这类问题几乎总是发生在生产环境。

1. 调试辅助设置

首先做的事情可能有些奇怪：

-- Encourage not using indexes:
set enable_indexscan = 0;
set enable_bitmapscan = 0;
set enable_indexonlyscan = 0;

由于坏索引是这类重复行进入数据库的主要原因，不能信任索引。这些底层调试选项告诉规划器，优先采用其他取数方式。在本例中，就是直接访问表，而不是通过索引查找。

2. 快速确认目标

-- Sanity check. This should return a number greater than 1. If not, stop.
set search_path = public;
select count(*) from mytable where id = 3;

开始前，先确保找对了表。设置 search_path 和查询目标表都是安全措施。id 是主键，正常情况下，每个值的计数应该只有 0 或 1。

我们已知 id = 3 有多条记录，因此这项检查主要用于确认即将操作的是正确对象，预期结果应大于 1。如果不是，就停止。

3. 始终创建备份

-- Make a backup:
create table mytable_backup as select * from mytable;

将全部已有行复制到新备份表。这不能替代完整数据库备份；完整备份始终是第零步。表级副本只是另一层保护。

4. 创建测试表

--  Test out the process on a subset of the data:
create table test_mytable as select * from mytable where id < 30;
create table test_mytable_duperows_20250317 (like mytable);

先在测试表上尝试总是明智的。本例创建原表的一个较小子集，其中包含已知有问题的 id = 3。

同时创建空表 test_mytable_duperows_20250317，保存之后移除的重复行。表名末尾的日期，让未来查看者知道创建时间。

5. 在 replica 会话中开始清理

从这里开始进行实际清理。先开启事务，再把 session_replication_role 设为 replica。这是高级且危险的命令，会禁用触发器和规则。平时不建议这样做，这里是为了防止外键阻碍删除错误行。

使用 SET LOCAL 而非普通 SET，确保下次 COMMIT 或 ROLLBACK 后恢复正常设置：

begin;
set local session_replication_role = 'replica';

测试表刚创建，没有触发器，也没有通过外键关联其他表。但为了让测试尽可能接近正式操作，仍然保留这个设置。

6. 清理重复行

begin;

set local session_replication_role = 'replica';

with goodrows as (
  select min(ctid) from TEST_mytable group by id
)
,mydelete as (
  delete from TEST_mytable
  where not exists (select 1 from goodrows where min=ctid)
  returning *
)
insert into TEST_mytable_duperows_20250317 select * from mydelete;

reset session_replication_role;

commit;

流程依次为：开启事务、设置 session_replication_role、执行一条 SQL、重置设置、提交。

这条 SQL 完成了不少工作，逐部分解释如下。

select min(ctid) from TEST_mytable group by id 首先选出每个 id 应保留的行。主键 id 本应唯一，出现多次时需要一个决胜规则。Postgres 每行都有隐藏的 ctid，它标识实际物理行的位置，因此可以区分这些行。

按 id 分组后，为每个唯一 id 选择最小 ctid，就能取得一行。选择 min()、max() 或其他方式并不重要，关键是只选一个。通过 WITH 创建名为 goodrows 的 CTE，保存这些信息，供删除使用。

delete from TEST_mytable
where not exists (select 1 from goodrows where min=ctid)
returning *

接下来，删除不在 goodrows 列表中的行。每个重复行都有不同的 ctid，因此每个 id 只保留一行。RETURNING * 让 DELETE 返回每一条被删除行的完整信息。

insert into test_mytable_duperows_20250317 select * from mydelete;

最后，将删除操作的输出保存到专门的表中。这样虽然删除了重复行，仍保留完整记录，便于调试和取证。

此时重复行应已移出测试表，并保存在 duperows 表中。最好查看这两张表，确认结果符合预期。

7. 在正式表上执行

准备好后，使用相同代码，将测试表替换为真实表：

create table mytable_duperows_20250317 (like mytable);

begin;

set local session_replication_role = 'replica';

with goodrows as (
  select min(ctid) from mytable group by id
)
,mydelete as (
  delete from mytable
  where not exists (select 1 from goodrows where min=ctid)
  returning *
)
insert into mytable_duperows_20250317 select * from mydelete;

reset session_replication_role;

commit;