PostgreSQL插件—pg_repack的使用说明

PostgreSQL插件—pg_repack的使用说明

概述

pg_repack 是一个 PostgreSQL 扩展，它允许您从表和索引中删除膨胀，并可选择恢复聚集索引的物理顺序。与CLUSTER和VACUUM FULL不同，它在线工作，在处理过程中不会对已处理的表持有独占锁。pg_repack 启动效率高，性能与直接使用 CLUSTER 相当。

它会为待重建的表创建一份副本。首先取一份全量快照，将所有活元组写入新表，并通过触发器将所有针对原表的变更同步至新表，最后通过重命名，使用新的紧实副本替换老表。而对于索引，则是通过PostgreSQL的CREATE (DROP) INDEX CONCURRENTLY完成的。

pg_repack 是pg_reorg项目的一个分支。

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

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

注意：

• 只有超级用户才能使用该实用程序。
• 目标表必须有一个 PRIMARY KEY，或者至少有一个 NOT NULL 列上的 UNIQUE 索引。
• 重整开始之前，最好取消掉所有正在进行的Vacuum任务。
• 对索引做重整之前，最好能手动清理掉可能正在使用该索引的查询。
• 如果出现异常的情况(警如中途强制退出)，有可能会留下未清理的垃圾，需要手工清理。
• 当完成重整，进行重命名替换时，会产生巨量的WAL，有可能会导致复制延迟，而且无法取消。
• 重整特别大的表时，需要预留至少与该表及其索引相同大小的磁盘空间，需要特别小心，手动检查。
• 如果遇到写入速度非常快的，最后阶段replylog的时候，只能等。

要求

• PostgreSQL 版本

  PostgreSQL 9.4、9.5、9.6、10、11、12、13、14、15

• 磁盘

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

下载

墨天轮地址：https://www.modb.pro/doc/96162

github地址：https://github.com/reorg/pg_repack

[root@lyp ~]# ll pg_repack-1.4.8.zip 
-rw-r--r--. 1 root root 126919 Jan  3 15:57 pg_repack-1.4.8.zip
[root@lyp ~]# 
复制

安装

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

$ cd pg_repack
$ make
$ sudo make install
复制

[root@lyp ~]# export PGHOME=/opt/pgsql13.2
[root@lyp ~]# export PATH=$HOME/bin:$PGHOME/bin:$PATH
[root@lyp ~]# 
[root@lyp ~]# cd pg_repack-1.4.8/
[root@lyp pg_repack-1.4.8]# ll
total 16
drwxr-xr-x. 3 root root  107 Jan  4 00:22 bin
-rw-r--r--. 1 root root 1662 Oct  3 19:24 COPYRIGHT
drwxr-xr-x. 2 root root  121 Oct  3 19:24 doc
drwxr-xr-x. 3 root root  248 Jan  4 00:22 lib
-rw-r--r--. 1 root root 1616 Oct  3 19:24 Makefile
-rw-r--r--. 1 root root 1286 Oct  3 19:24 META.json
drwxr-xr-x. 2 root root  219 Oct  3 19:24 msvc
-rw-r--r--. 1 root root 2133 Oct  3 19:24 README.rst
drwxr-xr-x. 4 root root   96 Oct  3 19:24 regress
drwxr-xr-x. 2 root root   54 Oct  3 19:24 SPECS
[root@lyp pg_repack-1.4.8]# 
[root@lyp pg_repack-1.4.8]# 
[root@lyp pg_repack-1.4.8]# make
make[1]: Entering directory `/root/pg_repack-1.4.8/bin'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql13.2/include -DREPACK_VERSION=1.4.8 -I. -I./ -I/opt/pgsql13.2/include/server -I/opt/pgsql13.2/include/internal  -D_GNU_SOURCE   -c -o pg_repack.o pg_repack.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql13.2/include -DREPACK_VERSION=1.4.8 -I. -I./ -I/opt/pgsql13.2/include/server -I/opt/pgsql13.2/include/internal  -D_GNU_SOURCE   -c -o pgut/pgut.o pgut/pgut.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql13.2/include -DREPACK_VERSION=1.4.8 -I. -I./ -I/opt/pgsql13.2/include/server -I/opt/pgsql13.2/include/internal  -D_GNU_SOURCE   -c -o pgut/pgut-fe.o pgut/pgut-fe.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 pg_repack.o pgut/pgut.o pgut/pgut-fe.o  -L/opt/pgsql13.2/lib   -Wl,--as-needed -Wl,-rpath,'/opt/pgsql13.2/lib',--enable-new-dtags  -L/opt/pgsql13.2/lib -lpq -L/opt/pgsql13.2/lib -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -ldl -lm -o pg_repack
make[1]: Leaving directory `/root/pg_repack-1.4.8/bin'
make[1]: Entering directory `/root/pg_repack-1.4.8/lib'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -DREPACK_VERSION=1.4.8 -I. -I./ -I/opt/pgsql13.2/include/server -I/opt/pgsql13.2/include/internal  -D_GNU_SOURCE   -c -o repack.o repack.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -DREPACK_VERSION=1.4.8 -I. -I./ -I/opt/pgsql13.2/include/server -I/opt/pgsql13.2/include/internal  -D_GNU_SOURCE   -c -o pgut/pgut-spi.o pgut/pgut-spi.c
( echo '{ global:'; gawk '/^[^#]/ {printf "%s;\n",$1}' exports.txt; echo ' local: *; };' ) >exports.list
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -Wl,--version-script=exports.list -o pg_repack.so repack.o pgut/pgut-spi.o -L/opt/pgsql13.2/lib    -Wl,--as-needed -Wl,-rpath,'/opt/pgsql13.2/lib',--enable-new-dtags  
sed 's,REPACK_VERSION,1.4.8,g' pg_repack.sql.in \
| sed 's,relhasoids,false,g'> pg_repack--1.4.8.sql;
sed 's,REPACK_VERSION,1.4.8,g' pg_repack.control.in > pg_repack.control
make[1]: Leaving directory `/root/pg_repack-1.4.8/lib'
make[1]: Entering directory `/root/pg_repack-1.4.8/regress'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/root/pg_repack-1.4.8/regress'
[root@lyp pg_repack-1.4.8]# 
[root@lyp pg_repack-1.4.8]# 
[root@lyp pg_repack-1.4.8]# 
[root@lyp pg_repack-1.4.8]# make install
make[1]: Entering directory `/root/pg_repack-1.4.8/bin'
/usr/bin/mkdir -p '/opt/pgsql13.2/bin'
/usr/bin/install -c  pg_repack '/opt/pgsql13.2/bin'
make[1]: Leaving directory `/root/pg_repack-1.4.8/bin'
make[1]: Entering directory `/root/pg_repack-1.4.8/lib'
/usr/bin/mkdir -p '/opt/pgsql13.2/lib'
/usr/bin/mkdir -p '/opt/pgsql13.2/share/extension'
/usr/bin/mkdir -p '/opt/pgsql13.2/share/extension'
/usr/bin/install -c -m 755  pg_repack.so '/opt/pgsql13.2/lib/pg_repack.so'
/usr/bin/install -c -m 644 .//pg_repack.control '/opt/pgsql13.2/share/extension/'
/usr/bin/install -c -m 644  pg_repack--1.4.8.sql pg_repack.control '/opt/pgsql13.2/share/extension/'
make[1]: Leaving directory `/root/pg_repack-1.4.8/lib'
make[1]: Entering directory `/root/pg_repack-1.4.8/regress'
make[1]: Nothing to be done for `install'.
make[1]: Leaving directory `/root/pg_repack-1.4.8/regress'
[root@lyp pg_repack-1.4.8]# 
复制

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

$ psql -c "CREATE EXTENSION pg_repack" -d test
复制

[postgres@lyp ~]$ psql -c "CREATE EXTENSION pg_repack" -d test
CREATE EXTENSION
[postgres@lyp ~]$ psql -d test
psql (13.2)
Type "help" for help.

test=# \dx pg_repack 
                                List of installed extensions
   Name    | Version | Schema |                         Description                          
-----------+---------+--------+--------------------------------------------------------------
 pg_repack | 1.4.8   | public | Reorganize tables in PostgreSQL databases with minimal locks
(1 row)

test=# 
复制

您可以使用DROP EXTENSION pg_repack删除 pg_repack或仅删除repack模式。

如果你是从以前版本的 pg_repack 或 pg_reorg 升级，只需如上所述从数据库中删除旧版本并安装新版本。

用法

pg_repack [选项] ... [数据库名称]
复制

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

• 选项：

  -a, --all 重新打包所有数据库

  -t, --table=TABLE 仅重新打包特定表

  -I, --parent-table=TABLE 重新打包特定的父表及其继承者

  -c, --schema=SCHEMA 仅在特定模式中重新打包表

  -s, --tablespace=TBLSPC 将重新打包的表移动到新的表空间

  -S, --moveidx 将重新打包的索引也移动到TBLSPC

  -o, --order-by=COLUMNS 按列而不是簇键排序

  -n, --no-order 做 vacuum full 而不是 cluster

  -N, --dry-run 打印将被重新包装的内容并退出

  -j, --jobs=NUM 为每个表使用这么多并行作业

  -i, --index=INDEX 只移动指定的索引

  -x, --only-indexes 只移动指定表的索引

  -T, --wait-timeout=SECS 超时以取消冲突的其他后端

  -D, --no-kill-backend 超时时不要杀死其他后端

  -Z, --no-analyze 最后不分析

  -k, --no-superuser-check 跳过客户端中的超级用户检查

  -C, --exclude-extension 不要重新打包属于特定扩展名的表

• 连接选项：

  -d, --dbname=DBNAME 要连接的数据库

  -h, --host=HOSTNAME 数据库服务器主机或套接字目录

  -p, --port=PORT 数据库服务器端口

  -U, --username=USERNAME 连接的用户名

  -w, --no-password 从不提示输入密码

  -W, --password 强制密码提示

• 通用选项：

  -e, --echo 回显查询

  -E, --elevel=LEVEL 设置输出消息级别

  –help 显示此帮助，然后退出

  –version 输出版本信息，然后退出

重组选项

• -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

  跳过属于指定扩展名的表。某些扩展可能在计划时间等方面严重依赖于此类表。

连接选项

连接到服务器的选项。您不能同时使用--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

  回声命令发送到服务器。

• -E LEVEL，--elevel=LEVEL

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

• --help

  显示程序的使用情况。

• --version

  显示程序的版本号。

环境

• PGDATABASE , PGHOST , PGPORT , PGUSER

  默认连接参数该实用程序与大多数其他 PostgreSQL 实用程序一样，也使用 libpq 支持的环境变量。

例子

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

[postgres@lyp ~]$ pg_repack test
INFO: repacking table "public.t1"
INFO: repacking table "public.t2"
[postgres@lyp ~]$ 
复制

对数据库测试中的表foo和bar执行在线 VACUUM FULL（忽略最终的集群索引）：

[postgres@lyp ~]$ pg_repack --no-order --table t1 --table t2 test
INFO: repacking table "public.t1"
INFO: repacking table "public.t2"
[postgres@lyp ~]$ 
复制

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

test=# select * from pg_indexes where tablename='t1';
 schemaname | tablename | indexname | tablespace |                          indexdef                          
------------+-----------+-----------+------------+------------------------------------------------------------
 public     | t1        | pk_t2_id  |            | CREATE UNIQUE INDEX t1_pkey ON public.t1 USING btree (id)
(1 row)

test=# \q
[postgres@lyp ~]$ 
INFO: repacking indexes of "t1"
INFO: repacking index "pub
[postgres@lyp ~]$ 
[postgres@lyp ~]$ psql -d test
psql (13.2)
Type "help" for help.

test=# 
test=# select * from pg_indexes where tablename='t1';
 schemaname | tablename | indexname | tablespace |                         indexdef                          
------------+-----------+-----------+------------+-----------------------------------------------------------
 public     | t1        | t1_pkey   | lxs        | CREATE UNIQUE INDEX t1_pkey ON public.t1 USING btree (id)
(1 row)

test=# 
复制

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

test=# select * from pg_indexes where tablename='t2';
 schemaname | tablename | indexname | tablespace |                          indexdef                          
------------+-----------+-----------+------------+------------------------------------------------------------
 public     | t2        | pk_t2_id  |            | CREATE UNIQUE INDEX pk_t2_id ON public.t2 USING btree (id)
(1 row)

test=# \q
[postgres@lyp ~]$ pg_repack -d test --index pk_t2_id --tablespace lxs
INFO: repacking index "public.pk_t2_id"
[postgres@lyp ~]$ psql -d test
psql (13.2)
Type "help" for help.

test=# select * from pg_indexes where tablename='t2';
 schemaname | tablename | indexname | tablespace |                          indexdef                          
------------+-----------+-----------+------------+------------------------------------------------------------
 public     | t2        | pk_t2_id  | lxs        | CREATE UNIQUE INDEX pk_t2_id ON public.t2 USING btree (id)
(1 row)

test=# 
复制

诊断

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 1.4.8 is not installed in the database

  当指定--all选项时，pg_repack 不会安装在数据库中。在数据库中创建 pg_repack 扩展。

• ERROR: pg_repack 1.4.8 is not installed in the database

  pg_repack 没有安装在--dbname指定的数据库中。在数据库中创建 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

  目标表没有定义 PRIMARY KEY 或任何 UNIQUE 约束。在表上定义 PRIMARY KEY 或 UNIQUE 约束。

• 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

  详细信息： 中断的表上的先前 pg_repack 可能留下了无效索引。请使用 DROP INDEX “schema”.“index_xxxxx” 删除该索引并重试。显然由 pg_repack 创建的临时索引已被遗忘，我们不想冒险自己删除该索引。如果索引实际上是由一个没有被清理的旧 pg_repack 作业创建的，你应该只使用DROP INDEX并再次尝试 repack 命令。

限制

pg_repack 有以下限制。

临时表

pg_repack 不能重组临时表。

GiST 索引

pg_repack 不能通过 GiST 索引聚集表。

DDL 命令

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

细节

Full Table Repacks

要执行full-table repack，pg_repack 将：

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

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

Index Only Repacks

要执行index-only repack，pg_repack 将：

1. 使用 CONCURRENTLY 匹配旧索引的定义在表上创建新索引
2. 将目录中的旧索引换成新索引
3. 删除旧索引