Postgresql 中文操作指南

pg_upgrade

pg_upgrade — 升级 PostgreSQL 服务器实例

pg_upgrade — upgrade a PostgreSQL server instance

Synopsis

pg_upgrade -b oldbindir [ -B newbindir ] -d oldconfigdir -D newconfigdir [ option …​]

pg_upgrade -b oldbindir [-B newbindir] -d oldconfigdir -D newconfigdir [option…​]

Description

pg_upgrade（以前称为 pg_migrator）允许存储在 PostgreSQL 数据文件中的数据升级到 PostgreSQL 的更新主要版本，而无需通常需要的大版本升级的数据转储/还原，例如，从 12.14 升级到 13.10 或从 14.9 升级到 15.5。它不是较小版本的升级所必需的，例如，从 12.7 升级到 12.8 或从 14.1 升级到 14.5。

pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/restore typically required for major version upgrades, e.g., from 12.14 to 13.10 or from 14.9 to 15.5. It is not required for minor version upgrades, e.g., from 12.7 to 12.8 or from 14.1 to 14.5.

主要的 PostgreSQL 版本会定期添加新功能，这些功能通常会改变系统表的布局，但是内部数据存储格式很少改变。pg_upgrade 使用此事实通过创建新的系统表并简单重用旧用户数据文件来执行快速升级。如果将来的主要版本改变了数据存储格式，使得旧的数据格式不可读，那么 pg_upgrade 将无法用于这样的升级。（社区将设法避免这种情况。）

Major PostgreSQL releases regularly add new features that often change the layout of the system tables, but the internal data storage format rarely changes. pg_upgrade uses this fact to perform rapid upgrades by creating new system tables and simply reusing the old user data files. If a future major release ever changes the data storage format in a way that makes the old data format unreadable, pg_upgrade will not be usable for such upgrades. (The community will attempt to avoid such situations.)

pg_upgrade 会尽力确保旧集群和新集群是二进制兼容的，例如，通过检查兼容的编译时设置，包括 32/64 位二进制文件。重要的是，任何外部模块也必须是二进制兼容的，尽管 pg_upgrade 无法检查这一点。

pg_upgrade does its best to make sure the old and new clusters are binary-compatible, e.g., by checking for compatible compile-time settings, including 32/64-bit binaries. It is important that any external modules are also binary compatible, though this cannot be checked by pg_upgrade.

pg_upgrade 支持从 9.2.X 及更高的版本升级到 PostgreSQL 的当前主要版本，包括 snapshot 和 beta 版本。

pg_upgrade supports upgrades from 9.2.X and later to the current major release of PostgreSQL, including snapshot and beta releases.

Options

pg_upgrade 接受以下命令行参数：

pg_upgrade accepts the following command-line arguments:

  • -b bindir—​old-bindir=bindir

    • the old PostgreSQL executable directory; environment variable PGBINOLD

  • -B bindir—​new-bindir=bindir

    • the new PostgreSQL executable directory; default is the directory where pg_upgrade resides; environment variable PGBINNEW

  • -c_—​check_

    • check clusters only, don’t change any data

  • -d configdir—​old-datadir=configdir

    • the old database cluster configuration directory; environment variable PGDATAOLD

  • -D configdir—​new-datadir=configdir

    • the new database cluster configuration directory; environment variable PGDATANEW

  • -j _njobs—​jobs=_njobs

    • number of simultaneous processes or threads to use

  • -k_—​link_

    • use hard links instead of copying files to the new cluster

  • -N_—​no-sync_

    • By default, pg_upgrade will wait for all files of the upgraded cluster to be written safely to disk. This option causes pg_upgrade to return without waiting, which is faster, but means that a subsequent operating system crash can leave the data directory corrupt. Generally, this option is useful for testing but should not be used on a production installation.

  • -o options_—​old-options_ options

    • options to be passed directly to the old postgres command; multiple option invocations are appended

  • -O options_—​new-options_ options

    • options to be passed directly to the new postgres command; multiple option invocations are appended

  • -p port—​old-port=port

    • the old cluster port number; environment variable PGPORTOLD

  • -P port—​new-port=port

    • the new cluster port number; environment variable PGPORTNEW

  • -r_—​retain_

    • retain SQL and log files even after successful completion

  • -s dir—​socketdir=dir

    • directory to use for postmaster sockets during upgrade; default is current working directory; environment variable PGSOCKETDIR

  • -U username—​username=username

    • cluster’s install user name; environment variable PGUSER

  • -v_—​verbose_

    • enable verbose internal logging

  • -V_—​version_

    • display version information, then exit

  • —​clone

    • Use efficient file cloning (also known as “reflinks” on some systems) instead of copying files to the new cluster. This can result in near-instantaneous copying of the data files, giving the speed advantages of -k/—​link while leaving the old cluster untouched.

    • File cloning is only supported on some operating systems and file systems. If it is selected but not supported, the pg_upgrade run will error. At present, it is supported on Linux (kernel 4.5 or later) with Btrfs and XFS (on file systems created with reflink support), and on macOS with APFS.

  • —​copy

    • Copy files to the new cluster. This is the default. (See also —​link and —​clone.)

  • -?_—​help_

    • show help, then exit

Usage

下面是使用 pg_upgrade 执行升级的步骤：

These are the steps to perform an upgrade with pg_upgrade:

Caution

通常，在重建脚本运行完成之前，访问重建脚本中引用的表是不安全的；这样做可能会产生不正确的结果或降低性能。未在重建脚本中引用的表可以立即访问。

In general it is unsafe to access tables referenced in rebuild scripts until the rebuild scripts have run to completion; doing so could yield incorrect results or poor performance. Tables not referenced in rebuild scripts can be accessed immediately.

Notes

pg_upgrade 创建各种工作文件（如模式转储），存储在 @ pg_upgrade_output.d @@ 中新集群的目录中。每次运行都会根据 ISO 8601（@ %Y%m%dT%H%M%S @@）格式化的时间戳建立一个新子目录，其所有生成的文件都存储在其中。@ pg_upgrade_output.d @@ 及其包含的文件将在 pg_upgrade 成功完成时自动删除；但在出现问题的情况下，其文件可能提供有用的调试信息。

pg_upgrade creates various working files, such as schema dumps, stored within pg_upgrade_output.d in the directory of the new cluster. Each run creates a new subdirectory named with a timestamp formatted as per ISO 8601 (%Y%m%dT%H%M%S), where all its generated files are stored. pg_upgrade_output.d and its contained files will be removed automatically if pg_upgrade completes successfully; but in the event of trouble, the files there may provide useful debugging information.

pg_upgrade 在旧数据目录和新数据目录中启动短暂的后备服务器。默认情况下，用于与这些后备服务器通信的临时 Unix 套接字文件位于当前工作目录中。在某些情况下，当前目录的路径名称可能太长而无法成为有效的套接字名称。在此情况下，您可以使用 @ -s @@ 选项将套接字文件放在路径名称较短的某个目录中。为了安全起见，请确保其他用户无法读取或写入该目录。（Windows 不支持此操作。）

pg_upgrade launches short-lived postmasters in the old and new data directories. Temporary Unix socket files for communication with these postmasters are, by default, made in the current working directory. In some situations the path name for the current directory might be too long to be a valid socket name. In that case you can use the -s option to put the socket files in some directory with a shorter path name. For security, be sure that that directory is not readable or writable by any other users. (This is not supported on Windows.)

如果 pg_upgrade 影响到您的安装，它将报告所有故障、重建和重新索引情况；重建表和索引的后升级脚本会自动生成。如果您尝试自动升级多个集群，您应当发现具有相同数据库模式的集群需要相同的后升级步骤来进行所有集群升级；这是因为后升级步骤基于数据库模式，而不是用户数据。

All failure, rebuild, and reindex cases will be reported by pg_upgrade if they affect your installation; post-upgrade scripts to rebuild tables and indexes will be generated automatically. If you are trying to automate the upgrade of many clusters, you should find that clusters with identical database schemas require the same post-upgrade steps for all cluster upgrades; this is because the post-upgrade steps are based on the database schemas, and not user data.

对于部署测试，创建旧集群的仅模式副本，插入虚拟数据，然后升级副本。

For deployment testing, create a schema-only copy of the old cluster, insert dummy data, and upgrade that.

pg_upgrade 不支持使用以下 @ reg* @@ OID 引用系统数据类型的表列升级数据库：

pg_upgrade does not support upgrading of databases containing table columns using these reg* OID-referencing system data types:

（@ regclass @@、@ regrole @@ 和 @ regtype @@ 可以升级。）

(regclass, regrole, and regtype can be upgraded.)

如果您想使用链接模式，并且希望在新集群启动时旧集群不被修改，请考虑使用克隆模式。如果有，复制旧集群并以链接模式升级。若要创建旧集群的有效副本，请使用 rsync 创建旧集群的脏副本，同时服务器正在运行，然后关闭旧服务器并再次运行 rsync --checksum 更新副本，进行任何更改，使其一致。（ —​checksum 是必要的，因为 rsync 只有文件修改时间精确度为一秒。）您可能需要排除一些文件，例如 postmaster.pid ，如 Section 26.3.3 所述。如果您的文件系统支持文件系统快照或按需写入文件副本，您可以使用该文件制作旧集群和表空间的备份，尽管快照和副本必须同时创建或在数据库服务器关闭时创建。

If you want to use link mode and you do not want your old cluster to be modified when the new cluster is started, consider using the clone mode. If that is not available, make a copy of the old cluster and upgrade that in link mode. To make a valid copy of the old cluster, use rsync to create a dirty copy of the old cluster while the server is running, then shut down the old server and run rsync --checksum again to update the copy with any changes to make it consistent. (—​checksum is necessary because rsync only has file modification-time granularity of one second.) You might want to exclude some files, e.g., postmaster.pid, as documented in Section 26.3.3. If your file system supports file system snapshots or copy-on-write file copies, you can use that to make a backup of the old cluster and tablespaces, though the snapshot and copies must be created simultaneously or while the database server is down.

See Also

initdb ， pg_ctl ， pg_dump ， postgres

initdb, pg_ctl, pg_dump, postgres