다음을 통해 공유

在 Azure Database for PostgreSQL 灵活服务器中使用 pg_repack 的完全清空

  • 아티클

适用于： Azure Database for PostgreSQL 灵活服务器

在本文中，你将学习如何使用 pg_repack 移除膨胀和提高 Azure Database for PostgreSQL 灵活服务器性能。 由于频繁更新和删除，表和索引中累积了不必要的数据。 膨胀可能导致数据库大小增长超过预期，并且可能会严重影响某些查询的性能。 使用 pg_repack 回收浪费的空间并更有效地重新组织数据。

什么是 pg_repack？

pg_repack 是一个 PostgreSQL 扩展，它从表和索引中删除膨胀，并更有效地重新组织它们。 pg_repack 的工作原理是创建目标表或索引的新副本，应用过程中发生的任何更改，然后以原子方式交换旧版本和新版本。 除操作开始和结束时的短暂时段外，pg_repack 不需要对已处理表或索引的任何停机时间或排他访问锁。 可使用 pg_repack 优化 Azure Database for PostgreSQL 灵活服务器数据库中的任何表或索引。

如何使用 pg_repack？

若要使用 pg_repack，你需要在 Azure Database for PostgreSQL 灵活服务器数据库中安装扩展，然后运行 pg_repack 命令，并指定要优化的表名或索引。 该扩展获取表或索引上的锁，以防止在优化过程中执行其他操作。 它会删除膨胀并更有效地重新组织数据。

完整表重新打包的工作原理

若要执行完整表重新打包，该扩展遵循以下步骤：

  1. 创建日志表以记录对原始表所做的更改。
  2. 将触发器添加到原始表，将 INSERT、UPDATEE 和 DELETE 记录到日志表中。
  3. 创建包含原始表中所有行的新表。
  4. 对新表生成索引。
  5. 将日志表中记录的所有更改应用于新表。
  6. 交换原始表和新表，包括索引和 toast 表。
  7. 删除原始表。

在这些步骤中，pg_repack 仅在初始设置（步骤 1 和 2）以及最终交换和删除阶段（步骤 6 和 7）期间短时间内保留排他访问锁。 在其余时间，pg_repack 仅需保留对原始表的共享访问锁，允许 INSERT、UPDATEE 和 DELET 照常进行。

限制

pg_repack 有一些限制，在使用它之前应该注意：

  • 目标表必须对 NOT NULL 列具有 PRIMARY KEY 或 UNIQUE 索引，操作才能成功。
  • pg_repack 运行时，你无法对目标表执行任何数据定义语言 (DDL) 命令（VACUUM 或 ANALYZE 除外）。 为了确保强制实施这些限制，pg_repack 在完整表重新打包期间保留对目标表的共享访问锁。

设置

先决条件

  1. 通过创建 pg_repack 扩展并将其加入允许列表来配置该扩展。

生成 pg_repack 客户端应用程序

要使用此扩展，需要一个客户端应用程序，你可对 Ubuntu 实例生成并安装该应用程序。

若要安装 pg_repack 版本 1.4.7，请在 Ubuntu 计算机上运行以下 bash 脚本。

# Create the file repository configuration
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update the package lists
sudo apt-get update
# Install required packages to build the code
sudo apt-get install -y postgresql-server-dev-14 unzip make gcc libssl-dev liblz4-dev zlib1g-dev libreadline-dev libzstd-dev
# Download compressed version of build tree for version 1.4.7 of pg_repack
wget 'https://api.pgxn.org/dist/pg_repack/1.4.7/pg_repack-1.4.7.zip'
# Uncompress build tree
unzip pg_repack-1.4.7.zip
# Set current directory to where build tree was uncompressed
cd pg_repack-1.4.7
# Build code
sudo make
# Copy resulting binaries to /usr/local/bin
sudo cp bin/pg_repack /usr/local/bin
# Run pg_repack to check its version
pg_repack --version

使用 pg_repack

有关如何在具有终结点 pgserver.postgres.database.azure.com、用户名 azureuser 和数据库 foo 的 Azure Database for PostgreSQL 灵活服务器实例的公共架构内名为 info 的表上使用以下命令运行 pg_repack 的示例。

  1. 使用首选客户端，连接到 Azure Database for PostgreSQL 灵活服务器实例。 在本示例中，我们使用 psql。

        psql "host=<server>.postgres.database.chinacloudapi.cn port=5432 dbname=<database> user=<user> password=<password> sslmode=require"

  2. 查找数据库中安装的 pg_repack 扩展的版本。

    SELECT installed_version FROM pg_available_extensions WHERE name = 'pg_repack';

  3. 该扩展的版本必须与客户端应用程序的版本匹配，可通过运行以下命令对此进行检查：

    azureuser@azureuser:~$ pg_repack --version

  4. 针对数据库 foo 中存在的名为“信息”的表运行 pg_repack 客户端。

    pg_repack --host=<server>.postgres.database.chinacloudapi.cn --username=<user> --dbname=<database> --table=info --jobs=2 --no-kill-backend --no-superuser-check

pg_repack 选项

适用于生产工作负载的有用 pg_repack 选项：

  • -k, --no-superuser-check：跳过客户端中的超级用户检查。 此设置有助于在支持将 pg_repack 作为非超级用户运行的平台（例如 Azure Database for PostgreSQL 灵活服务器实例）上使用它。

  • -j, --jobs：创建与 Azure Database for PostgreSQL 灵活服务器的指定数目的额外连接，并使用这些额外连接并行化每个表上的索引重新生成。 仅完整表重新打包支持并行索引生成。

  • --index 或 --only 索引选项：如果你的 Azure Database for PostgreSQL 灵活服务器实例具有额外可用的核心和磁盘 I/O，则使用此选项可有效地加快 pg_repack 速度。

  • -D, --no-kill-backend：如果在等待 --wait-timeout 中指定的时间后还不能获得锁，则跳过对表的重新打包，而不是终止正在运行锁定查询的后端客户端。 --wait-timeout 默认设置为 60 秒。 此参数的默认值为 false。

  • -E LEVEL, --elevel=LEVEL：从 DEBUG、INFO、NOTICE、WARNING、ERROR、LOG、FATAL 和 PANIC 中选择输出消息级别。 默认为 INFO。

若要了解所有选项，请参阅 pg_repack 的文档。

常见问题

pg_repack 是扩展还是客户端可执行文件（如 psql 或 pg_dump）？

pg_repack 实际上两者都是。 pg_repack/lib 具有扩展的代码，包括它创建的架构和 SQL 项目，以及实现其中几个函数的代码的 C 库。

另一方面，pg_repack/bin 具有客户端应用程序的代码，该应用程序知道如何与扩展中实现的可编程性元素进行交互。 此客户端应用程序旨在减轻与服务器端扩展所呈现的不同界面进行交互的复杂性。 它为用户提供了一些更易于理解的命令行选项。 如果不在客户端应用程序的目标数据库上创建该扩展，则客户端应用程序没有用处。 服务器端扩展本身功能齐全，但需要用户理解复杂的交互模式。 该模式将包括执行查询以检索数据（这些数据被用作该扩展实现的函数的输入）等。

相关内容