合 PG使用插件pg_repack收缩膨胀的表和索引
Tags： PGPostgreSQL插件vacuum膨胀表膨胀索引膨胀收缩pg_repack
简介
官网：
https://reorg.github.io/pg_repack/
https://pgxn.org/dist/pg_repack
https://github.com/reorg/pg_repack/
Postgresql通过数据多版本实现MVCC（参考：https://www.dbaup.com/pgzhongdemvccduobanbenbingfakongzhi.html ）。删除数据并不会真正删除数据，而是修改标识；更新是通过删除+插入的方式进行，所以在频繁更新的系统，数据膨胀是个头疼的问题，如果不进行处理，数据膨胀倍数可能达到十几倍。
为了处理膨胀问题，pg提供了vacuum工具，vacuum分为普通vacuum和vacuum full，普通vacuum会清理死元组，但是不会进行空间重组，磁盘上的已经利用的空间不会释放给操作系统，但是会释放死元组的空间，后续插入的元组会根据空闲空间管理fsm优先插入空闲空间。Vacuum full清理会释放磁盘空间，但是会获取八级锁（会阻塞所有其它的操作），因为vacuum full的原理是新建一个表数据文件，然后从老表中拷贝数据到新文件中，这个过程会阻塞select。
因为影响业务，pg社区开发了pg_repack工具，老版本叫pg_reorg。pg_repack以extension的方式存在，用户可以自己安装该插件。
PostgreSQL支持通过插件pg_repack在线清理表空间，有效解决因对全表大量更新等操作引起的表膨胀问题。pg_repack无需获取排它锁，相比CLUSTER或VACUUM FULL更加轻量化。
pg_repack是一个可以在线重建表和索引的扩展，它会在数据库中创建一个和需要清理的目标表一样的临时表，将目标表中的数据COPY到临时表，并在临时表上建立和目标表一样的索引，然后通过重命名的方式用临时表替换目标表。
pg_repack 是pg_reorg项目的一个分支。
您可以选择以下方法之一进行重组：
- 在线
CLUSTER（按簇索引排序） - 按指定列排序
- 在线
VACUUM FULL（仅包装行） - 仅重建或重定位表的索引
注意：
- 只有超级用户才能使用该实用程序。
- 目标表必须有一个
PRIMARY KEY，或者至少有一个NOT NULL列上的UNIQUE索引。 - 重整开始之前，最好取消掉所有正在进行的Vacuum任务
- 对索引做重整之前，最好能手动清理掉可能正在使用该索引的查询
- 如果出现异常的情况(警如中途强制退出)，有可能会留下未清理的垃圾，需要手工清理。
- 当完成重整，进行重命名替换时，会产生巨量的WAL，有可能会导致复制延迟，而且无法取消
- 重整特别大的表时，需要预留至少与该表及其索引相同大小的磁盘空间，需要特别小心，手动检查。
- 如果遇到写入速度非常快的，最后阶段replylog的时候，只能等。
安装
Pg_repack的源码在github或者pgxn上都可以下载，https://pgxn.org/dist/pg_repack
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | -- yum安装（PG源就有） yum list pg_repack* yum install -y pg_repack_14 -- wget https://codeload.github.com/reorg/pg_repack/zip/refs/tags/ver_1.4.8 wget https://api.pgxn.org/dist/pg_repack/1.4.8/pg_repack-1.4.8.zip unzip pg_repack-1.4.8.zip cd pg_repack-1.4.8 make && make install -- 或者 apt-get install -y python python3-pip pip3 install pgxnclient pgxn install pg_repack -- Debian依赖 cat > /etc/apt/sources.list <<"EOF" deb http://mirrors.ustc.edu.cn/debian stable main contrib non-free deb http://mirrors.ustc.edu.cn/debian stable-updates main contrib non-free EOF apt-get update -y && apt-get upgrade -y apt install -y curl wget iputils-ping procps net-tools lsb-release build-essential sysstat telnet unzip dialog apt install -y cmake libssl-dev make gcc libreadline-dev postgresql-server-dev-14 liblz4-dev zlib1g-dev libz-dev -- 或者 sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - apt-get update -y apt-get install -y postgresql-14-repack -- 安装插件 create extension pg_repack; |
编译过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | [pg13@lhrpg pg_repack-1.4.6]$ make && make install make[1]: Entering directory `/pg13/soft/pg_repack-1.4.6/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/pg13/pg13/include -DREPACK_VERSION=1.4.6 -I. -I./ -I/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/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/pg13/pg13/include -DREPACK_VERSION=1.4.6 -I. -I./ -I/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/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/pg13/pg13/include -DREPACK_VERSION=1.4.6 -I. -I./ -I/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/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/pg13/pg13/lib -Wl,--as-needed -Wl,-rpath,'/pg13/pg13/lib',--enable-new-dtags -L/pg13/pg13/lib -lpq -L/pg13/pg13/lib/postgresql -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -ldl -lm -o pg_repack make[1]: Leaving directory `/pg13/soft/pg_repack-1.4.6/bin' make[1]: Entering directory `/pg13/soft/pg_repack-1.4.6/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.6 -I. -I./ -I/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/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.6 -I. -I./ -I/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/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/pg13/pg13/lib -Wl,--as-needed -Wl,-rpath,'/pg13/pg13/lib',--enable-new-dtags sed 's,REPACK_VERSION,1.4.6,g' pg_repack.sql.in \ | sed 's,relhasoids,false,g'> pg_repack--1.4.6.sql; sed 's,REPACK_VERSION,1.4.6,g' pg_repack.control.in > pg_repack.control make[1]: Leaving directory `/pg13/soft/pg_repack-1.4.6/lib' make[1]: Entering directory `/pg13/soft/pg_repack-1.4.6/regress' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/pg13/soft/pg_repack-1.4.6/regress' make[1]: Entering directory `/pg13/soft/pg_repack-1.4.6/bin' /usr/bin/mkdir -p '/pg13/pg13/bin' /usr/bin/install -c pg_repack '/pg13/pg13/bin' make[1]: Leaving directory `/pg13/soft/pg_repack-1.4.6/bin' make[1]: Entering directory `/pg13/soft/pg_repack-1.4.6/lib' /usr/bin/mkdir -p '/pg13/pg13/lib/postgresql' /usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension' /usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension' /usr/bin/install -c -m 755 pg_repack.so '/pg13/pg13/lib/postgresql/pg_repack.so' /usr/bin/install -c -m 644 .//pg_repack.control '/pg13/pg13/share/postgresql/extension/' /usr/bin/install -c -m 644 pg_repack--1.4.6.sql pg_repack.control '/pg13/pg13/share/postgresql/extension/' make[1]: Leaving directory `/pg13/soft/pg_repack-1.4.6/lib' make[1]: Entering directory `/pg13/soft/pg_repack-1.4.6/regress' make[1]: Nothing to be done for `install'. make[1]: Leaving directory `/pg13/soft/pg_repack-1.4.6/regress' |
帮助：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | [pg13@lhrpg pg_repack-1.4.6]$ which pg_repack /pg13/pg13/bin/pg_repack [pg13@lhrpg pg_repack-1.4.6]$ [pg13@lhrpg pg_repack-1.4.6]$ pg_repack --help pg_repack re-organizes a PostgreSQL database. Usage: pg_repack [OPTION]... [DBNAME] 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 -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 Read the website for details: <https://reorg.github.io/pg_repack/>. Report bugs to <https://github.com/reorg/pg_repack/issues>. |
pg_repack 是以 extension 的方式工作的。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | postgres=# select * from pg_available_extensions where name like '%repack%' order by name; name | default_version | installed_version | comment -----------+-----------------+-------------------+-------------------------------------------------------------- pg_repack | 1.4.6 | | Reorganize tables in PostgreSQL databases with minimal locks (1 row) postgres=# create extension pg_repack; CREATE EXTENSION postgres=# \dn List of schemas Name | Owner --------+---------- public | postgres repack | postgres (2 rows) postgres=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+------------------------------------------------------------------------ pageinspect | 1.8 | public | inspect the contents of database pages at a low level pg_repack | 1.4.6 | public | Reorganize tables in PostgreSQL databases with minimal locks pg_stat_statements | 1.8 | public | track planning and execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (4 rows) postgres=# \dx pg_repack List of installed extensions Name | Version | Schema | Description -----------+---------+--------+-------------------------------------------------------------- pg_repack | 1.4.6 | public | Reorganize tables in PostgreSQL databases with minimal locks (1 row) postgres=# \dx+ pg_repack Objects in extension "pg_repack" Object description ------------------------------------------------------------------------------- function repack.array_accum(anyelement) function repack.conflicted_triggers(oid) function repack.disable_autovacuum(regclass) function repack.get_alter_col_storage(oid) function repack.get_assign(oid,text) function repack.get_columns_for_create_as(oid) function repack.get_compare_pkey(oid,text) function repack.get_create_index_type(oid,name) function repack.get_create_trigger(oid,oid) function repack.get_drop_columns(oid,text) function repack.get_enable_trigger(oid) function repack.get_index_columns(oid,text) function repack.get_order_by(oid,oid) function repack.get_storage_param(oid) function repack.get_table_and_inheritors(regclass) function repack.oid2text(oid) function repack.repack_apply(cstring,cstring,cstring,cstring,cstring,integer) function repack.repack_drop(oid,integer) function repack.repack_indexdef(oid,oid,name,boolean) function repack.repack_index_swap(oid) function repack.repack_swap(oid) function repack.repack_trigger() function repack.version() function repack.version_sql() schema repack view repack.primary_keys view repack.tables (27 rows) |
用法
1 | 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=SECSpg_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本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
