PostgreSQL的扩展（extensions）-常用的扩展之pg_repack

PostgreSQL的扩展（extensions）-常用的扩展之pg_repack

基础信息
OS版本：Red Hat Enterprise Linux Server release 7.9 (Maipo)
DB版本：16.2
pg软件目录：/home/pg16/soft
pg数据目录：/home/pg16/data
端口：5777

pg_repack 是一款非常有用的 PostgreSQL 扩展工具，它能够重新打包（repack）表和索引以回收空间并减少碎片，而且在这个过程中不会锁定表，允许数据库在重整过程中继续对数据进行读写操作。这是与 PostgreSQL 内建的 VACUUM FULL 命令相比的一个重大优势，因为 VACUUM FULL 在重新组织表以回收空间时会锁定表。

特性

• 最小化锁定时间：pg_repack在重组表和索引的时候减少了锁的使用时间，使得数据库对于读写操作几乎总是可用的。
• 重新打包表和索引：不仅可以对表进行重新打包，还可以重新打包索引，减少索引碎片。
• 兼容性：支持多个 PostgreSQL 版本。

安装 pg_repack

从源代码安装

如果你的系统没有预打包的 `pg_repack` 版本，你可以从源代码编译安装。

下载网址：https://pgxn.org/dist/pg_repack/

--编译安装
cd /home/pg16/resource
[pg16@test resource]$ unzip pg_repack-1.5.0.zip 
[pg16@test resource]$ cd pg_repack-1.5.0/
[pg16@test pg_repack-1.5.0]$ make
[pg16@test pg_repack-1.5.0]$ make install

当你安装好 pg_repack 后，需要在目标数据库上创建扩展，例如当前是在postgres 库上创建

[pg16@test pg_repack-1.5.0]$ psql -p 5777
psql (16.2)
Type "help" for help.

postgres=# CREATE EXTENSION pg_repack;
CREATE EXTENSION
postgres=# SELECT * FROM pg_extension;
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 14270 | plpgsql            |       10 |           11 | f              | 1.0        |           | 
 16423 | pg_stat_statements |       10 |         2200 | t              | 1.10       |           | 
 16454 | pg_repack          |       10 |         2200 | f              | 1.5.0      |           | 
(3 rows)

使用 pg_repack

通过help查看帮助文档。

[pg16@test ~]$ 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
      --error-on-invalid-index  don't repack tables which belong to specific extension
      --switch-threshold    switch tables when that many tuples are left to catchup

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 之后，你可以通过命令行工具 pg_repack 来重新组织表和索引。以下是一些基本用法：

示例1

重新打包特定表，如 yewu1.t4

[pg16@test ~]$ pg_repack -d white -t yewu1.t4
INFO: repacking table "yewu1.t4"

示例2

重新打包指定数据库的所有表

[pg16@test ~]$ pg_repack -d white
INFO: repacking table "public.pgbench_accounts"
INFO: repacking table "public.pgbench_branches"
INFO: repacking table "public.pgbench_tellers"
INFO: repacking table "yewu1.t4"

示例3

重新打包索引

[pg16@test ~]$ pg_repack -d white --only-indexes -t yewu1.t4            
INFO: repacking indexes of "yewu1.t4"
INFO: repacking index "yewu1.idx_t2"
INFO: repacking index "yewu1.idx_t4"
INFO: repacking index "yewu1.t4_pkey"

请记得，使用 pg_repack 之前，确保你已经有了足够的权限来执行这些操作，并且对数据库做了适当的备份。

注意事项

• 在 pg_repack 运行期间，尽管它最小化了锁的时间，但在最后阶段仍然需要短暂的锁定，以便完成表的替换。因此，最好在数据库负载相对较低的时间段运行它。
• 确保在执行大量数据重组之前备份数据库，以免出现不可预测的情况导致数据丢失。

谨记：心存敬畏，行有所止。