使用转储和还原升级 PostgreSQL 数据库Upgrade your PostgreSQL database using dump and restore

通过使用以下方法将数据库迁移到主版本更高的服务器,可以升级部署在 Azure Database for PostgreSQL - 单一服务器中的 PostgreSQL 服务器。You can upgrade your PostgreSQL server deployed in Azure Database for PostgreSQL - Single Server by migrating your databases to a higher major version server using following methods.

  • 使用 PostgreSQL pg_dumppg_restore脱机 方法,这种方法会导致迁移数据时停机。Offline method using PostgreSQL pg_dump and pg_restore which incurs downtime for migrating the data. 本文档介绍这种升级/迁移方法。This document addresses this method of upgrade/migration.
  • 使用 数据库迁移服务 (DMS) 的 联机 方法。Online method using Database Migration Service (DMS). 这种方法减少了迁移时的停机时间,使目标数据库与源保持同步,并且可以选择何时进行切换。This method provides a reduced downtime migration and keeps the target database in-sync with with the source and you can choose when to cut-over. 但是,使用 DMS 需要满足一些先决条件和限制。However, there are few prerequisites and restrictions to be addressed for using DMS. 有关详细信息,请参阅 DMS 文档For details, see the DMS documentation.

下表根据数据库大小和方案提供了一些建议。The following table provides some recommendations based on database sizes and scenarios.

数据库/方案Database/Scenario 转储/还原(脱机)Dump/restore (Offline) DMS(联机)DMS (Online)
你的数据库很小,可以承受升级时停机You have a small database and can afford downtime to upgrade XX
小型数据库 (< 10 GB)Small databases (< 10 GB) XX XX
中小型数据库 (10 GB - 100 GB)Small-medium DBs (10 GB – 100 GB) XX XX
大型数据库 (> 100 GB)Large databases (> 100 GB) XX
可以承受升级时停机(与数据库大小无关)Can afford downtime to upgrade (irrespective of the database size) XX
能否满足 DMS 先决条件,包括重启?Can address DMS pre-requisites, including a reboot? XX
能否在升级过程中避开 DDL 和未记录的表?Can avoid DDLs and unlogged tables during the upgrade process? XX

本指南提供了一些脱机迁移方法和示例,说明如何从源服务器迁移到运行更高版本 PostgreSQL 的目标服务器。This guide provides few offline migration methodologies and examples to show how you can migrate from your source server to the target server that runs a higher version of PostgreSQL.


可以通过多种方式执行 PostgreSQL 转储和还原。PostgreSQL dump and restore can be performed in many ways. 你可以选择使用本指南提供的方法之一进行迁移,也可以根据自己的需求选择任何替代方法。You may choose to migrate using one of the methods provided in this guide or choose any alternate ways to suit your needs. 有关包含附加参数的详细转储和还原语法,请参阅文章 pg_dumppg_restoreFor detailed dump and restore syntax with additional parameters, see the articles pg_dump and pg_restore.

将转储和还原与 Azure Database for PostgreSQL 配合使用的先决条件Prerequisites for using dump and restore with Azure Database for PostgreSQL

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

  • 运行要升级的 9.5、9.6 或 10 的源 PostgreSQL 数据库A source PostgreSQL database running 9.5, 9.6, or 10 which you want to upgrade
  • 具有所需主版本 Azure Database for PostgreSQL 服务器的目标 PostgreSQL 数据库服务器。A target PostgreSQL database server with the desired major version Azure Database for PostgreSQL server.
  • PostgreSQL 客户端系统,用于运行转储和还原命令。A PostgreSQL client system to run the dump and restore commands.
    • 它可以是安装了 PostgreSQL 以及 pg_dumppg_restore 命令行实用程序的 Linux 或 Windows 客户端。It can be a Linux or Windows client with PostgreSQL installed and has pg_dump and pg_restore command-line utilities installed.
  • PostgreSQL 客户端最好在与源服务器和目标服务器相同的区域中运行。Your PostgreSQL client preferably running in the same region as the source and target servers.

其他详细信息和注意事项Additional details and considerations

  • 通过单击门户中的“连接字符串”,可以找到源数据库和目标数据库的连接字符串。You can find the connection string to the source and target databases by clicking the "Connection Strings" from the portal.
  • 你可能正在服务器中运行多个数据库。You may be running more than one database in your server. 可以通过连接到源服务器并运行 \l 来查找数据库列表。You can find the list of databases by connecting to your source server and running \l.
  • 在目标数据库服务器中创建相应的数据库。Create corresponding databases in the target database server.
  • 可以跳过 azure_maintenance 或模板数据库的升级。You can skip upgrading azure_maintenance or template databases.
  • 请参阅上面的表,确定数据库是否适合这种迁移模式。Refer to the tables above to determine the database is suitable for this mode of migration.
  • 如果要使用 Azure CLI,请注意会话会在 20 分钟后超时。If you want to use Azure Cli, please note that the session times out after 20 minutes. 如果数据库大小小于 10 GB,则有可能在会话不超时的情况下完成升级。否则,可能必须通过其他方法(例如每 10-15 分钟按一次 键)使会话保持打开状态。If your database size is < 10 GB, you may be able to complete the upgrade without the session timing out. Otherwise, you may have to keep the session open by other means, such as pressing key once in 10-15 minutes.

本指南中使用的示例数据库Example database used in this guide

本指南使用以下源服务器和目标服务器以及数据库名称进行举例说明。In this guide, the following source and target servers and database names are used to illustrate with examples.

说明Description Value
源服务器 (v9.5)Source server (v9.5) pg-95.postgres.database.chinacloudapi.cnpg-95.postgres.database.chinacloudapi.cn
源数据库Source database bench5gbbench5gb
源数据库大小Source database size 5 GB5 GB
源用户名Source user name pg@pg-95
目标服务器 (v11)Target server (v11) pg-11.postgres.database.chinacloudapi.cnpg-11.postgres.database.chinacloudapi.cn
目标数据库Target database bench5gbbench5gb
目标用户名Target user name pg@pg-11

使用脱机迁移方法升级数据库Upgrade your databases using offline migration methods

可以选择使用此部分中所述的方法之一进行升级。You may choose to use one of the methods described in this section for your upgrades. 执行任务时,可以使用以下提示。You can use the following tips while performing the tasks.

  • 如果对源数据库和目标数据库使用相同的密码,则可以设置 PGPASSWORD=yourPassword 环境变量。If you are using the same password for source and the target database, you can set the PGPASSWORD=yourPassword environment variable. 这样就不必在每次运行命令(如 psql、pg_dump 和 pg_restore)时都提供密码。Then you don't have to provide password every time you run commands like psql, pg_dump, and pg_restore. 同样,也可以设置 PGUSERPGSSLMODE 等其他变量。请参阅 PostgreSQL 环境变量Similarly you can setup additional variables like PGUSER, PGSSLMODE etc. see to PostgreSQL environment variables.

  • 如果 PostgreSQL 服务器需要 TLS/SSL 连接(默认情况下在 Azure Database for PostgreSQL 服务器中启用),请设置环境变量 PGSSLMODE=require,以便 pg_restore 工具使用 TLS 进行连接。If your PostgreSQL server requires TLS/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 TLS. 如果不使用 TLS,错误可能会显示为 FATAL: SSL connection is required. Please specify SSL options and retry.Without TLS, 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.

方法 1:使用转储文件进行迁移Method 1: Migrate using dump file

此方法涉及两个步骤。This method involves two steps. 第一步是从源服务器创建转储。First is to create a dump from the source server. 第二步是将转储文件还原到目标服务器。The second step is to restore the dump file to the target server. 有关更多详细信息,请参阅使用转储和还原进行迁移文档。More details, please see the Migrate using dump and restore documentation. 如果你的数据库较大,并且客户端系统具有足够的存储空间来存储转储文件,则建议使用此方法。This is the recommended method if you have large databases and your client system has enough storage to store the dump file.

方法 2:使用将转储数据流式传输到目标数据库的方法进行迁移Method 2: Migrate using streaming the dump data to the target database

数据库转储直接流式传输到目标数据库服务器,并且不将转储存储在客户端中。The database dump is streamed directly to the target database server and does not store the dump in the client. 因此,这种方法可用于存储空间有限的客户端。Hence, this can be used with a client with limited storage.

  1. 使用 \l 命令确保目标服务器中存在数据库。Make sure the database exists in the target server using \l command. 如果数据库不存在,则创建数据库。If the database does not exist, then create the database.

     psql "host=myTargetServer port=5432 dbname=postgres user=myUser password=###### sslmode=mySSLmode"
    postgres> \l   
    postgres> create database myTargetDB;
  2. 使用管道将转储和还原作为单个命令行运行。Run the dump and restore as a single command line using a pipe.

    pg_dump -Fc -v --mySourceServer --port=5432 --username=myUser --dbname=mySourceDB | pg_restore -v --no-owner --host=myTargetServer --port=5432 --username=myUser --dbname=myTargetDB

    例如,应用于对象的For example,

    pg_dump -Fc -v --host=pg-95.postgres.database.chinacloudapi.cn --port=5432 --username=pg@pg-95 --dbname=bench5gb | pg_restore -v --no-owner --host=pg-11.postgres.database.chinacloudapi.cn --port=5432 --username=pg@pg-11 --dbname=bench5gb
  3. 升级(迁移)过程完成后,即可使用目标服务器测试应用程序。Once the upgrade (migration) process completes, you can test your application with the target server.

  4. 对服务器内的所有数据库重复此过程。Repeat this process for all the databases within the server.

例如,下表说明了使用流式传输转储方法进行迁移所需的时间。As an example, the following table illustrates time it took to migrate using streaming dump method. 系统使用 pgbench 填充示例数据。The sample data is populated using pgbench. 与 pgbench 生成的表和索引相比,你的数据库可能具有不同数量的对象且大小各异,因此强烈建议测试数据库的转储和还原,以了解升级数据库所需的实际时间。As your database can have different number of objects with varied sizes than pgbench generated tables and indexes, it is highly recommended to test dump and restore of your database to understand the actual time it takes to upgrade your database.

数据库大小Database Size 大约需要的时间Approx. time taken
1 GB1 GB 1-2 分钟1-2 minutes
5 GB5 GB 8-10 分钟8-10 minutes
10 GB10 GB 15-20 分钟15-20 minutes
50 GB50 GB 1-1.5 小时1-1.5 hours
100 GB100 GB 2.5-3 小时2.5-3 hours

方法 3:使用并行转储和还原进行迁移Method 3: Migrate using parallel dump and restore

如果数据库中有几个较大的表,你想对该数据库并行执行转储和还原过程,则可以考虑使用此方法。You can consider this method if you have few larger tables in your database and you want to parallelize the dump and restore process for that database. 客户端系统还需要有足够的存储空间来容纳备份转储。You also need enough storage in your client system to accommodate backup dumps. 此并行转储和还原过程减少了完成整个迁移所需的时间。This parallel dump and restore process reduces the time consumption to complete the whole migration. 例如,使用方法 1 和 2 完成 50 GB pgbench 数据库的迁移需要 1-1.5 个小时,而使用此方法所需的时间不到 30 分钟。For example, the 50 GB pgbench database which took 1-1.5 hrs to migrate was completed using Method 1 and 2 took less than 30 minutes using this method.

  1. 对于源服务器中的每个数据库,在目标服务器上创建一个对应的数据库。For each database in your source server, create a corresponding database at the target server.

     psql "host=myTargetServer port=5432 dbname=postgres user=myuser password=###### sslmode=mySSLmode"
     postgresl> create database myDB;

    例如,应用于对象的For example,

    psql "host=pg-11.postgres.database.chinacloudapi.cn port=5432 dbname=postgres user=pg@pg-11 password=###### sslmode=require"
    postgres> create database bench5gb;
    postgres> \q
  2. 以目录格式运行 pg_dump 命令,其中作业数 = 4(数据库中的表数)。Run the pg_dump command in a directory format with number of jobs = 4 (number of tables in the database). 对于更大的计算层和更多的表,可以将其增加到更大的数目。With larger compute tier and with more tables, you can increase it to a higher number. 该 pg_dump 将创建一个目录,用于存储每个作业的压缩文件。That pg_dump will create a directory to store compressed files for each job.

    pg_dump -Fd -v --host=sourceServer --port=5432 --username=myUser --dbname=mySourceDB -j 4 -f myDumpDirectory

    例如,应用于对象的For example,

    pg_dump -Fd -v --host=pg-95.postgres.database.chinacloudapi.cn --port=5432 --username=pg@pg-95 --dbname=bench5gb -j 4 -f dump.dir
  3. 然后在目标服务器上还原备份。Then restore the backup at the target server.

    $ pg_restore -v --no-owner --host=myTargetServer --port=5432 --username=myUser --dbname=myTargetDB -j 4 myDumpDir

    例如,应用于对象的For example,

    $ pg_restore -v --no-owner --host=pg-11.postgres.database.chinacloudapi.cn --port=5432 --username=pg@pg-11 --dbname=bench5gb -j 4 dump.dir

后续步骤Next steps

  • 对目标数据库功能满意后,可以删除旧的数据库服务器。After you're satisfied with the target database function, you can drop your old database server.
  • 如果要使用与源服务器相同的数据库终结点,则在删除旧的源数据库服务器后,可以使用旧的数据库服务器名称创建只读副本。If you want to use the same database endpoint as the source server, then after you had deleted your old source database server, you can create a read replica with the old database server name. 建立稳定状态后,可以停止副本,这会将副本服务器提升为独立服务器。Once the steady state is established, you can stop the replica, which will promote the replica server to be an independent server. 有关更多详细信息,请参阅复制See Replication for more details.
  • 请记住先在测试环境中测试和验证这些命令,然后再将其用于生产。Remember to test and validate these commands in a test environment before you use them in production.