postgresql扩展 pg_repack

原创贾勇智 2022-04-14

3038

PostgreSQL 扩展pg_repack是是 pg_reorg 项目的一个分支，但pg_reorg已停止更新。

作用：从表和索引中删除膨胀，并可选择恢复聚集索引的物理顺序。用最少的锁重新组织 PostgreSQL 数据库中的表
异同：与 CLUSTER 和 VACUUM FULL 不同，它在线工作，在处理过程中不会对已处理的表持有独占锁。
性能：pg_repack 启动效率很高，性能与直接使用 CLUSTER 相当。

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

在线 CLUSTER（按集群索引排序）
按指定列排序
在线 VACUUM FULL（仅包装行）
仅重建或重定位表的索引

注意：
只有超级用户才能使用该实用程序。
目标表必须有一个 PRIMARY KEY，或者在 NOT NULL 列上至少有一个唯一的总索引。

要求

PostgreSQL 版本 9.4、9.5、9.6、10、11、12、13
磁盘
执行全表重新打包需要大约两倍于目标表及其索引的可用磁盘空间。例如，如果要重组的表和索引的总大小为 1GB，则需要额外的 2GB 磁盘空间。

下载

可以从 PGXN 网站下载pg_repack。解压缩存档并按照安装说明进行操作。
或者，您可以使用 PGXN 客户端下载、编译和安装软件包；采用：

$ pgxn install pg_repack
复制

检查 pgxn 安装文档以获取可用选项。

安装

pg_repack 可以在 UNIX 或 Linux 上使用 make 构建。自动使用 PGXS 构建框架。在构建之前，可能需要安装 PostgreSQL 开发包（postgresql-devel 等）并将包含 pg_config 的目录添加到您的 $PATH。然后你可以运行：

cd pg_repack
make
make install
复制

您还可以使用 Microsoft Visual C++ 2010 在 Windows 上构建程序。 msvc 文件夹中有项目文件。

安装后，在要处理的数据库中加载 pg_repack 扩展。 pg_repack 被打包为一个扩展，所以你可以执行：

psql -c "CREATE EXTENSION pg_repack" -d your_database
复制

您可以使用 DROP EXTENSION pg_repack 删除 pg_repack 或仅删除重新打包模式。

如果您从先前版本的 pg_repack 或 pg_reorg 升级，只需按照上述说明从数据库中删除旧版本并安装新版本。

用法

pg_repack [OPTION]... [DBNAME]
复制

The following options can be specified in OPTIONS.
Options:
-a, --all repack all databases
-t, --table=TABLE
repack specific table only
-I, --parent-table=TABLE
repack specific parent table and its inheritors
-c, --schema=SCHEMA
repack tables in specific schema only
-s, --tablespace=TBLSPC
move repacked tables to a new tablespace
-S, --moveidx move repacked indexes to TBLSPC too
-o, --order-by=COLUMNS
order by columns instead of cluster keys
-n, --no-order do vacuum full instead of cluster
-N, --dry-run print what would have been repacked and exit
-j, --jobs=NUM Use this many parallel jobs for each table
-i, --index=INDEX
move only the specified index
-x, --only-indexes
move only indexes of the specified table
-T, --wait-timeout=SECS
timeout to cancel other backends on conflict
-D, --no-kill-backend
don’t kill other backends when timed out
-Z, --no-analyze
don’t analyze at end
-k, --no-superuser-check
skip superuser checks in client
-C, --exclude-extension
don’t repack tables which belong to specific extension
Connection options: <<<<<
-d, --dbname=DBNAME
database to connect
-h, --host=HOSTNAME
database server host or socket directory
-p, --port=PORT
database server port
-U, --username=USERNAME
user name to connect as
-w, --no-password
never prompt for password
-W, --password force password prompt
Generic options: <<<<<
-e, --echo echo queries
-E, --elevel=LEVEL
set output message level
–help show this help, then exit
–version output version information, then exit

Reorg 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
创建到 PostgreSQL 的指定数量的额外连接，并使用这些额外连接来并行重建每个表上的索引。仅全表重新打包支持并行索引构建，不支持 --index 或 --only-indexes 选项。如果您的 PostgreSQL 服务器有额外的内核和可用的磁盘 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 内无法获取锁，则跳到重新打包表，而不是取消冲突的查询。默认值为假。
-Z, --no-analyze
在全表重组后禁用 ANALYZE。如果未指定，则在重组后运行 ANALYZE。
-k, --no-superuser-check
跳过客户端中的超级用户检查。此设置对于在支持以非超级用户身份运行的平台上使用 pg_repack 很有用。
-C, --exclude-extension
跳过属于指定扩展的表。某些扩展可能在计划时严重依赖此类表等。

Connection Options

连接到服务器的选项。您不能同时使用 --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
User name to connect as.
-w, --no-password
永远不要发出密码提示。如果服务器需要密码验证并且密码不能通过其他方式（例如 .pgpass 文件）获得，则连接尝试将失败。此选项在没有用户输入密码的批处理作业和脚本中很有用。
-W, --password
强制程序在连接到数据库之前提示输入密码。

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

通用选项

-e, --echo
回显命令发送到服务器。
-E LEVEL, --elevel=LEVEL
从 DEBUG、INFO、NOTICE、WARNING、ERROR、LOG、FATAL 和 PANIC 中选择输出消息级别。默认值为信息。
–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

pg_repack is not installed in the database when the --all option is specified.
Create the pg_repack extension in the database.

ERROR: pg_repack VER is not installed in the database

pg_repack is not installed in the database specified by --dbname.
Create the pg_repack extension in the database.

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

There is a mismatch between the pg_repack binary and the database library (.so or .dll).

The mismatch could be due to the wrong binary in the $PATH or the wrong database being addressed. Check the program directory and the database; if they are what expected you may need to repeat pg_repack installation.

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

The SQL extension found in the database does not match the version required by the pg_repack program.

You should drop the extension from the database and reload it as described in the installation section.

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

The target table doesn’t have a PRIMARY KEY or any UNIQUE constraints defined.

Define a PRIMARY KEY or a UNIQUE constraint on the table.

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

The target table doesn’t have columns specified by --order-by option.

Specify existing columns.

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

The trigger was probably installed during a previous attempt to run pg_repack on the table which was interrupted and for some reason failed to clean up the temporary objects.

You can remove all the temporary objects by dropping and re-creating the extension: see the installation section for the details.

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

There is a chance of deadlock when two concurrent pg_repack commands are run on the same table. So, try to run the command after some time.

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.

A temporary index apparently created by pg_repack has been left behind, and we do not want to risk dropping this index ourselves. If the index was in fact created by an old pg_repack job which didn’t get cleaned up, you should just use DROP INDEX and try the repack command again.

限制

pg_repack 带有以下限制。

Temp tables

pg_repack 无法重新组织临时表。

GiST 索引

pg_repack cannot cluster tables by GiST indexes.

DDL 命令

在 pg_repack 工作时，您将无法执行目标表的 DDL 命令，但 VACUUM 或 ANALYZE 除外。 pg_repack 将在全表重新打包期间对目标表持有 ACCESS SHARE 锁，以强制执行此限制。

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

pg_repack执行细节

全表重新包装

要执行全表重新打包，pg_repack 将：

1.create a log table to record changes made to the original table

2.add a trigger onto the original table, logging INSERTs, UPDATEs and DELETEs into our log table

3.create a new table containing all the rows in the old table

4.build indexes on this new table

5.apply all changes which have accrued in the log table to the new table

6.swap the tables, including indexes and toast tables, using the system catalogs

7.drop the original table

pg_repack 只会在初始设置（上面的步骤 1 和 2）和最终的交换和删除阶段（步骤 6 和 7）期间持有一个访问独占锁很短的时间。在剩下的时间里，pg_repack 只需要在原始表上持有一个 ACCESS SHARE 锁，这意味着 INSERT、UPDATE 和 DELETE 可以照常进行。

仅索引重新包装

要执行仅索引重新打包，pg_repack 将：
1.create new indexes on the table using CONCURRENTLY matching the definitions of the old indexes

2.swap out the old for the new indexes in the catalogs

3.drop the old indexes
同时创建索引有一些注意事项，请参阅文档了解详细信息。

创建索引可能会干扰数据库的常规操作。通常 PostgreSQL 会锁定要索引的表以防止写入，并通过对表的单次扫描来执行整个索引构建。其他事务仍然可以读取该表，但如果它们尝试插入、更新或删除表中的行，它们将阻塞，直到索引构建完成。如果系统是实时生产数据库，这可能会产生严重影响。对非常大的表建立索引可能需要很多小时，即使对于较小的表，索引构建也会在生产系统无法接受的长时期内锁定写入者。

PostgreSQL 支持在不锁定写入的情况下构建索引。通过指定 CREATE INDEX 的 CONCURRENTLY 选项调用此方法。当使用这个选项时，PostgreSQL 必须对表执行两次扫描，此外它必须等待所有可能修改或使用索引的现有事务终止。因此，与标准索引构建相比，此方法需要更多的总工作量，并且需要更长的时间才能完成。但是，由于它允许在构建索引时继续正常操作，因此此方法对于在生产环境中添加新索引很有用。当然，索引创建带来的额外 CPU 和 I/O 负载可能会减慢其他操作。

在并发索引构建中，索引实际上是在一个事务中输入到系统目录中，然后在另外两个事务中发生两次表扫描。在每次表扫描之前，索引构建必须等待已修改表的现有事务终止。在第二次扫描之后，索引构建必须等待在第二次扫描之前具有快照（参见第 13 章）的任何事务终止，包括在其他表上的并发索引构建的任何阶段使用的事务，如果所涉及的索引是部分或具有不是简单列引用的列。然后最后可以将索引标记为可供使用，并且 CREATE INDEX 命令终止。然而，即便如此，索引也可能无法立即用于查询：在最坏的情况下，只要在索引构建开始之前存在事务，它就无法使用。