PostgreSQL 教程: 设置逻辑复制

十月 19, 2023

摘要:PostgreSQL 10 中引入了内置的逻辑复制逻辑解码。一段时间以来,逻辑复制中实现了越来越多的功能。当需要选定一些表从多个 OLTP 数据库复制到单个 OLAP 数据库以用于报告和分析时,此功能非常有用。

逻辑复制还可以用于,在两个主要的 PostgreSQL 版本之间执行复制,以进行滚动式升级。在本教程中,我们将讨论在两个 PostgreSQL 服务器之间设置逻辑复制所涉及的步骤。

准备工作

逻辑复制使用发布和订阅模型。发送更改的节点成为发布者,订阅这些更改的节点成为订阅者。一个发布可以有一个或多个订阅。我们可以选择要复制的内容 – INSERT、DELETE、UPDATE 或 ALL。默认情况下,它是 ALL。

强烈建议,只对具有主键列的表,或带有唯一键(且列值不为空值)的表,启用逻辑复制。可以将这些列设置为副本标识。特别是在更新或删除的情况下,副本标识将用于一条记录的唯一标识。如果不满足该要求,我们可以使用 FULL 形式的副本标识。这意味着整个记录将用作键值,但效率可能较低。

操作步骤

我们将使用以下步骤启动逻辑复制:

1. 启用所需参数:

$ psql -c "ALTER SYSTEM SET wal_level TO 'logical'"

2. 重启 PostgreSQL:

$ pg_ctl -D $PGDATA restart -mf

3. 添加所有表或多张表进行复制:

$ psql -d redrock -c "CREATE PUBLICATION rockpub FOR ALL TABLES"

或者

$ psql -d redrock -c "CREATE PUBLICATION rockpub FOR TABLE scott.employee scott.departments"

4. 将数据架构从发布端复制到订阅端:

$ pg_dump -h publisher_server_ip -p 5432 -d redrock -Fc -s -U postgres | pg_restore -d redrock -h subscriber_node_ip -p 5432 -U postgres

5. 创建订阅:

$ psql -d redrock -c "CREATE SUBSCRIPTION rocksub CONNECTION 'host=publisher_server_ip dbname=redrock user=postgres password=secret port=5432' PUBLICATION rockpub"

需要避免复制已存在的数据时,可以这样:

$ psql -d redrock -c "CREATE SUBSCRIPTION rocksub CONNECTION 'host=publisher_server_ip dbname=redrock user=postgres password=oracle port=5432' PUBLICATION rockpub WITH (copy_data = false)"

怎么做到的

设置逻辑复制与设置流复制一样简单,但需要一些额外的步骤或验证(有时)。如步骤 1 所示,我们必须确保两台 PostgreSQL 服务器均已将wal_level设置为 logical。如果需要更改它,则需要重新启动,可以使用步骤 2 中的命令执行此操作。

一旦wal_level的更改生效,我们就可以在主服务器(即发布者)上创建一个发布。如果仔细观察步骤 3,第一个命令将显示如何对redrock数据库中的所有表启用复制。但是,如果您需要添加一个或多个表,则可以使用同一步骤中的下一个命令。

一旦我们在主服务器上创建了发布,我们需要将要同步的表的结构,复制到订阅服务器或备用服务器。这可以使用步骤 4 中的命令来实现。此命令使用了-s执行pg_dump,它通过 PIPE 仅将数据架构复制到订阅端。这可以在发布者或订阅者上执行。

在复制数据架构后,我们可以简单地创建订阅,来为我们完成所有的工作。在创建订阅时,我们指定了订阅的名称,以及发布者的连接字符串和发布的名称。

如步骤 5 所示,有两种创建订阅的方法。第一个是通过复制所有已存在的数据,并在复制所有数据后开始复制。第二个方法避免了复制已存在的数据,而是从创建订阅时开始复制。

当我们想要使用多个进程,将已存在的数据从发布者并行复制和转储到订阅者时,第二种方法非常有用。但是,由于数据复制需要保证一致,因此会涉及一些停机时间。第一种方法可能不需要任何停机时间,因为它会负责复制已存在的数据,并在适当时启动复制,但它们不能并行执行。因此,第一种方法可能应该受到推荐,但对于大型 PostgreSQL 实例来说可能会更慢。执行步骤 5 后,发布者和订阅者之间的逻辑复制设置就完成了。