Postgresql 中文操作指南

19.6. Upgrading a PostgreSQL Cluster #

本节讨论如何将数据库数据从一个 PostgreSQL 版本升级到新版本。

This section discusses how to upgrade your database data from one PostgreSQL release to a newer one.

当前的 PostgreSQL 版本号由主版本号和次版本号组成。例如，在版本号 10.1 中，10 是主版本号，1 是次版本号，这意味着这将是主版本 10 的第一个次版本。对于 PostgreSQL 10.0 版本之前的版本，版本号由三个数字组成，例如，9.5.3。在这些情况下，主版本由版本号的前两个数字组组成，例如，9.5，次版本是第三个数字，例如，3，这意味着这将是主版本 9.5 的第三个次版本。

Current PostgreSQL version numbers consist of a major and a minor version number. For example, in the version number 10.1, the 10 is the major version number and the 1 is the minor version number, meaning this would be the first minor release of the major release 10. For releases before PostgreSQL version 10.0, version numbers consist of three numbers, for example, 9.5.3. In those cases, the major version consists of the first two digit groups of the version number, e.g., 9.5, and the minor version is the third number, e.g., 3, meaning this would be the third minor release of the major release 9.5.

次版本绝不改变内部存储格式，而且始终与同一主版本号的较早次版本和较晚次版本兼容。例如，版本 10.1 与版本 10.0 和版本 10.6 兼容。同样，例如，9.5.3 与 9.5.0、9.5.1 和 9.5.6 兼容。要在兼容版本之间进行更新，只需在服务器关闭时替换可执行文件并重新启动服务器。数据目录保持不变——次级升级就是这么简单。

Minor releases never change the internal storage format and are always compatible with earlier and later minor releases of the same major version number. For example, version 10.1 is compatible with version 10.0 and version 10.6. Similarly, for example, 9.5.3 is compatible with 9.5.0, 9.5.1, and 9.5.6. To update between compatible versions, you simply replace the executables while the server is down and restart the server. The data directory remains unchanged — minor upgrades are that simple.

对于 major PostreSQL 发行版，内部数据存储格式会发生更改，因此会使升级复杂化。将数据迁移到新主要版本中的传统方法是对数据库进行转储和还原，尽管这可能很慢。一种较快的方法是 pg_upgrade 。还可以使用复制方法，如下所述。（如果你正在使用 PostgreSQL 的预打包版本，它可能会提供脚本来帮助进行主要版本升级。请参阅包级别文档以获取详细信息。）

For major releases of PostgreSQL, the internal data storage format is subject to change, thus complicating upgrades. The traditional method for moving data to a new major version is to dump and restore the database, though this can be slow. A faster method is pg_upgrade. Replication methods are also available, as discussed below. (If you are using a pre-packaged version of PostgreSQL, it may provide scripts to assist with major version upgrades. Consult the package-level documentation for details.)

新的主要版本通常还引入了一些用户可见的不兼容性，因此可能需要应用程序编程更改。所有用户可见的更改都列在发行说明中 ( Appendix E)；特别注意标记为“迁移”的部分。虽然您可以从一个主要版本升级到另一个主要版本而不升级到中间版本，但您应该阅读所有中间版本的主要发行说明。

New major versions also typically introduce some user-visible incompatibilities, so application programming changes might be required. All user-visible changes are listed in the release notes (Appendix E); pay particular attention to the section labeled "Migration". Though you can upgrade from one major version to another without upgrading to intervening versions, you should read the major release notes of all intervening versions.

谨慎的用户会希望在新版本上测试他们的客户端应用程序，然后再完全切换，因此，同时安装旧版本和新版本通常是一个好主意。在测试 PostgreSQL 主升级时，请考虑以下可能的更改类别：

Cautious users will want to test their client applications on the new version before switching over fully; therefore, it’s often a good idea to set up concurrent installations of old and new versions. When testing a PostgreSQL major upgrade, consider the following categories of possible changes:

  • Administration

    • The capabilities available for administrators to monitor and control the server often change and improve in each major release.

  • SQL

    • Typically this includes new SQL command capabilities and not changes in behavior, unless specifically mentioned in the release notes.

  • Library API

    • Typically libraries like libpq only add new functionality, again unless mentioned in the release notes.

  • System Catalogs

    • System catalog changes usually only affect database management tools.

  • Server C-language API

    • This involves changes in the backend function API, which is written in the C programming language. Such changes affect code that references backend functions deep inside the server.

19.6.1. Upgrading Data via pg_dumpall #

一种升级方法是从 PostgreSQL 的一个主版本转储数据并在另一个主版本中还原数据——为此，您必须使用 logical 备份工具（例如 pg_dumpall）；文件系统级别的备份方法将不起作用。（已实施一些检查来防止您使用与 PostgreSQL 版本不兼容的数据目录，因此尝试对数据目录启动错误的服务器版本不会造成太大伤害。）

One upgrade method is to dump data from one major version of PostgreSQL and restore it in another — to do this, you must use a logical backup tool like pg_dumpall; file system level backup methods will not work. (There are checks in place that prevent you from using a data directory with an incompatible version of PostgreSQL, so no great harm can be done by trying to start the wrong server version on a data directory.)

建议您使用 newer 版本的 PostgreSQL 中的 pg_dump 和 pg_dumpall 程序，以利用这些程序中可能已进行的增强。转储程序的当前版本可以读取从 9.2 开始的任何服务器版本的数据。

It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL, to take advantage of enhancements that might have been made in these programs. Current releases of the dump programs can read data from any server version back to 9.2.

这些说明假设您的现有安装位于 /usr/local/pgsql 目录下，并且数据区域位于 /usr/local/pgsql/data。请适当地替换您的路径。

These instructions assume that your existing installation is under the /usr/local/pgsql directory, and that the data area is in /usr/local/pgsql/data. Substitute your paths appropriately.

在不同的目录中安装新服务器，并在不同的端口上并行运行旧服务器和新服务器，可以将停机时间缩至最短。然后你可以使用以下命令：

The least downtime can be achieved by installing the new server in a different directory and running both the old and the new servers in parallel, on different ports. Then you can use something like:

pg_dumpall -p 5432 | psql -d postgres -p 5433

来传输你的数据。

to transfer your data.

19.6.2. Upgrading Data via pg_upgrade #

pg_upgrade 模块允许将安装从一个主要 PostgreSQL 版本就地迁移到另一个版本。升级可以在几分钟内进行，特别是在 —​link 模式下。它需要与上述 pg_dumpall 类似的步骤，例如，启动/停止服务器、运行 initdb。pg_upgrade documentation 概述了必要步骤。

The pg_upgrade module allows an installation to be migrated in-place from one major PostgreSQL version to another. Upgrades can be performed in minutes, particularly with —​link mode. It requires steps similar to pg_dumpall above, e.g., starting/stopping the server, running initdb. The pg_upgrade documentation outlines the necessary steps.

19.6.3. Upgrading Data via Replication #

还可以使用逻辑复制方法来创建具有更新版本 PostgreSQL 的备用服务器。这是可行的，因为逻辑复制支持 PostgreSQL 不同主要版本之间的复制。备用服务器可以在同一台计算机上或在不同的计算机上。一旦它与运行 PostgreSQL 旧版本的（主）服务器同步，就可以切换主服务器并使备用服务器成为主服务器，并关闭旧数据库实例。这种切换只导致升级过程的停机时间达到数秒。

It is also possible to use logical replication methods to create a standby server with the updated version of PostgreSQL. This is possible because logical replication supports replication between different major versions of PostgreSQL. The standby can be on the same computer or a different computer. Once it has synced up with the primary server (running the older version of PostgreSQL), you can switch primaries and make the standby the primary and shut down the older database instance. Such a switch-over results in only several seconds of downtime for an upgrade.

这种升级方法可以使用内置逻辑复制工具以及外部逻辑复制系统来执行，例如 pglogical、Slony、Londiste 和 Bucardo。

This method of upgrading can be performed using the built-in logical replication facilities as well as using external logical replication systems such as pglogical, Slony, Londiste, and Bucardo.