pg_repack: 使用最少的锁重组表数据

十二月 24, 2023

摘要pg_repack是一个 PostgreSQL 外部工具,它允许您从表和索引中回收因膨胀而浪费的空间,还可以选择恢复聚集索引的物理顺序。

本文包含以下部分:

  1. 介绍
  2. 要求
  3. 用法
  4. 环境
  5. 示例
  6. 诊断
  7. 限制
  8. 实现原理
  9. 另见

介绍

pg_repack是一个 PostgreSQL 外部工具,它允许您从表和索引中回收因膨胀而浪费的空间,还可以选择恢复聚集索引的物理顺序。与 CLUSTERVACUUM FULL 不同,它可以在线工作,在处理过程中不会对处理的表进行排他性锁定。pg_repack 的启动效率很高,其性能可与直接使用 CLUSTER 相媲美。

您可以选择以下方法之一进行重组:

  • 在线 CLUSTER(按聚簇索引排序)
  • 按指定列排序
  • 在线 VACUUM FULL(仅重组表行)
  • 仅重建或重定位一个表的索引

注意:

  • 只有超级用户才能使用该外部工具。
  • 目标表必须具有主键,或者至少具有一个在非空列上的唯一性索引。

要求

  • PostgreSQL 版本

    支持 PostgreSQL 9.5、9.6、10、11、12、13、14、15、16。不支持 PostgreSQL 9.4 及更早版本。

  • 磁盘

    执行完整的表重组所需的可用磁盘空间,大约是目标表及其索引的两倍。例如,如果要重组的表和索引的总大小为 1GB,则需要额外的 2GB 磁盘空间。

用法

pg_repack [OPTION]... [DBNAME]

可以在OPTIONS中指定以下选项。

重组选项

  • -a, --all

    尝试重组实例中的所有数据库里面的表数据。未安装pg_repack扩展的数据库会被跳过。

  • -t TABLE, --table=TABLE

    只重组指定的表。可以通过输入多个-t选项,来重组织多个表。默认情况下,会重组目标数据库中所有符合条件的表。

  • -I TABLE, --parent-table=TABLE

    重组指定的表及其继承表。可以通过输入多个-I选项,来重组多个表和它们的继承表。

  • -c, --schema

    只重组指定模式中的表。可以通过输入多个-c选项,来重组多个模式。还可以结合使用--tablespace,将表移动到其他表空间。

  • -o COLUMNS [,...], --order-by=COLUMNS [,...]

    按照指定列排序,在线执行 CLUSTER。

  • -n, --no-order

    在线执行一次 VACUUM FULL。从版本 1.2 开始,这是非聚簇表的默认设置。

  • -N, --dry-run

    列出将要重组的内容并退出。

  • -j, --jobs

    创建指定数量的额外连接,并使用这些额外连接,并行执行每个表上的索引重建。并行重建索引仅支持全表重组,不支持带有--index--only-indexes选项。如果您的 PostgreSQL 服务器有额外的 CPU 计算资源和可用的磁盘 I/O,这可能是加快pg_repack速度的有用方法。

  • -s TBLSPC, --tablespace=TBLSPC

    将要重组的表移动到指定的表空间:这实质上是ALTER TABLE ... SET TABLESPACE的一个在线版本。表的索引将会保留在原来的表空间中,除非还指定了--moveidx

  • -S, --moveidx

    此外,将要重组的表的索引移动到--tablespace选项指定的表空间。

  • -i, --index

    只重组指定的索引。可以通过输入多个-i选项,来重组多个索引。还可以结合使用--tablespace,将索引移动到不同的表空间。

  • -x, --only-indexes

    只重组指定表的索引,必须使用--table或者--parent-table选项,来指定这些索引。

  • -T SECS, --wait-timeout=SECS

    pg_repack需要在重组开始时获取一个排他锁,在重组结束时也需要获取一个排他锁。该设置可控制pg_repack等待获取这些锁的秒数。如果在此持续时间后无法进行锁定,并且未指定--no-kill-backend选项,pg_repack会强制取消冲突的查询。如果您使用的是 PostgreSQL 8.4 或更高版本, 在两次超时后,pg_repack会回退到使用pg_terminate_backend(),以断开任何保留的后端连接。默认值为 60 秒。

  • -D, --no-kill-backend

    如果在--wait-timeout指定的持续时间内无法获取到锁,则跳过表的重组,而不是取消冲突的查询。默认值为 false。

  • -Z, --no-analyze

    重组完一次全表后不执行 ANALYZE。如果未指定,会在重组后运行 ANALYZE。

  • -k, --no-superuser-check

    跳过客户端中的超级用户权限检查。在支持以非超级用户身份运行pg_repack的平台上,此设置对于使用它非常有用。

  • -C, --exclude-extension

    跳过那些属于指定扩展的表。比如:某些扩展可能在规划期间会严重依赖此类表。

  • --switch-threshold

    当日志表中剩余到多少元组时切换表。此设置可用于避免无法跟上写入密集型表的数据增加速度。

连接选项

连接到服务器的选项。您不能将--all选项,和--dbname--table--parent-table同时一起使用。

  • -a, --all

    重组所有数据库里面的表数据。

  • -d DBNAME, --dbname=DBNAME

    指定要重组的数据库的名称。如果未指定此名称且未使用-a(或--all),则会从环境变量 PGDATABASE 中读取数据库名称。如果未设置,则会使用为连接指定的用户名。

  • -h HOSTNAME, --host=HOSTNAME

    指定运行服务器的计算机的主机名。如果该值以斜杠开头,则会将其用作 Unix 域套接字的目录。

  • -p PORT, --port=PORT

    指定服务器监听连接的 TCP 端口,或本地 Unix 域套接字文件扩展名。

  • -U USERNAME, --username=USERNAME

    要连接的用户名。

  • -w, --no-password

    不要发出密码输入提示。如果服务器需要密码身份验证,并且密码无法通过其他方式(如.pgpass文件)获得,则连接尝试会失败。该选项在批处理作业和脚本中非常有用,因为这些作业和脚本中没有用户来输入密码。

  • -W, --password

    强制程序在连接到数据库之前提示输入密码。

    此选项从来都不是必需的,因为如果服务器要求密码身份验证,程序将自动提示输入密码。但是,pg_repack会浪费一次连接,去尝试发现服务器需要密码。在某些情况下,输入-W以避免额外的连接尝试是值得的。

通用选项

  • -e, --echo

    发送到服务器的 Echo 命令。

  • -E LEVEL, --elevel=LEVEL

    DEBUGINFONOTICEWARNINGERRORLOGFATALPANIC中选择输出消息级别。缺省值为INFO

  • --help

    显示程序的用法。

  • --version

    显示程序的版本号。

环境

  • PGDATABASE, PGHOST, PGPORT, PGUSER

    连接参数默认值

    该外部工具与大多数其他的 PostgreSQL 内置工具一样,也使用了 libpq 支持的环境变量(请参阅环境变量)。

示例

对数据库test中的所有聚簇表在线执行一次 CLUSTER,并对所有非聚簇表在线执行一次 VACUUM FULL:

$ pg_repack test

对数据库test中的表foobar,在线执行一次 VACUUM FULL(忽略最终的聚簇索引):

$ pg_repack --no-order --table foo --table bar test

将表foo的所有索引移动到表空间tbs

$ pg_repack -d test --table foo --only-indexes --tablespace tbs

将指定的索引移动到表空间tbs

$ pg_repack -d test --index idx --tablespace tbs

诊断

pg_repack运行失败时,会报告错误消息。以下列表显示了可能的错误原因。

在出现严重错误后,您需要手动清理。要进行清理,只需从数据库中删除 pg_repack 并重新安装:对于 PostgreSQL 9.1 及以上版本,请在发生错误的数据库中执行DROP EXTENSION pg_repack CASCADE,接着执行CREATE EXTENSION pg_repack;对于以前的版本,将脚本$SHAREDIR/contrib/uninstall_pg_repack.sql加载到发生错误的数据库中,然后再次加载$SHAREDIR/contrib/pg_repack.sql

  • INFO: database “db” skipped: pg_repack VER is not installed in the database

    在指定--all选项时,该数据库中没有安装 pg_repack。

    在数据库中创建 pg_repack 扩展。

  • ERROR: pg_repack VER is not installed in the database

    --dbname指定的数据库中,没有安装 pg_repack。

    在数据库中创建 pg_repack 扩展。

  • ERROR: program ‘pg_repack V1’ does not match database library ‘pg_repack V2’

    二进制文件pg_repack和数据库动态库(.so.dll)之间存在不匹配。

    不匹配可能是由于$PATH路径中的二进制文件错误,或者定位到错误的数据库所致。请检查程序目录和数据库;如果它们是预期的,您可能需要重新安装pg_repack

  • ERROR: extension ‘pg_repack V1’ required, found ‘pg_repack V2’

    在数据库中找到的扩展 SQL,与 pg_repack 程序所需的版本不匹配。

    您应该从数据库中删除扩展,并重新加载它。

  • ERROR: relation “table” must have a primary key or not-null unique keys

    目标表未定义主键或任何唯一性约束。

    请在表上定义主键或唯一性约束。

  • ERROR: query failed: ERROR: column “col” does not exist

    目标表上没有--order-by选项指定的列。

    指定存在的列。

  • WARNING: the table “tbl” already has a trigger called repack_trigger

    触发器可能是在之前尝试在表上运行pg_repack时安装的,在处理该表时被中断,并且由于某种原因未能清理这些临时对象。

    您可以通过删除并重新创建扩展,来清理所有的临时对象。

  • ERROR: Another pg_repack command may be running on the table. Please try again later.

    当两个并发的pg_repack命令在同一表上运行时,可能会出现死锁。因此,请尝试在一段时间后再运行该命令。

  • WARNING: Cannot create index “schema”.“index_xxxxx”, already exists

    DETAIL: An invalid index may have been left behind by a previous pg_repack on the table which was interrupted. Please use DROP INDEX “schema”.“index_xxxxx” to remove this index and try again.

    一个显然是由pg_repack创建的临时索引被残留了下来,我们不想冒险直接删除这个索引。如果该索引确实是由之前的pg_repack作业创建的,且未能清理,则应使用 DROP INDEX 删除索引,并再次尝试运行pg_repack命令。

限制

pg_repack的使用存在以下限制。

临时表

pg_repack不能重组临时表。

GiST 索引

pg_repack不能按 GiST 索引对表进行聚簇。

DDL 命令

pg_repack正在目标表上面工作时,您将无法执行除 VACUUM 或 ANALYZE 之外的 DDL 命令。pg_repack会在全表重组期间对目标表持有 ACCESS SHARE 锁,以强制启用该限制。

如果您使用的pg_repack版本是 1.1.8 或更低版本,则在运行pg_repack时,不得尝试对目标表执行任何 DDL 命令。在许多情况下,pg_repack会失败并正确地回退,但是在这些早期版本中的某些情况下,可能会导致数据损坏。

实现原理

全表重组

要执行一次全表重组,pg_repack会:

  1. 创建一个日志表,以记录对原始表所做的更改
  2. 在原始表中添加一个触发器,将 INSERT、UPDATE 和 DELETE 记录到我们的日志表中
  3. 创建一个包含旧表中所有行的新表
  4. 在此新表上构建索引
  5. 将日志表中累积的所有更改应用到新表中
  6. 使用系统表,交换原始表和新表,包括索引和 Toast 表
  7. 删除原始表

在初始设置阶段(上面的步骤 1 和 2),和最后的交换和删除阶段(步骤 6 和 7),pg_repack会短暂地持有一个 ACCESS EXCLUSIVE 锁。在其余的时间里,pg_repack只需要在原始表上保留一个 ACCESS SHARE 锁,这意味着 INSERT、UPDATE 和 DELETE 可以照常进行。

仅重组索引

若要执行一次仅针对索引的重组,pg_repack会:

  1. 使用 CONCURRENTLY 在表上创建新索引,以匹配旧索引的定义
  2. 将系统表中的旧索引切换成新索引
  3. 删除旧索引

并发地创建索引需要注意一些事项,有关详细信息,请参阅文档

另见