postgresql16 物理复制与逻辑复制的实现和对比

本文面向想要练习 PostgreSQL 中数据库复制基础知识但可能无法访问远程服务器的初学者。我认为学习新技术时，在自己的机器上运行示例以巩固概念是至关重要的。对于副本来说，这可能很困难，因为许多可用的资源假设用户具有一定的 PostgreSQL 经验并且可以访问另一台运行副本的服务器。我们不会在这里做任何假设，唯一的先决条件是您已经安装了 Postgres 并且可以登录到 shell。

准备工作

对于这些示例，我们使用 Ubuntu 22.10 来运行 PostgreSQL 16（开发版本）。

$HOME/pg/data

将$HOME/pg/data 作为我们的数据目录。第一个示例将介绍使用流式物理复制，然后是使用 日志传输 (shipping)进行物理复制的简短示例，然后是逻辑复制的示例，最后总结不同的复制类型及其功能。

流式物理复制

首先，我们以超级用户身份登录主服务器，通常这是默认用户“postgres”。

psql -U postgres

进入 shell 后，为复制创建一个新角色，在主服务器中创建一个表，向该表中插入一些数据，然后退出。

postgres=# CREATE ROLE rep_user WITH REPLICATION LOGIN PASSWORD 'rep_pass';
postgres=# create table t1(a int, b int);
postgres=# insert into t1 values (1,2);
postgres=# \q

打开 $HOME/pg/data/postgres.conf 并确保

listen_addresses = 'localhost'

然后，将以下内容附加到 $HOME/pg/data/pg_hba.conf 的末尾

host  replication   rep_user  localhost   md5

运行以下命令创建主服务器的备份，然后将其用作副本服务器的数据目录（位于 $HOME/pg/rep）。

$ pg_basebackup -h localhost -U rep_user -X stream -C -S replica_1 -v -R -W -D $HOME/pg/rep

创建此目录后，打开 $HOME/pg/rep/postgres.conf 并将端口号设置为 PostgreSQL 默认 5432 以外的其他值。

port = 5433

然后在 postgres.conf 中编辑以primary_conninfo开始的 行，使其如下所示：

primary_conninfo = 'dbname=postgres user=postgres host=localgost port=5432 sslmode=disable'

在启动服务器之前我们必须做的最后一件事是在副本服务器的数据文件夹中创建一个 空的standby.signal文件。在命令行中输入以下内容：

$ touch $HOME/pg/rep/standby.signal

打开第二个终端，因为我们将在主机上启动两个独立的 PostgreSQL 实例，每个实例位于不同的端口上。

在第一个终端（主服务器）中，输入：

pg_ctl -D $HOME/pg/data start

在第二个终端（副本服务器）中，输入：

pg_ctl -D $HOME/pg/rep start

每个都应该给出一个输出，表明服务器已启动。我们可以通过输入以下内容来确认这一点：

$ ps -aux | grep postgres
tristen    29088  0.0  0.1 175596 18580 ?        Ss   15:42   0:00 /home/tristen/disk/pgapp/bin/postgres -D /home/tristen/pg/data
tristen    29089  0.0  0.0 175728  2388 ?        Ss   15:42   0:00 postgres: checkpointer 
tristen    29090  0.0  0.0 175752  2364 ?        Ss   15:42   0:00 postgres: background writer 
tristen    29092  0.0  0.0 175596  7424 ?        Ss   15:42   0:00 postgres: walwriter 
tristen    29093  0.0  0.0 177196  4892 ?        Ss   15:42   0:00 postgres: autovacuum launcher 
tristen    29094  0.0  0.0 177176  4984 ?        Ss   15:42   0:00 postgres: logical replication launcher 
tristen    29112  0.0  0.1 175596 18456 ?        Ss   15:42   0:00 /home/tristen/disk/pgapp/bin/postgres -D /home/tristen/pg/rep
tristen    29113  0.0  0.0 175728  2564 ?        Ss   15:42   0:00 postgres: checkpointer 
tristen    29114  0.0  0.0 175596  2564 ?        Ss   15:42   0:00 postgres: background writer 
tristen    29115  0.0  0.0 176348  4488 ?        Ss   15:42   0:00 postgres: startup recovering 000000010000000000000014
tristen    29116  0.0  0.0 176136  3664 ?        Ss   15:42   0:00 postgres: walreceiver streaming 0/14000110
tristen    29117  0.0  0.0 177336  7060 ?        Ss   15:42   0:00 postgres: walsender rep_user 127.0.0.1(39650) streaming 0/14000110
tristen    29126  0.0  0.0  17580  2252 pts/2    S+   15:42   0:00 grep --color=auto postgres

可以看到有两个进程 $HOME/disk/pgapp/bin/postgres 在不同的两个目录中运行。

现在，我们将连接到两个实例中的每一个。首先是第一个终端上的主服务器：

psql -U postgres -p 5432

然后是第二个终端上的副本服务器：

psql -U postgres -p 5433

-p 标志用于指定端口。在主服务器上使用它是多余的，因为它使用默认端口。然而，这很好地表明我们确实连接到两个不同的端口，从而连接到两个不同的 PostgreSQL 实例。

如果我们在 Postgres shell 中输入 \d，可以获得数据库中的表的列表。在副本服务器相对应的第二个终端中，可以看到表 t1 确实被复制了。

postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

现在让在主服务器上创建一个新表，以表明它将自动复制到副本服务器。

在第一个终端上输入：

postgres=# create table t2(c int, d text);
CREATE TABLE
postgres=# insert into t2 values (3, 'hello');
INSERT 0 1

在第二个终端中输入：

postgres=# select * from t2;
 c |   d   
---+-------
 3 | hello
(1 row)

可以看到，不仅新表是从主服务器复制过来的，而且刚刚插入的数据也是如此。

值得注意的是，这种复制关系只是单向的。也就是说，副本服务器只能从主服务器的复制。如果尝试在副本服务器中创建表，则会收到一条错误消息：

ERROR:  cannot execute CREATE TABLE in a read-only transaction

从逻辑上讲，这是因为我们希望副本是只读的，以使来自 主服务器的数据更容易可用。

日志传输物理复制

日志传输(Shipping)物理复制 与 流式物理复制 有类似的设置，主要只是更改一些配置文件。在主服务器上编辑 postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

如果服务器正在运行，请重新启动服务器并创建主服务器的备份：

$ pg_basebackup -h localhost -U rep_user -X fetch -v -R -W -D $HOME/pg/rep

打开 $HOME/pg/rep/postgresql.conf 并编辑两行。注释掉以 archive_command 开头的行，并取消注释以 Restore_command 开头的行，添加在 = 之后进行恢复时要使用的命令。这些行应如下所示：

#archive_command = 'cp %p /path/to/archive/%f'

restore_command = 'cp /path/to/archive/%f %p'

像 流式物理复制 示例中一样启动两台服务器，应该可以看到副本正在复制主服务器。请注意，日志传输更改(shipping changes)可能不会像在流式物理复制中那样实时。日志传输复制具有较高的延迟，并且仅在 WAL 文件填充到副本时才发送更改。

逻辑复制

逻辑复制与物理复制具有与服务器相同的初始设置。它涉及一些不同的配置，还涉及一些手动编辑以使表正常工作。

在主服务器上编辑 postgresql.conf

wal_level = logical

确保主服务器上的 pga_hba.conf 包含复制用户的正确连接权限。

host     all     rep_user     localhost     md5

一旦所有配置准备就绪，要启动两台服务器并登录 shell。回想一下，在我们的示例中，每个服务器都使用不同的端口号，因此请确保您没有两次使用同一服务器！在每个服务器各自的 shell 中，我们需要在每个服务器中创建一个具有 相同名称和结构 的表。如果表完全不同，副本将无法找到它，将收到错误。也就是说，在两个终端中输入以下内容：

postgres=# create table t1(id int, val text);

现在我们在两个数据库中都有相同的空表，继续以下命令复制该表。在主服务器上输入以下命令为表 t1 创建发布：

postgres=# CREATE PUBLICATION pub_t1 FOR TABLE t1;

这将为表 t1 创建一个发布，这意味着表 t1 将被复制到副本服务器。可以复制任意数量的表作为发布的一部分。

创建发布后，将副本数据库中的表 t1 订阅到主数据库中的表 t1。为此，必须在副本数据库中创建订阅，如下所示：

postgres=# CREATE SUBSCRIPTION sub_t1 CONNECTION 'dbname=postgres host=localhost port=5432 user=postgres' PUBLICATION pub_t1;

注意，在本示例中，我们同时使用了默认用户 postgres 和默认数据库 postgres，但这不是必需的。

现在已经创建了订阅，将看到主服务器表 t1 中的所有更新都出现在副本服务器的表 t1 中。主服务器上没有做发布的任何其他表都不会复制到副本。现在做一个测试，在主数据库终端中向表 t1 中插入一些数据：

postgres=# insert into t1 values (1, 'hello');
INSERT 0 1
postgres=# insert into t1 values (2, 'world');
INSERT 0 1

现在让我们看看该表是否实际上已复制到副本服务器中：

postgres=# select * from t1;
 id |  val  
----+-------
  1 | hello
  2 | world
(2 rows)

现在，数据已成功复制到副本服务器。但我们怎么区分这是不是物理复制呢？好吧，让我们向主数据库添加一个新表并插入一些数据：

postgres=# create table t2(c int, d int);
CREATE TABLE
postgres=# insert into t2 values(3,4);
INSERT 0 1
postgres=# insert into t2 values(5,6);
INSERT 0 1

再检查一下副本：

postgres=# select * from t2;
ERROR:  relation "t2" does not exist
LINE 1: select * from t2;

可以看到表 t2 没有被复制，因为它没有订阅，因此不存在于副本中。这允许将某些表从中央数据库公开给远程数据库，同时保持其他表的私有性。

但是对 表结构 的 修改 在副本数据库中 结果会如何？好吧，让我们测试一下。在主数据库终端中，更改表结构并添加一些数据：

postgres=# alter table t1 add column x int;
ALTER TABLE
postgres=# insert into t1 values (3, 'foo', 42);
INSERT 0 1
postgres=# insert into t1 values (4, 'bar', 10);
INSERT 0 1
postgres=# select * from t1;
 id |  val  | x  
----+-------+----
  1 | hello |   
  2 | world |   
  3 | foo   | 42
  4 | bar   | 10
(4 rows)

现在检查一下副本服务器：

postgres=# select * from t1;
 id |  val  
----+-------
  1 | hello
  2 | world
(2 rows)

可以看到副本服务器不再复制主服务器。即使将同一架构中的新数据添加到主数据库中，它也不会显示在副本中：

postgres=# insert into t1 values (15, 'test');
INSERT 0 1

postgres=# select * from t1;
 id |  val  
----+-------
  1 | hello
  2 | world
(2 rows)

由于我们更改了 t1 的 架构，副本不再从中复制数据，因为它现在实际上是一个不同的表。如果您更改了表的架构并将副本运行在其他地方，记住这一点很重要。

如果不在副本中创建与主服务器中的表 相匹配的表，会发生什么情况？

postgres=# CREATE PUBLICATION pub_t2 FOR TABLE t2;
CREATE PUBLICATION

postgres=# CREATE SUBSCRIPTION sub_t2 CONNECTION 'dbname=postgres host=localhost port=5432 user=postgres' PUBLICATION pub_t2;
ERROR:  relation "public.t2" does not exist

这样收到一条错误消息，指出我们想要订阅的 关系 不存在。这可能会非常令人困惑，因为我们给了它所有连接信息，并且我们知道表 t2 存在于该位置。但是，此错误是指它在我们的副本上找不到表 t2，因此无法创建订阅。因此，请务必记住，我们需要两个数据库包含具有相同架构的相同表，以便逻辑复制正常工作。

对比

以下是我们从高层次角度讨论的所有主题的摘要。如果您仍在尝试决定要使用哪种复制方法，那么此摘要可能有助于做出决定。

逻辑复制与物理复制

逻辑复制与物理复制的比较

日志传输 与 流式复制

流式物理复制与日志传输物理复制的比较

异步流与同步流 复制

异步流与同步流的比较

总结

关于使用 PostgreSQL 设置服务器复制以实现各种复制方法的文章到此结束。首先，我们回顾了设置和运行每种方法的实际步骤，然后查看了每种方法功能的高级摘要。

References 参考

27.2. log-shipping standby servers. PostgreSQL Documentation. (2023, February 9). Retrieved March 29, 2023, from https://www.postgresql.org/docs/current/warm-standby.html

31.11. quick setup. PostgreSQL Documentation. (2023, February 9). Retrieved March 29, 2023, from https://www.postgresql.org/docs/current/logical-replication-quick-setup.html

B, A. (2022, April 8). [web log]. Retrieved March 29, 2023, from https://scalegrid.io/blog/comparing-logical-streaming-replication-postgresql/.

Levinas, M. (2022, October 10). [web log]. Retrieved March 29, 2023, from https://www.cherryservers.com/blog/how-to-set-up-postgresql-database-replication.

Ravoof, S. (2023, February 17). [web log]. Retrieved March 29, 2023, from https://kinsta.com/blog/postgresql-replication/.

原文地址Setting Up a PostgreSQL Replica Server Locally - Highgo Software Inc.文章来源地址https://www.toymoban.com/news/detail-791096.html

到了这里，关于postgresql16 物理复制与逻辑复制的实现和对比的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！