查找和修复不一致的 B 树索引

数据库索引中出现不一致的原因有很多，包括软件缺陷、硬件问题或底层行为变化（例如排列顺序更改）。

PostgreSQL 社区构建了识别和解决此类问题的工具，这包括 amcheck 等工具，PostgreSQL 社区建议使用这些工具来识别一致性问题，包括早期版本的 PostgreSQL 14 所表现出的问题。

我们编写了此 playbook，以供遇到此类问题的 Cloud SQL for PostgreSQL 用户参考。我们希望本页面提供的信息也有助于其他 PostgreSQL 用户识别和修复不一致的 B 树索引。我们的目标是不断改进本文档，使其作为更广泛开源社区的资源。如果您有任何反馈，请使用本页面顶部和底部的“发送反馈”按钮。

解决索引不一致的问题涉及以下步骤：

准备工作。

在开始重新编入索引之前，您应该备份数据库、设置正确的权限、验证 psql 客户端版本，并下载 amcheck 扩展程序。
检查是否存在不一致的 B 树索引。

如需确定需要修复不一致问题的索引，您需要确定具有不一致问题的所有 B 树索引，并确定所有唯一键和主键违规行为。
解决索引的不一致问题。

将索引重新编入索引可修复其所有不一致问题。您可能需要调整实例的内存设置以提高性能。
监控重新编入索引操作。

我们建议您监控重新编入索引操作的进度，以确保操作正在进行且不会被阻止。
验证索引一致。

成功将索引重新编入索引后，建议您验证索引不包含任何不一致。

准备工作

备份数据库

为了确保在重新编入索引期间不丢失任何数据，我们建议您备份数据库。如需了解详情，请参阅创建按需备份。

设置 `cloudsqlsuperuser` 权限

为完成本页面中的步骤，您必须拥有 cloudsqlsuperuser 权限。如需了解详情，请参阅 session_replication_role。

确保 `psql` 客户端版本为 9.6 或更高版本

如需完成本页面中的步骤，您必须确保 psql 客户端版本为 9.6 或更高版本。运行 psql --version 命令以验证当前的 psql 客户端版本。

安装 Amcheck 扩展程序

如需检查索引不一致，您必须启用 amcheck 扩展程序。

PostgreSQL 9.6

注意：在安装 amcheck_next 之前，您必须确保已安装 POSTGRES_9_6_24.R20220710.01_12。如需详细了解如何安装新的目标维护版本，请参阅自助维护。

如需为 PostgreSQL 9.6 安装 amcheck，请运行以下语句：

  CREATE EXTENSION amcheck_next;

如果您收到“无法打开扩展程序控制文件...”错误，请确认您运行的是正确的目标维护版本 (POSTGRES_9_6_24.R20220710.01_12)。

PostgreSQL 10 及更高版本

如需为 PostgreSQL 10 及更高版本安装 amcheck，请运行以下语句：

  CREATE EXTENSION amcheck;

检查是否存在不一致的 B 树索引

以下部分介绍了如何通过检查索引不一致以及唯一和主键违规来检查是否存在不一致的 B 树索引。

检查是否存在不一致的现象

运行以下语句以检查每个数据库的所有 B 树索引中的不一致：

代码示例

  DO $$
  DECLARE
    r RECORD;
    version varchar(100);
  BEGIN
    RAISE NOTICE 'Started amcheck on database: %', current_database();
    SHOW server_version into version;
    SELECT split_part(version, '.', 1) into version;
    FOR r IN
      SELECT c.oid, c.oid::regclass relname, i.indisunique
        FROM pg_index i
        JOIN pg_opclass op ON i.indclass[0] = op.oid
        JOIN pg_am am ON op.opcmethod = am.oid
        JOIN pg_class c ON i.indexrelid = c.oid
        JOIN pg_namespace n ON c.relnamespace = n.oid
      WHERE am.amname = 'btree'
        AND c.relpersistence != 't'
        AND c.relkind = 'i'
        AND i.indisready AND i.indisvalid LOOP
      BEGIN
        RAISE NOTICE 'Checking index %:', r.relname;
        IF version = '10' THEN
          PERFORM bt_index_check(index => r.oid);
        ELSE
          PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique);
        END IF;
      EXCEPTION
        WHEN undefined_function THEN
          RAISE EXCEPTION 'Failed to find the amcheck extension';
        WHEN OTHERS THEN
          RAISE LOG 'Failed to check index %: %', r.relname, sqlerrm;
          RAISE WARNING 'Failed to check index %: %', r.relname, sqlerrm;
      END;
    END LOOP;
    RAISE NOTICE 'Finished amcheck on database: %', current_database();
  END $$;

您应该会收到类似如下所示的输出：

输出

  NOTICE:  Checking index t_pkey:
  NOTICE:  Checking index t_i_key:
  WARNING:  Failed to check index t_i_key: item order invariant violated for index "t_i_key"
  NOTICE:  Checking index t_j_key:
  WARNING:  Failed to check index t_j_key: item order invariant violated for index "t_j_key"
  NOTICE:  Checking index ij:
  WARNING:  Failed to check index ij: item order invariant violated for index "ij"

如需详细了解如何查看 PostgreSQL 日志，请参阅查看实例日志。

识别并纠正唯一和主键违规行为

本部分介绍如何检查索引是否存在唯一和主键违规，以及如何修复它们（如果存在违规）。

识别唯一键违规行为

在将索引重新编入索引之前，必须先修复唯一键违规行为。如需检查所有唯一键违规行为，请在每个数据库中运行以下命令：

代码示例

  WITH q AS (
      /* this gets info for all UNIQUE indexes */
      SELECT indexrelid::regclass as idxname,
            indrelid::regclass as tblname,
            indcollation,
            pg_get_indexdef(indexrelid),
            format('(%s)',(select string_agg(quote_ident(attname), ', ')
                from pg_attribute a
                join unnest(indkey) ia(nr) on ia.nr = a.attnum
              where attrelid = indrelid)) as idxfields,
            COALESCE(substring(pg_get_indexdef(indexrelid) FROM '[)] (WHERE .*)$'), '') as whereclause
        FROM pg_index
      WHERE indisunique
      /* next line excludes indexes not affected by collation changes */
        AND trim(replace(indcollation::text, '0', '')) != ''
  )
  SELECT
  /* the format constructs the query to execute for each index */
  format(
  $sql$
  DO $$ BEGIN RAISE NOTICE 'checking index=%3$I    on   table=%1$I      key_columns=%2$I '; END;$$;
  SELECT this,
        prev,
        /* we detect both reversed ordering or just not unique */
        (CASE WHEN this = prev THEN 'DUPLICATE' ELSE 'BACKWARDS' END) as violation_type
    FROM (SELECT %2$s AS this,
                lag(%2$s) OVER (ORDER BY %2$s) AS prev
            FROM %1$s %4$s
          ) s
  WHERE this <= prev and this IS NOT NULL and prev IS NOT NULL; /* change to just '<' if looking for reverse order in index */
  $sql$, tblname, idxfields, idxname, whereclause
  )
    FROM q
  -- LIMIT 20 /* may use limit for testing */
  -- the next line tells psql to executes this query and then execute each returned line separately
  \gexec

此脚本的输出类似于以下内容：

输出

  NOTICE:  checking index=users_email_key on table=users key_columns="(email)"
  NOTICE:  checking index=games_title_key on table=games  key_columns="(title)"
          this        |        prev        | violation_type
  --------------------+--------------------+----------------
  Game #16 $soccer 2  | Game #16 $soccer 2 | DUPLICATE
  Game #18 $soccer 2  | Game #18 $soccer 2 | DUPLICATE
  Game #2 $soccer 2   | Game #2 $soccer 2  | DUPLICATE
  Game #5 $soccer 2   | Game #5 $soccer 2  | DUPLICATE

在此输出中，表标头 NOTICE 显示其下方显示的值的索引、列和表。如果输出包含显示 DUPLICATE 或 BACKWARDS 的行，则表示索引中存在损坏现象，可能需要修复。具有 BACKWARDS 的行表示可能隐藏的重复值。如果您在表中看到任一条目，请参阅纠正重复的键违规行为。

纠正重复的键违规行为

如果您已确定重复的唯一索引，或者由于重复键违规错误导致重新编入索引操作失败，请完成以下步骤以查找并移除重复的键。

从 NOTICE 表标头中提取 key_columns，如前面的示例输出所示。在以下示例中，键列为 email。
代码示例
```
  NOTICE:  checking index=users_email_key on table=users key_columns="(email)"
```
在第 3 步的查询的 KEY_COLUMNS 中使用这些值。
查找表的架构。使用 psql 连接到您的数据库并运行以下命令：
代码示例
```
\dt TABLE_NAME
```
schema 列中的值是您在第 3 步的查询中用于 SCHEMA_NAME 的值。

例如，对于以下查询：
```
 \dt games
 
```
输出类似于以下内容：
```
 List of relations
 Schema  | Name  | Type  | Owner
 --------+-------+-------+----------
 public  | games | table | postgres
 (1 row)
 
```
运行以下语句以强制执行全表扫描并获取重复键。
代码示例
```
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;

SELECT KEY_COLUMNS, count(*)
  FROM SCHEMA_NAME.TABLE_NAME
GROUP BY KEY_COLUMNS
HAVING count(*) > 1;
```
注意：如果您的数据库较大，则此语句可能需要很长时间才能完成。

在上述语句中，KEY_COLUMNS 是您正在检查的表中的唯一索引或主键所涵盖的一个或多个列。这些列是在您检查唯一键违规行为时识别的。该语句会返回重复键以及每个键的重复计数。

例如，对于以下查询：
```
  SELECT name,count(*)
    FROM public.TEST_NAMES
  GROUP BY name
  HAVING count(*) > 1;
  
```
输出类似于以下内容：
```
  name                | count
  --------------------+-------
  Johnny              |     2
  Peter               |     2
  (2 rows)
  
```
在这种情况下，请继续下一步以移除重复的键。

如果 KEY_COLUMNS 中的任何列是 null，则可以忽略它们，因为唯一限制条件不适用于 NULL 列。

如果未发现重复的键，则可以前往修复不一致的索引。

可选，但建议执行：为包含重复键的记录创建备份。运行以下语句以创建备份记录：

代码示例

  CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
  AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
  WHERE (KEY_COLUMNS)
  IN ((KEY_VALUES));

在此语句中，KEY_VALUES 是从上一步的结果中复制的值列表。例如：

代码示例

  CREATE TABLE public.TEST_NAMES_bak
  AS SELECT * FROM public.TEST_NAMES
  WHERE (name) IN (('Johnny'),('Peter'))

对于大量行，使用第 2 步中的 SELECT 语句（不包含 count 参数）替换 IN 语句中的 (KEY_VALUES) 参数会更为简单。例如：

代码示例

  CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
  AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
  WHERE (KEY_COLUMNS)
  IN ( SELECT (KEY_COLUMNS)
  FROM SCHEMA_NAME.TABLE_NAME
  GROUP BY (KEY_COLUMNS)
  HAVING count(*) > 1);

为用户添加复制角色以停用触发器：

代码示例

  ALTER USER CURRENT_USER with REPLICATION;
  SET session_replication_role = replica;

运行以下语句以删除重复的键：

代码示例

  BEGIN;

  DELETE FROM  SCHEMA_NAME.TABLE_NAME a
      USING  (
              SELECT   min(ctid) AS ctid,
                      KEY_COLUMNS
              FROM     SCHEMA_NAME.TABLE_NAME
              GROUP BY KEY_COLUMNS
                      HAVING count(*) > 1 ) b
      WHERE a.KEY_COLUMNS = b.KEY_COLUMNS
      AND   a.ctid <> b.ctid;

例如，对于多列 KEY_COLUMNS：

代码示例

  DELETE FROM public.test_random a
      USING (
             SELECT min(ctid) AS ctid,
             day, rnum
      FROM public.test_random
      GROUP BY day, rnum
             HAVING count(*) > 1 ) b
      WHERE a.day=b.day and a.rnum = b.rnum
      AND a.ctid <> b.ctid;

其中 day 和 rnum 是 KEY_COLUMNS。

对于每一组重复行，运行此语句会保留一行并删除其他行。如果要控制删除哪个行版本，请在 delete 语句中运行以下过滤条件：

代码示例

  DELETE FROM  SCHEMA_NAME.TABLE_NAME
  WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);

完成以下步骤，检查 DELETE 命令是否返回预期的行数，并且没有任何错误：
1. 运行以下语句以确定表中已更改的行：
  代码示例
  SELECT schemaname, relname, n_tup_del, n_tup_upd FROM pg_stat_xact_all_tables WHERE n_tup_del+n_tup_upd > 0;
2. 如果所有行都正确，请提交 DELETE 事务：
  代码示例
  END;
3. 如果存在错误，请回滚更改以修复错误：
  代码示例
  ROLLBACK;
删除重复的键后，您可以将索引重新编入索引。

修复不一致的索引

以下部分介绍了如何修复实例中发现的索引不一致问题。

根据数据库的配置方式，您可能需要对前面步骤中确定的每个索引执行以下操作：

准备将索引重新编入索引。
将索引重新编入索引。
如果重新编入索引操作由于外键违规而失败，则您必须查找并纠正这些违规行为。
再次运行重新编入索引操作。

准备将索引重新编入索引

查找索引大小

与较小的数据库相比，将较大的数据库编入索引需要更多时间。要提高较大数据库的索引和重新编入索引操作的速度，您可以为这些操作分配更多内存和 CPU。这是规划重新编入索引操作的一个重要步骤。了解索引大小后，您可以设置重新编入索引操作使用的内存大小并设置并行工作器的数量。

运行以下语句，查找要修复的索引的大小（以千字节为单位）：

代码示例

  SELECT i.relname                                      AS index_name,
        pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size
  FROM   pg_index x
        JOIN pg_class i
          ON i.oid = x.indexrelid
  WHERE  i.relname = 'INDEX_NAME';

此语句的输出类似以下内容：

输出

  index_name  | index_size
  ------------+------------
  my_index    | 16 kB
  (1 row)

设置用于重新编入索引的内存大小

根据上一部分确定的索引大小，请务必为 maintenance_work_mem 设置适当的值。此参数指定用于重新编入索引操作的内存量。例如，如果索引大小超过 15 GB，我们建议您调整维护内存。如需了解详情，请参阅设置数据库标志。

与较小的数据库相比，将较大的数据库编入索引需要更多时间。为提高索引和重新编入索引操作的速度，对于在此重新编入索引操作期间具有 4 GB 或更多内存的实例，我们建议将 maintenance_work_mem 设置为至少 2% 的实例内存。

设置并行工作器的数量

您可以使用 PostgreSQL 11 或更高版本在数据库中设置 max_parallel_maintenance_workers 参数，以增加用于重新编入索引的并行工作器的数量。此参数的默认值为 2，但可以设置为更大的值，以增加重新编入索引的工作器的数量。对于具有 8 个或更多 vCPU 核心的实例，我们建议将 max_parallel_maintenance_workers 标志值设置为 4。

如需了解详情，请参阅设置数据库标志。

将索引重新编入索引

您可以使用 pg_repack 实用程序将索引重新编入索引，而不会阻止生产工作负载。此实用程序可以自动执行并简化并发重新编入索引过程，让您可以在不停机的情况下重新编入索引，尤其是对于没有 REINDEX CONCURRENTLY 操作的 PostgreSQL 版本 11 及更早版本。对于此过程，请使用 pg_repack 1.4.7 版。

完成以下步骤，以使用 pg_repack 将索引重新编入索引：

从 pg_repack 页面下载、编译和安装 pg_repack 实用程序。

Debian GNU/Linux 11

为方便起见，我们建议 Debian Linux 用户下载并安装适用于 Linux x86_64 平台的预构建可执行二进制文件。

此二进制文件的 sha256 校验和哈希如下：

ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f

如需验证您的 Linux 版本是否为 Debian GNU/Linux 11，请运行 hostnamectl 命令。

自编译

注意：对于此过程，您应在 Linux 或 Unix 上使用有效的 GNU 工具链在终端中运行命令。

从 pg_repack 页面下载、编译并安装 pg_repack 实用程序。
创建 pg_repack 扩展程序：
代码示例
```
  CREATE EXTENSION pg_repack;
  
```
运行以下命令以并发地将索引重新编入索引：
代码示例
```
  pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600
  
```
注意：pg_repack 不允许您将物化视图上的索引重新编入索引。

此命令的输出类似如下内容：
输出
```
  INFO: repacking index "public.t_i_key"
  
```
如果运行 pg_repack 时发生任何错误，您可以更正错误，然后重试。修复完所有唯一键索引和主键索引后，您应检查是否存在外键违规行为，并纠正发现的任何违规行为。

查找并纠正外键违规行为

如需了解如何查找并纠正外键违规行为，请参阅查找并纠正外键违规行为。

监控重新编入索引操作

有时，重新编入索引操作可能会被其他会话阻止。我们建议您每 4 小时检查一次此文件。如果重新编入索引操作被阻止，您可以取消阻止会话，以便重新编入索引操作可以完成。

完成以下步骤以识别阻止和等待会话，然后在 INDEX 操作中取消这些会话：

如需识别阻止会话，请运行以下查询：

代码示例

  SELECT pid,
        usename,
        pg_blocking_pids(pid) AS blocked_by,
        query                 AS blocked_query
  FROM   pg_stat_activity
  WHERE  cardinality(pg_blocking_pids(pid)) > 0;

如需取消会话，请使用上一查询中阻止会话的 PID 运行以下查询：
代码示例
```
  SELECT pg_cancel_backend(PID);
  
```

验证索引是否一致

您必须继续检查每个不一致的索引的不一致问题。修复实例的所有不一致索引和键违规行为后，您可以按照之前部分中的步骤检查是否不存在任何问题：

检查索引的不一致问题
检查唯一键违规

查找和修复不一致的 B 树索引

准备工作

备份数据库

设置 cloudsqlsuperuser 权限

确保 psql 客户端版本为 9.6 或更高版本

安装 Amcheck 扩展程序

PostgreSQL 9.6

PostgreSQL 10 及更高版本

检查是否存在不一致的 B 树索引

检查是否存在不一致的现象

代码示例

输出

识别并纠正唯一和主键违规行为

识别唯一键违规行为

代码示例

输出

纠正重复的键违规行为

代码示例

代码示例

代码示例

代码示例

代码示例

代码示例

代码示例

代码示例

代码示例

代码示例

代码示例

代码示例

代码示例

修复不一致的索引

准备将索引重新编入索引

查找索引大小

代码示例

输出

设置用于重新编入索引的内存大小

设置并行工作器的数量

将索引重新编入索引

Debian GNU/Linux 11

自编译

代码示例

代码示例

输出

查找并纠正外键违规行为

监控重新编入索引操作

代码示例

代码示例

验证索引是否一致

设置 `cloudsqlsuperuser` 权限

确保 `psql` 客户端版本为 9.6 或更高版本