PostgreSQL 教程: 在线升级

三月 11, 2025

摘要：在本教程中，您将学习如何实现在线升级 PostgreSQL 大版本。

介绍

PostgreSQL 提供了逻辑复制的能力，通过逻辑复制可以实现近乎零停机的在线迁移和升级，并且可以应用在如下这些场景：

升级数据库软件的版本；
升级操作系统软件的版本，或者变更操作系统类型，比如从 Windows 变更成 Linux；
升级硬件的规格，或者变更硬件的架构，比如从 X86 变更成 ARM；
调整数据库部署架构，比如将一个大的数据库拆分成多个小的数据库；
迁移数据库部署环境，比如从某一个公有云的服务器迁移到另一个公有云上面；

如果您的数据库很大，则可能需要一些时间来通过网络进行传输。另外，基于逻辑复制的在线迁移只支持以数据库为单位或以表为单位的迁移。我们将在下面的步骤中介绍以数据库为单位的迁移。在本节中，我们将对 lrtest 数据库进行在线迁移。

初始检查

如果您还没有为您计划复制的每个表使用主键，请创建主键。下面的查询会显示出哪些表没有主键。在您开始迁移之前查询应该显示为 0 个表。

SELECT n.nspname AS schema, c.relname AS table
  FROM pg_class c JOIN pg_namespace n
    ON n.oid = c.relnamespace
  WHERE c.relkind = 'r'
    AND NOT EXISTS (
      SELECT 1 FROM pg_constraint con
        WHERE con.conrelid = c.oid AND con.contype = 'p')
    AND n.nspname NOT IN ('pg_catalog', 'information_schema');

如果您在此处未获得 0 行的值，您需要在查询出来的表上创建主键来进行表的复制。

配置修改

一个需要注意的重要事项是，如果您已针对内存、最大连接数等调整了 postgresql.conf，则这些参数不会在逻辑复制过程中复制。在迁移之前，请在原数据库和目标数据库上分别执行下面的查询，并对查询结果进行细致的对比。

SELECT name, setting, unit, boot_val FROM pg_settings;

其中，视图 pg_settings 提供了数据库服务器运行时的参数信息。如果您在参数对比过程中，发现原数据库和目标数据库存在有差异的参数值，您可能需要在目标数据库修改对应的参数配置，并重启目标数据库，以便让配置更改生效。

由于逻辑复制需要复制用户，我们将在原数据库上执行下面的查询，创建一个具有复制权限的用户。

CREATE USER logicalrep REPLICATION PASSWORD 'pgpass';

在创建完复制用户后，我们需要更改原数据库上的pg_hba.conf配置文件，以允许目标数据库连接到原数据库上复制数据。由于数据库服务器是从上往下读取pg_hba.conf的，这意味着您需要根据配置规则适当地放置这些行：

host   lrtest  logicalrep  192.168.1.51/32  md5

在更改完pg_hba.conf后，我们需要将原数据库的wal_level参数设置为logical。

ALTER SYSTEM SET wal_level TO logical;

在我们更改完上面的配置后，我们需要重新启动原数据库，以便让配置更改生效。

迁移架构

对于要迁移的原数据库，使用 pg_dump 进行架构转储。这将允许我们在目标数据库恢复架构信息。

pg_dump -Fc -s -h 192.168.1.50 -d lrtest -U postgres -f /tmp/lrtest_schema.dmp

使用 pg_restore 将架构转储导入到目标数据库。

pg_restore -C -h 192.168.1.51 -d lrtest -U postgres /tmp/lrtest_schema.dmp

注意：在 PostgreSQL 逻辑复制的过程中，更改原数据库上的架构信息，无法同步到目标数据库。要实现这一点，请参考 Redrock Postgres 的解决方案：订阅 DDL 操作。

迁移表数据

现在架构信息已经导入到目标数据库了，我们可以通过在原数据库上运行以下命令来创建发布。如上所述，我们即将迁移数据库中的所有表。如果您只想复制数据库中某些表，您可以在使用 CREATE PUBLICATION 命令时传递 FOR TABLE。

CREATE PUBLICATION lrtest_migrate FOR ALL TABLES;

在目标数据库上运行下面的命令，订阅原数据库上面所有的表数据和更改：

CREATE SUBSCRIPTION lrtest_migrate CONNECTION
  'host=192.168.1.50 dbname=lrtest user=logicalrep password=pgpass'
  PUBLICATION lrtest_migrate;

数据校验

接下来，我们需要验证原数据库和目标数据库的数据是否一致。在原数据库和目标数据库上，运行下面的命令，创建用于计算表数据校验和的聚集函数。

CREATE FUNCTION md5_agg_sfunc(text, text) RETURNS text
  AS 'SELECT md5($1 || $2)' LANGUAGE sql;

CREATE AGGREGATE md5_agg (
  BASETYPE = text,
  STYPE = text,
  SFUNC = md5_agg_sfunc,
  INITCOND = ''
);

然后，对于数据库中的每个表，我们可以在原数据库和目标数据库上分别执行下面的查询，并确认两边查询的结果是一致的。

SELECT md5_agg(t::text) AS checksum FROM
  (SELECT * FROM t_table ORDER BY table_key) AS t;

如果发现原数据库和目标数据库存在有差异的数据表，可以在两个数据库上分别执行下面的查询，对表中的数据进行分批的校验。

SELECT md5_agg(t::text) AS checksum, max(table_key) AS maxval FROM (
  SELECT * FROM t_table
    WHERE table_key > key_val
    ORDER BY table_key LIMIT 100000
) AS t;

注意：在上面的查询中，table_key为表t_table中的主键字段，key_val为主键列值。在开始第一次查询时，请将key_val设置为小于表中的最小主键列值，并在接下来的分批校验过程中，将key_val设置为上一步查询返回的最大主键列值。

清理

原数据库中的数据已成功复制到目标数据库。我们甚至更进一步，确认了原数据库和目标数据库数据是一致的。此时逻辑复制正在运行，您可以将其保留在原位，直到您准备好进行切换。如果您的所有数据都已复制，并且您已经完成了转换，您可以删除订阅。

DROP SUBSCRIPTION lrtest_migrate;

现在在线迁移过程已成功完成，如果您不再需要原数据库，我们可以将其移除。

了解更多

PostgreSQL 管理

介绍