You need to enable JavaScript to run this app.
导航
清理空间(pg_repack)
最近更新时间:2024.07.31 14:12:08首次发布时间:2023.06.20 15:31:32

云数据库 PostgreSQL 版支持通过插件 pg_repack 提供在线 Vacuum Full 的能力,有效解决因为频繁 Update、Delete 等操作引起的表和索引等对象所占据的物理磁盘空间膨胀的问题。相较于 Cluster 和 Vacuum Full,pg_repack 在执行过程中,不会阻塞对目标表的 DML 操作。

原理介绍

pg_repack 支持对全表和索引进行 repack,缩减因 Update、Delete 等操作引起的表和索引等对象所占的磁盘空间。

对全表进行 repack

对全表进行 repack 的实现原理如下:

  1. 创建日志表记录对原表的变更。

  2. 在原表创建触发器,将原表的 INSERTUPDATEDELETE 操作记录到日志表中。

  3. 创建新表,与原表的行列相同。

  4. 对新表执行 INSERT INTO SELECT,将原表数据导入新表。

  5. 在新表中创建和原表一一对应的索引。

  6. 将日志表里的变更应用到新表。

  7. 将新表及其索引和 repack 的原表及其索引进行物理文件交换。

  8. 删除新表和新索引。

  9. 对目标表执行 ANALYZE,更新统计信息。

    pg_repack 会在上述第 1、2 步和第 6~8 步短暂持有 ACCESS EXCLUSIVE 锁。
    其余步骤 pg_repack 只需要持有原表的 ACCESS SHARE 锁,不影响原表的 INSERTUPDATEDELETE

对索引进行 repack

对索引进行 repack 的实现原理如下:

  1. 以 CONCURRENTLY 方式创建新索引。

  2. 将新索引和 repack 的目标索引进行物理文件交换。

  3. 删除旧索引文件。

使用限制

  • pg_repack 需配合客户端使用,云数据库 PostgreSQL 版目前只支持 pg_repack 1.4.8 版本,同时要求服务端二进制版本与客户端相同。

  • pg_repack 需要额外的存储空间。全表 repack 时,剩余存储空间需至少是待 repack 表和待 repack 表所有索引占据的总空间和的 2 倍。

  • 如果待 repack 的目标表有主键,且主键索引定义存在 INCLUDE 子句,则被 INCLUDE 的列必须 NOT NULL。

  • pg_repack 无法对临时表进行清理。

  • pg_repack 运行时无法对 repack 操作中的表执行 DDL。pg_repack 会持有 ACCESS SHARE 锁,禁止 DDL 执行。

  • 重建表和索引时会占用较多的磁盘 IO,建议在使用时提前评估对业务的影响。

  • pg_repack 期间不要执行大量的 DML 操作,尤其是 UPDATE,否则会影响 repack 的效率和效果。

  • pg_repack 期间会产生大量的 WAL 日志,会对归档产生一定的影响。

  • 必须在需要进行 repack 的目标数据库创建插件。

  • pg_repack 必须以高权限用户执行。

  • 在对大表执行 repack 之前,建议对参与 repack 的高权限账号执行 ALTER USER 高权限账号名 SET idle_in_transaction_session_timeout to 0,防止因为 repack 时间超长导致 repack 任务失败。在 repack 任务执行完成后,通过 ALTER USER 高权限账号名 RESET idle_in_transaction_session_timeout 重置即可。

  • 在待执行 repack 的目标库上,不能存在 FOR ALL TABLES 的 publication,否则会导致 repack 在最后应用增量阶段失败。在执行 repack 前,可以通过 SELECT count(*) FROM pg_publication WHERE puballtables = true 查询待 repack 的目标库上是否存在 FOR ALL TABLES 的 publication,查询结果为 0 则表示不存在。

创建与删除插件

  • 创建插件

    CREATE EXTENSION pg_repack;
    
  • 删除插件

    DROP EXTENSION pg_repack;
    

安装客户端

pg_repack 需配合客户端使用,编译方式如下:

  1. 下载 PostgreSQL 12.13,并解压、编译和安装。
wget https://github.com/postgres/postgres/archive/refs/tags/REL_12_13.tar.gz
tar -zxf REL_12_13.tar.gz
cd postgres-REL_12_13/

./configure --prefix=/usr/local/pgsql-12.13
make clean
make -sj
make install
  1. 配置环境变量。
export PG_HOME=/usr/local/pgsql-12.13
export PG_CONFIG=${PG_HOME}/bin/pg_config
export PATH=${PG_HOME}/bin:${PATH}

export PG_INCLUDE_DIR=$(${PG_HOME}/bin/pg_config --includedir)
export PG_PKG_INCLUDE_DIR=$(${PG_HOME}/bin/pg_config --pkgincludedir)
export PG_SERVER_INCLUDE_DIR=$(${PG_HOME}/bin/pg_config --includedir-server)
export PG_LIB_DIR=$(${PG_HOME}/bin/pg_config --libdir)
export PG_PKG_LIB_DIR=$(${PG_HOME}/bin/pg_config --pkglibdir)

export LD_LIBRARY_PATH=${PG_LIB_DIR}:${PG_PKG_LIB_DIR}${LD_LIBRARY_PATH+:${LD_LIBRARY_PATH}}
export C_INCLUDE_PATH=${PG_INCLUDE_DIR}:${PG_PKG_INCLUDE_DIR}:${PG_SERVER_INCLUDE_DIR}${C_INCLUDE_PATH+:${C_INCLUDE_PATH}}
export CPLUS_INCLUDE_PATH=${PG_INCLUDE_DIR}:${PG_PKG_INCLUDE_DIR}:${PG_SERVER_INCLUDE_DIR}${CPLUS_INCLUDE_PATH+:${CPLUS_INCLUDE_PATH}}
  1. 下载 pg_repack 1.4.8 版本源码并编译和安装。
wget https://github.com/reorg/pg_repack/archive/refs/tags/ver_1.4.8.tar.gz
tar -zxf ver_1.4.8.tar.gz 
cd pg_repack-ver_1.4.8/

export PG_CONFIG=${PG_HOME}/bin/pg_config
make clean
make -sj
make install

说明

如您的环境为 Linxu x86,可通过以下链接获取包含了 pg_repack 1.4.8 的客户端。

使用示例

  • 查看帮助手册: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 版本。

    pg_repack --version
    
  • 仅打印支持 repack 的表,但不实际执行:--dry-run

    pg_repack --no-superuser-check --dry-run --echo -h dbHost -p dbPort -d dbName -U dbUser --table tableName
    
  • repack 单表。

    pg_repack --no-superuser-check --echo -h dbHost -p dbPort -d dbName -U dbUser --table tableName
    
  • repack 索引。

    • repack 目标表的所有索引

      pg_repack --no-superuser-check --echo -h dbHost -p dbPort -d dbName -U dbUser --table tableName --only-indexes
      
    • repack 目标表的指定索引

      pg_repack --no-superuser-check --echo -h dbHost -p dbPort -d dbName -U dbUser --index=indexName
      
  • repack 所有库。

    出于对 repack 成功率和效率的考虑,建议您优先选择单表 repack,不进行全库 repack。

    pg_repack --no-superuser-check --echo -h dbHost -p dbPort -d dbName -U dbUser --all
    

更多参考

关于 pg_repack 更详细的使用说明请参考 pg_repack 官方帮助文档