使用转储和还原迁移 PostgreSQL 数据库Migrate your PostgreSQL database using dump and restore

可以使用 pg_dump 将 PostgreSQL 数据库提取到转储文件,并使用 pg_restore 从 pg_dump 创建的存档文件中还原 PostgreSQL 数据库。You can use pg_dump to extract a PostgreSQL database into a dump file and pg_restore to restore the PostgreSQL database from an archive file created by pg_dump.


若要逐步执行本操作方法指南,需要:To step through this how-to guide, you need:

按照以下步骤转储并还原 PostgreSQL 数据库:Follow these steps to dump and restore your PostgreSQL database:

使用 pg_dump 创建转储文件,该文件包含要加载数据Create a dump file using pg_dump that contains the data to be loaded

若要在本地或某个 VM 中备份现有 PostgreSQL 数据库,请运行以下命令:To back up an existing PostgreSQL database on-premises or in a VM, run the following command:

pg_dump -Fc -v --host=<host> --username=<name> --dbname=<database name> -f <database>.dump

例如,如果有一个本地服务器,并且该服务器中包含一个名为 testdb 的数据库 For example, if you have a local server and a database called testdb in it

pg_dump -Fc -v --host=localhost --username=masterlogin --dbname=testdb -f testdb.dump

使用 pg_restore 将数据还原到目标 Azure Database for PostgreSQLRestore the data into the target Azure Database for PostgreSQL using pg_restore

创建目标数据库后,可以使用 pg_restore 命令和 -d、--dbname 参数将数据从转储文件还原到目标数据库。After you've created the target database, you can use the pg_restore command and the -d, --dbname parameter to restore the data into the target database from the dump file.

pg_restore -v --no-owner --host=<server name> --port=<port> --username=<user@servername> --dbname=<target database name> <database>.dump

包括 --no-owner 参数会导致还原过程中创建的所有对象由使用 --username 指定的用户拥有。Including the --no-owner parameter causes all objects created during the restore to be owned by the user specified with --username. 有关详细信息,请参阅有关 pg_restore 的正式 PostgreSQL 文档。For more information, see the official PostgreSQL documentation on pg_restore.


如果 PostgreSQL 服务器需要 SSL 连接(默认情况下在 Azure Database for PostgreSQL 服务器中启用),请设置环境变量 PGSSLMODE=require,以便 pg_restore 工具使用 SSL 连接。If your PostgreSQL server requires SSL connections (on by default in Azure Database for PostgreSQL servers), set an environment variable PGSSLMODE=require so that the pg_restore tool connects with SSL. 如果不使用 SSL,错误可能会显示为 FATAL: SSL connection is required. Please specify SSL options and retry.Without SSL, the error may read FATAL: SSL connection is required. Please specify SSL options and retry.

在 Windows 命令行中,在运行 pg_restore 命令之前运行命令 SET PGSSLMODE=requireIn the Windows command line, run the command SET PGSSLMODE=require before running the pg_restore command. 在 Linux 或 Bash 中,在运行 pg_restore 命令之前运行命令 export PGSSLMODE=requireIn Linux or Bash run the command export PGSSLMODE=require before running the pg_restore command.

在此示例中,请将数据从转储文件 testdb.dump 还原到目标服务器 mydemoserver.postgres.database.chinacloudapi.cn 上的数据库 mypgsqldbIn this example, restore the data from the dump file testdb.dump into the database mypgsqldb on target server mydemoserver.postgres.database.chinacloudapi.cn.

pg_restore -v --no-owner --host=mydemoserver.postgres.database.chinacloudapi.cn --port=5432 --username=mylogin@mydemoserver --dbname=mypgsqldb testdb.dump

优化迁移过程Optimizing the migration process

将现有的 PostgreSQL 数据库迁移到 Azure Database for PostgreSQL 服务的一种方法是,在源上备份数据库并在 Azure 中进行还原。One way to migrate your existing PostgreSQL database to Azure Database for PostgreSQL service is to back up the database on the source and restore it in Azure. 若要最大限度地缩短完成迁移所需的时间,请考虑将以下参数与备份和还原命令结合使用。To minimize the time required to complete the migration, consider using the following parameters with the backup and restore commands.


有关语法的详细信息,请参阅 pg_dumppg_restore 文章。For detailed syntax information, see the articles pg_dump and pg_restore.

对于备份For the backup

  • 使用 -Fc 交换机进行备份,以便能够并行执行还原以提高速度。Take the backup with the -Fc switch so that you can perform the restore in parallel to speed it up. 例如:For example:

    pg_dump -h MySourceServerName -U MySourceUserName -Fc -d MySourceDatabaseName -f Z:\Data\Backups\MyDatabaseBackup.dump

对于还原For the restore

  • 我们建议将备份文件移动到你要迁移到的 Azure Database for PostgreSQL 服务器所在区域中的 Azure VM,并从该 VM 执行 pg_restore 以减少网络延迟。We suggest that you move the backup file to an Azure VM in the same region as the Azure Database for PostgreSQL server you are migrating to, and do the pg_restore from that VM to reduce network latency. 此外,我们还建议通过启用加速网络来创建 VM。We also recommend that the VM is created with accelerated networking enabled.

  • 默认情况下应该已经完成,但需打开转储文件来验证 create index 语句是否在插入数据之后。It should be already done by default, but open the dump file to verify that the create index statements are after the insert of the data. 如果不是这种情况,请将 create index 语句移动到插入的数据之后。If it isn't the case, move the create index statements after the data is inserted.

  • 使用 -Fc 和 -j # 交换机进行并行还原。Restore with the switches -Fc and -j # to parallelize the restore. # 是目标服务器上的内核数。# is the number of cores on the target server. 你还可以尝试将 # 设置为目标服务器内核数的两倍,以查看产生的影响。You can also try with # set to twice the number of cores of the target server to see the impact. 例如:For example:

    pg_restore -h MyTargetServer.postgres.database.chinacloudapi.cn -U MyAzurePostgreSQLUserName -Fc -j 4 -d MyTargetDatabase Z:\Data\Backups\MyDatabaseBackup.dump
  • 此外,还可以通过在开头添加 set synchronous_commit = off; 命令并在末尾添加 set synchronous_commit = on; 命令来编辑转储文件。You can also edit the dump file by adding the command set synchronous_commit = off; at the beginning and the command set synchronous_commit = on; at the end. 如果在应用更改数据之前未在末尾打开该功能,可能会导致随后的数据丢失。Not turning it on at the end, before the apps change the data, may result in subsequent loss of data.

  • 在目标 Azure Database for PostgreSQL 服务器上,请考虑在还原之前执行以下操作:On the target Azure Database for PostgreSQL server, consider doing the following before the restore:

    • 关闭查询性能跟踪,因为迁移期间不需要这些统计信息。Turn off query performance tracking, since these statistics are not needed during the migration. 可以通过将 pg_stat_statements.track、pg_qs.query_capture_mode 和 pgms_wait_sampling.query_capture_mode 设置为 NONE 来完成此操作。You can do this by setting pg_stat_statements.track, pg_qs.query_capture_mode, and pgms_wait_sampling.query_capture_mode to NONE.

    • 使用高计算和高内存 sku(如 32 vCore 内存优化)来加速迁移。Use a high compute and high memory sku, like 32 vCore Memory Optimized, to speed up the migration. 完成还原操作后,可以轻松缩回到所需的 sku。You can easily scale back down to your preferred sku after the restore is complete. sku 越高,通过增加 pg_restore 命令中相应的 -j 参数就可以实现越多的并行性。The higher the sku, the more parallelism you can achieve by increasing the corresponding -j parameter in the pg_restore command.

    • 通过增加目标服务器上的 IOPS 可以提高还原性能。More IOPS on the target server could improve the restore performance. 你可以通过增加服务器的存储大小来预配更多 IOPS。You can provision more IOPS by increasing the server's storage size. 此设置不可逆,但要考虑的一点是,更高的 IOPS 是否在将来有益于你的实际工作负荷。This setting is not reversible, but consider whether a higher IOPS would benefit your actual workload in the future.

请记住先在测试环境中测试和验证这些命令,然后再将其用于生产。Remember to test and validate these commands in a test environment before you use them in production.

后续步骤Next steps