Azure Database for PostgreSQL 灵活服务器的 pg_dump 和 pg_restore 最佳做法

项目
08/22/2024

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

本文介绍用于加速 pg_dump 和 pg_restore 的选项与最佳做法。还介绍了用于执行 pg_restore 的最佳服务器配置。

pg_dump 的最佳做法

可以使用 pg_dump 实用工具将 Azure Database for PostgreSQL 灵活服务器数据库提取到脚本文件或存档文件中。以下部分列出了一些可用于通过 pg_dump 减少总体转储时间的命令行选项。

目录格式 (-Fd)

此选项输出一个目录格式存档，该存档可以输入到 pg_restore。默认情况下，输出会进行压缩。

并行作业 (-j)

借助 pg_dump，可以使用并行作业选项并发运行转储作业。此选项可缩短总转储时间，但也会增加数据库服务器上的负载。我们建议在密切监视 CPU、内存和 IOPS（每秒输入/输出操作数）使用率等源服务器指标后得出并行作业值。

为并行作业选项设置值时，pg_dump 需要以下信息：

连接数必须等于并行作业数 +1，因此请务必相应地设置 max_connections 值。
并行作业数应小于或等于为数据库服务器分配的 vCPU 数。

压缩 (-Z0)

此选项指定要使用的压缩级别。零表示不压缩。在运行 pg_dump 期间，零压缩可以帮助提高性能。

表膨胀和清理

在启动 pg_dump 进程之前，请考虑是否有必要清空表。表膨胀会显著增加 pg_dump 时间。执行以下查询以识别表膨胀：

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

此查询中的 dead_pct 列是死元组的百分比（与活元组相比）。如果某个表的 dead_pct 值较高，可能指示该表未正确清空。有关详细信息，请参阅 Azure Database for PostgreSQL 灵活服务器中的 Autovacuum 优化。

对于识别的每个表，可以通过运行以下命令执行手动清空分析：

vacuum(analyze, verbose) <table_name>

使用 PITR 服务器

可以在联机或实时服务器上执行 pg_dump。即使使用了数据库，它也会进行一致性备份。它不会阻止其他用户使用数据库。在启动 pg_dump 进程之前，请考虑数据库大小和其他业务或客户需求。小型数据库可能很适合在生产服务器上执行 pg_dump。

对于大型数据库，可以通过生产服务器创建时间点恢复 (PITR) 服务器，然后在 PITR 服务器上执行 pg_dump 进程。在 PITR 上运行 pg_dump 将是冷运行过程。此方法的好处是不必担心实际生产服务器上运行的 pg_dump 进程会额外利用 CPU、内存和 IO。你可以在 PITR 服务器上运行 pg_dump，并在完成 pg_dump 进程后删除 PITR 服务器。

pg_dump 的语法

对 pg_dump 使用以下语法：

pg_dump -h <hostname> -U <username> -d <databasename> -Fd -j <Num of parallel jobs> -Z0 -f sampledb_dir_format

pg_restore 的最佳做法

可以使用 pg_restore 实用工具从 pg_dump 创建的存档还原 Azure Database for PostgreSQL 灵活服务器数据库。以下部分列出了一些用于减少总体还原时间的命令行选项。

并行还原

使用多个并发作业可以缩短在多 vCore 目标服务器上还原大型数据库的时间。作业数可以等于或小于为目标服务器分配的 vCPU 数。

服务器参数

如果要将数据还原到新服务器或非生产服务器，可以在运行 pg_restore 之前优化以下服务器参数：

work_mem = 32 MB
max_wal_size = 65536 (64 GB)
checkpoint_timeout = 3600 #60min
maintenance_work_mem = 2097151 (2 GB)
autovacuum = off
wal_compression = on

还原完成后，请确保根据工作负载要求相应更新上述所有参数。

注意

请仅在内存和磁盘空间足够时遵循上述建议。如果你的小型服务器具有 2、4 或 8 个 vCore，请相应地设置参数。

其他注意事项

在运行 pg_restore 之前禁用高可用性 (HA)。
还原完成后分析所有已迁移的表。

pg_restore 的语法

对 pg_restore 使用以下语法：

pg_restore -h <hostname> -U <username> -d <db name> -Fd -j <NUM> -C <dump directory>

-Fd：目录格式。
-j：作业数。
-C：首先在输出中包含用于创建数据库本身，然后重新连接到该数据库的命令。

下面用示例说明了此语法如何显示：

pg_restore -h <hostname> -U <username> -j <Num of parallel jobs> -Fd -C -d <databasename> sampledb_dir_format

虚拟机注意事项

在同一 Azure 区域和可用性区域中（其中最好同时包含目标服务器和源服务器）创建虚拟机。或者，在至少比较靠近源服务器或目标服务器的位置创建虚拟机。我们建议使用具有高性能本地 SSD 的 Azure 虚拟机。

有关 SKU 的详细信息，请参阅：

通过