pg_repack

功能描述

pg_repack插件可以用来重新组织和压缩数据库中的astore，行存表和索引，主要用于在线重建表和索引，以解决表或索引膨胀的问题，减少碎片并提高性能。

该工具通过复制表配合触发器，实现数据库的在线表重组功能，实际是实现了和vacuum full类似的功能，“重组表”和“清理磁盘碎片”的原理类似，原表的元组之间可能存在一些空洞，pg_repack负责重新组织一个元组排列紧密的新表。

pg_repack与VACUUM FULL相比，后者在做清理时会长时间锁表阻塞DML，而pg_repack不会。pg_repack在原表的基础上，把原表数据拷贝到新表上，拷贝过程中，会通过建立触发器（期间会锁表）记录原表的DML操作，数据拷贝完成后，锁定表并将log更新到新表上，并互换新表（重组后的表）和原表。

注意事项

暂不支持在MySQL兼容模式下使用此插件。
该插件仅V2.2 Build 10(Patch No.15)及以上补丁版本支持。
使用pg_repack前必须确保空间充足。例如表和索引一共占用了1GB的空间，那么执行重组操作就至少需要额外的2GB的空间，才能执行成功，否则会由于空间不足而报错。
不支持列存，ustore行存表，unlogged表的重组。
Vastbase与PostgreSQL对于分区表的底层实现存在差异，PG通过参数--parent-table指定分区表，而Vastbase通过--table参数来指定，会自动repack分区表的每个子分区。

使用方法

1、在使用pg_repack插件之前，需要在对应数据库中执行如下命令安装pg_repack插件：

create extension pg_repack;

2、创建完成插件之后使用特定的命令来运行pg_repack，语法格式如下所示：

pg_repack [OPTION]... [DBNAME]

参数说明

Vastbase暂不支持以下未介绍的参数。

OPTION选项

-a,--all

开启该选项，则会重组所有数据库（所有表），默认为关闭状态。未安装pg_repack插件的数据库将被跳过。

该选项仅在大范围repack时才会生效，因此不能与 -t /--table 选项，-l/--parent-table 选项， -c /--schema选项同时使用。
-t, --table=TABLE

仅重新组织指定的表。可以通过编写多个-t开关来重组多个表。默认情况下，目标数据库中所有符合条件的表都会被重组。
-I, --parent-table=TABLE

重新组织指定的表及其继承者。可以通过编写多个-I开关来重新组织多个表层次结构。

由于继承表的概念目前只在PostgreSQL兼容模式下实现，所以该选项仅在Vastbase的PG兼容模式下可以使用，其它兼容模式下都会作为普通表处理。
-c, --schema=SCHEMA

仅重新打包指定模式中的表。可以通过指定多个-c打包多个模式。可以与--tablespace结合使用以将表移动到不同的表空间。

该选项仅在大范围repack时才会生效，因此不能与 -t /--table 选项，-l/--parent-table 选项， -a / --all选项同时使用。
-s, --tablespace=TBLSPC

默认为空，该选项指定重组后的新表处于哪个表空间。本质上是ALTER TABLE ... SET TABLESPACE 的在线版本。除非同时指定 --moveidx，否则表的索引将保留在原始表空间中。
-S, --moveidx

将重新打包的表的索引移动到 --tablespace 选项指定的表空间。该选项只在-s选项指定后开启才会生效。
-o, --order-by=COLUMNS

默认为空，指定重组后的表元组根据某列进行排序。

-o/--order-by选项不能与-n/--no-order选项同时指定。
-n, --no-order

默认关闭，若开启该选项，则会无序重组新表元组。
-N, --dry-run

指定该选项会对重组做一次预演，该选项开启后不会真正执行repack，但会输出repack的预计相关表信息并退出。
-j, --jobs=NUM

默认为”0”，如果原表存在多个索引的时候，使用该参数可以并发重建索引，提升repack的效率（只在表上索引较多的时候有用）。

仅全表重新打包支持并行索引构建，不支持 --index 或 --only-indexes 选项。如果除Vastbase服务器外还有额外的内核和可用的磁盘 I/O，这可能是加速pg_repack的有用方法。
-i, --index=INDEX

仅重新打包指定的索引。可以通过指定多个 -i 重新打包多个索引。可以与 --tablespace 结合使用以将索引移动到不同的表空间。
- 该选项直接指明了特定的索引表，所以不能同时指定 -t/--table 选项或者 -l/--parent-table 选项，（这两个是数据表相关的选项），相反， -x /--only-indexes 是配合数据表选项的附属选项，所以必须要指定 -t 或者 -l 其中之一选项。
- -i 和 -x 本身是冲突的选项，不能同时使用，在-i 或 -x其中之一开启后，需要注意以下情况：
  - 不能同时指定 -a / --all 选项：repack索引表则必须指定特定索引表。
  - 不能同时指定 -C/--exclude-extension 选项：由于 -C 在只在类似 -a 这种大范围repack才会生效，所以也会和 -i/-x 指定特定索引表冲突。
  - 不能指定 -o /--order-by或者 -n/--no-order 两个和排序相关的选项：这两个选项只能排序数据表的元组，而非索引表。
  - 需要显式指定 -Z即 --no-analyze 选项：analyze命令无法分析索引表，所以需要开启该选项。
  - 不能指定 -j/--jobs 即并行相关选项：无法并行处理特定索引表。
-x, --only-indexes

仅重新打包指定表的索引，表必须使用 --table 或 --parent-table选项指定。当选项开启后，只重组--table 或者 --parent-table 所指定的数据表的索引表，但不重组数据表。
-T, --wait-timeout=SECS

pg_repack过程需要获取共享锁和排它锁，这可能会和其他进程产生冲突。此选项指定了获取锁的超时时间，默认为 60 秒。

如果在此持续时间后无法获取锁且未指定 --no-kill-backend 选项，则 pg_repack 将强制取消冲突查询。
-D, --no-kill-backend

该选项需要结合--wait-timeout选项进行理解，当获取锁的时间超过timeout值，工具会强制关闭冲突进程以完成pg_repack操作，若开启--no-kill-backend选项则不会关闭，而是放弃pg_repack操作。

此选项默认为关闭状态。
-Z, --no-analyze

该选项指定在重组完成后，是否对表进行分析。

Vastbase当前必须显式指定此选项，否则会报错。
-k, --no-superuser-check

默认情况下，pg_repack 需要在连接选项的 --username 选项指定超级用户才能执行，但开启该选项可以忽略超级用户检查。此设置对于在支持以非超级用户身份运行的平台上使用 pg_repack 很有用。
-C, --exclude-extension

默认为空，该选项指定一个拓展插件，在大范围重组的情况下，会忽略该拓展插件所涉及或者所需的系统表和普通表。

该选项仅在大范围repack时才会生效，因此不能与 -t /--table 选项，-l/--parent-table 选项同时使用，同理 -i/-x也不行。

连接选项

连接到服务器的选项，不能同时使用--all 和 --dbname 或 --table 和 --parent-table。

-d, --dbname=DBNAME

指定要重组的数据库的名称。
-h, --host=HOSTNAME

指定运行服务器的机器的主机名。如果该值以斜杠开头，则将其用作 Unix 域套接字的目录。
-p, --port=PORT

指定服务器正在侦听连接的 TCP 端口或本地 Unix 域套接字文件扩展名。
-U, --username=USERNAME

指定要连接的用户名。
-w, --no-password

不出现输入密码提示。如果主机要求密码认证并且密码没有通过其它形式给出，则连接尝试将会失败。
-W, --password

指定-U参数所指定的用户密码。

通用选项

-e, --echo

回显命令发送到服务器。
-E, --elevel=LEVEL

指定输出的消息的等级。

取值范围：DEBUG，INFO，NOTICE，WARNING，ERROR，LOG，FATAL和PANIC。

默认值：INFO
--help

打印帮助信息并退出。
--version

打印版本信息并退出。

示例

前置步骤： 执行如下命令安装pg_repack插件。

create extension pg_repack;

示例1： 对普通表进行重组。

1、创建测试表。

CREATE TABLE repack_test (
    id SERIAL PRIMARY KEY,
    table_data TEXT,
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2、插入数据。

DO $$
DECLARE
    i INT;
BEGIN
    FOR i IN 1..10000 LOOP
        -- 插入10000行随机元组
        INSERT INTO repack_test (table_data) VALUES (md5(random()::text));
    END LOOP;
END;
$$;

3、删除部分数据以模拟碎片化空洞。

DELETE FROM repack_test WHERE id % 10 = 0;  -- 删除其中10%的数据

4、检测表大小。

函数pg_total_relation_size用于查询数据表使用的磁盘空间，包括索引和压缩数据。

SELECT pg_size_pretty(pg_total_relation_size('repack_test'));

返回结果为：

pg_size_pretty
---------------
1216 kB
(1 row)

5、退出vsql，执行pg_repack工具。

pg_repack --dbname=vastbase --table=repack_test --no-superuser-check --no-analyze

执行成功，返回如下内容：

INF0:repacking table "public.repack_test"
WARNING: Trigger function with non-plpgsql type is not recommended.
DETAIL:Non-plpgsql trigger function are not shippable by default.
HINT: Unshippable trigger may lead to bad performance.
INF0:repacking table "public.repack_test" success

6、再次连接vsql并检测表大小。

vsql -d vastbase -r
SELECT pg_size_pretty(pg_total_relation_size('repack_test'));

返回结果如下，数据表占用的磁盘空间已减少：

pg_size_pretty
---------------
1096 kB
(1 row)

示例2： 对继承表进行重组。

由于继承表的概念目前只在PostgreSQL兼容模式下实现，所以该示例执行环境为PG兼容模式。

1、创建父表。

CREATE TABLE vehicle (
    id SERIAL PRIMARY KEY,
    brand VARCHAR(50),
    model VARCHAR(50)
);

2、创建子表。

-- 创建子表 car
CREATE TABLE car (
    id SERIAL PRIMARY KEY,
    brand VARCHAR(50),
    model VARCHAR(50),
    car_type VARCHAR(50)
) INHERITS (vehicle);

-- 创建子表 truck
CREATE TABLE truck (
    id SERIAL PRIMARY KEY,
    brand VARCHAR(50),
    model VARCHAR(50),
    truck_type VARCHAR(50)
) INHERITS (vehicle);

3、向表中插入数据。

-- 插入大量数据到父表 vehicle
INSERT INTO vehicle (brand, model) SELECT 'BrandA', 'ModelA' || i FROM generate_series(1, 10000) AS i;

-- 插入大量数据到子表 car
INSERT INTO car (brand, model, car_type) SELECT 'BrandB', 'ModelB' || i, 'Sedan' FROM generate_series(1, 10000) AS i;

-- 插入大量数据到子表 truck
INSERT INTO truck (brand, model, truck_type) SELECT 'BrandC', 'ModelC' || i, 'Pickup' FROM generate_series(1, 10000) AS i;

4、随机删除部分数据。

DELETE FROM vehicle WHERE id % 10 = 0;
DELETE FROM car WHERE id % 10 = 0;
DELETE FROM truck WHERE id % 10 = 0;

5、检测每个表的空间占用。

SELECT 'vehicle', pg_size_pretty(pg_total_relation_size('vehicle'));
SELECT 'car', pg_size_pretty(pg_total_relation_size('car'));
SELECT 'truck', pg_size_pretty(pg_total_relation_size('truck'));

返回结果为：

?column?    |pg_size_pretty
------------+---------------
vehicle     |  904 kB
(1 row)

?column?    |pg_size_pretty
------------+---------------
car         |  1008 kB
(1 row)

?column?    |pg_size_pretty
------------+---------------
truck       |  1008 kB
(1 row)

6、退出vsql，执行repack。

pg_repack --dbname=vastbase --parent-table=vehicle --no-superuser-check --no-analyze --elevel=DEBUG

7、再次连接vsql并检测重组后表的大小。

vsql -d vastbase -r
SELECT 'vehicle', pg_size_pretty(pg_total_relation_size('vehicle'));
SELECT 'car', pg_size_pretty(pg_total_relation_size('car'));
SELECT 'truck', pg_size_pretty(pg_total_relation_size('truck'));

从如下返回结果可以看出，父表和子表占用的磁盘空间均已减少：

?column?    |pg_size_pretty
------------+---------------
vehicle     |  816 kB
(1 row)

?column?    |pg_size_pretty
------------+---------------
car         |  912 kB
(1 row)

?column?    |pg_size_pretty
------------+---------------
truck       |  912 kB
(1 row)

VastbaseG100