PostgreSQL空间回收利器——pg_repack_pgsql repack

PostgreSQL的表或索引发生膨胀后，我们可以使用vacuum full的方式来重建表以回收磁盘空间。但是vacuum full需要持有排它锁，会堵塞读操作。

因此一般在生产系统中使用vacuum full变得不太现实。在这种情况下，我们可以使用pg_repack来进行回收垃圾空间，其使用了增量的方式重组数据，最后通过切换FILENODE完成数据重组。这仅仅在切换FILENODE时需要持有排他锁，非常短暂，影响比VACUUM FULL和rewrite的方式小多了。

安装：

**下载地址：**https://pgxn.org/dist/pg_repack/doc/pg_repack.html#download

安装方式十分简单，直接解压完安装即可。

使用pg_repack有两个前提条件：

1. 必须是超级用户才可以使用此扩展；
2. 表上必须要有primary key，或者表的not null列上必须要有unique index

pg_repack全库：
pg13@cnndr4pptliot-> pg_repack bill
INFO: repacking table “monitor.snap_list”
INFO: repacking table “cron.job”
INFO: repacking table “public.t1”

pg_repack单张表：
pg13@cnndr4pptliot-> pg_repack bill -t t1
INFO: repacking table “public.t1”

pg_repack支持的选项：
pg13@cnndr4pptliot-> 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.