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

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

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

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

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

  1. 准备工作

    在开始重新编入索引之前,您应该备份数据库、设置正确的权限、验证 psql 客户端版本,并启用 amcheck 扩展程序。

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

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

  3. 解决索引的不一致问题

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

  4. 监控重新编入索引操作

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

  5. 验证索引一致

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

准备工作

备份 AlloyDB 集群的数据

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

设置 alloydbsuperuser 权限

为完成本页面中的步骤,您必须拥有 alloydbsuperuser 权限。如需了解详情,请参阅 AlloyDB 的预定义 PostgreSQL 角色

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

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

启用 amcheck 扩展程序

如需检查索引不一致,您必须启用 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;
      PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique);
    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"

如需详细了解如何查看日志,请参阅使用日志浏览器查看日志

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

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

识别唯一键违规行为

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

代码示例

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%2$I on table %1$I %4$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 显示其下方显示的值的索引、列和表。如果输出包含显示 DUPLICATEBACKWARDS 的行,则表示索引中存在损坏现象,可能需要修复。具有 BACKWARDS 的行表示可能隐藏的重复值。如果您在表中看到任一条目,请参阅纠正重复的键违规行为

纠正重复的键违规行为

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

  1. NOTICE 表标头中提取 key_columns,如前面的示例输出所示。在以下示例中,键列为 email

    输出

      NOTICE:  checking index=users_email_key on table=users key_columns="(email)"

    在第 3 步的查询的 KEY_COLUMNS 中使用这些值。

  2. 查找表的架构。使用 psql 连接到您的数据库并运行以下命令:

    代码示例

    \dt TABLE_NAME
    schema 列中的值是您在第 3 步的查询中用于 SCHEMA_NAME 的值。

    例如,对于以下查询:

     \dt games
     

    输出类似于以下内容:

     List of relations
     Schema  | Name  | Type  | Owner
     --------+-------+-------+----------
     public  | games | table | postgres
     (1 row)
     

  3. 运行以下语句以强制执行全表扫描并获取重复键。

    代码示例

    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 列。

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

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

    代码示例

    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);
  5. 为用户添加复制角色以停用触发器:

    代码示例

    ALTER USER CURRENT_USER with REPLICATION;
    SET session_replication_role = replica;
  6. 运行以下语句以删除重复的键:

    代码示例

    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;
    其中 dayrnumKEY_COLUMNS

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

    代码示例

    DELETE FROM  SCHEMA_NAME.TABLE_NAME
    WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);
  7. 完成以下步骤,检查 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;
  8. 删除重复的键后,您可以重新编制索引。

修复不一致的索引

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

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

  1. 准备重新编制索引

  2. 重新编制索引

  3. 如果重新编入索引操作由于外键违规而失败,则您必须查找并纠正这些违规行为

  4. 再次运行重新编入索引操作。

准备重新编制索引

查找索引大小

与较小的数据库相比,将较大的数据库编入索引需要更多时间。要提高较大数据库的索引和重新编入索引操作的速度,您可以为这些操作分配更多内存和 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,我们建议您调整维护内存。

以下示例展示了如何设置 maintenance_work_mem

代码示例

SET maintenance_work_mem TO "1GB";

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

设置并行工作器的数量

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

以下示例展示了如何识别为这些参数设置的值:

代码示例

SHOW max_parallel_maintenance_workers;
SHOW max_worker_processes;
SHOW max_parallel_workers;

max_parallel_maintenance_workers 参数是 max_worker_processes 的子集,受 max_parallel_workers 限制。如果您需要更多并行工作器,请增加 max_worker_processesmax_parallel_workers 的值。

以下示例展示了如何设置 max_parallel_maintenance_workers

代码示例

SET max_parallel_maintenance_workers TO 4;

max_parallel_maintenance_workers 参数无法保证分配工作器。如需确认多个并行工作器开始重新编入索引,请在开始重新编入索引后从另一个会话运行以下查询:

代码示例

SELECT
leader.leader_pid,leader.pid "worker_pid",leader.query
FROM
pg_stat_activity leader, pg_stat_activity worker
WHERE leader.leader_pid = worker.pid;

重新编制索引

您可以使用 pg_repack 实用程序重新编制索引,而不会阻止生产工作负载。此实用程序可自动执行并简化并发重新编入索引过程,让您可以在不停机的情况下重新编入索引。对于此过程,请使用 pg_repack 1.4.7 版。

完成以下步骤,以使用 pg_repack 重新编制索引:

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

  2. 创建 pg_repack 扩展程序:

    代码示例

    CREATE EXTENSION pg_repack;
  3. 运行以下命令以并发地重新编制索引:

    代码示例

      pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600

    此命令的输出类似如下内容:

    输出

    INFO: repacking index "public.t_i_key"

    如果运行 pg_repack 时发生任何错误,您可以更正错误,然后重试。修复完所有唯一键索引和主键索引后,您应检查是否存在外键违规行为,并纠正发现的任何违规行为。

查找并纠正外键违规行为

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

监控重新编入索引操作

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

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

  1. 如需识别阻止会话,请运行以下查询:

    代码示例

    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;
  2. 如需取消会话,请使用上一查询中阻止会话的 PID 运行以下查询:

    代码示例

    SELECT pg_cancel_backend(PID);

验证索引是否一致

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