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

十二月 24, 2023

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

目录

介绍

pg_repack是一个 PostgreSQL 外部工具，它允许您从表和索引中回收因膨胀而浪费的空间，还可以选择恢复聚集索引的物理顺序。与 CLUSTER 和 VACUUM 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

    从DEBUG、INFO、NOTICE、WARNING、ERROR、LOG、FATAL和PANIC中选择输出消息级别。缺省值为INFO。

  • --help

    显示程序的用法。

  • --version

    显示程序的版本号。

环境

  • PGDATABASE, PGHOST, PGPORT, PGUSER

    连接参数默认值

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

示例

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

$ pg_repack test

对数据库test中的表foo和bar，在线执行一次 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. 删除旧索引

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

了解更多

搜索

分类

标签