PostgreSQL-11物理流复制部署方案

实验环境：

背景介绍

PostgreSQL在9.0版本开始支持物理复制，也就是流复制（Streaming Replication），通过流复制技术，可以从实例级复制出一个与主库一模一样的从库（也叫备库）。流复制同步方式有同步，异步两种，如果主节点和备节点不是很忙，通常异步模式下备库和主库的延迟时间能控制在毫秒级。本次案例实验环境采用异步方式部署。

另一种复制方式为逻辑复制（Logical Replication），通常也称之为选择性复制，因为逻辑复制可以基于表级别的复制，而不是复制实例上的所以数据库所有的表。PostgreSQL10版本前不支持逻辑复制。

流复制和逻辑复制主要的差异有如下几点：

• 流复制是物理复制，其核心原理是主库将预写日志WAL日志流发送给备库，备库接收到WAL日志流后进行重做，因此流复制时基于WAL日志的物理复制。逻辑复制核心原理也是基于WAL，逻辑复制会根据预先设置好的规则解析WAL日志，将二进制文件解析成特定格式的逻辑变化信息，之后主库逻辑变化信息发给备库，备库收到WAL逻辑解析信息后再次应用日志。
• 流复制只能对PostgreSQL实例级进行复制，而逻辑复制能够对数据库表级别进行复制。
• 流复制能对DDL操作进行复制，比如主库的新建表，给表加，减字段会自动同步到备库，而逻辑复制不能对DDL操作进行复制到备库
• 流复制主库可读写，但备库值允许查询不允许写入，而逻辑复制主，备库均可读写
• 流复制要求PostgreSQL大版本必须一致，而逻辑复制支持跨PostgreSQL大版本。
• 知识点：

WAL：（Write—Ahead Logging）PostgreSQL预写日志。记录数据库的变化，二进制文件格式，当主库出现异常断电时，如何WAL已经成功写入，但还没有来得及刷新到数据文件，当数据库再次启动时会根据WAL进行事务前滚，从而恢复数据库到一致的状态。

部署过程：

• 前提条件：

主库pg数据库均已安装完成，并可以正常启动状态完成以下的步骤。以下操作均在主库上进行。

STEP01：

登录pg库中创建流复制专用用户。切换到pg库所属系统用户下执行，新体系默认为postgres系统用户下。

[root@orcl144 data]# su - postgres

Last login: Mon Feb 28 11:43:08 CST 2022 on pts/0

[postgres@orcl144 ~]$ cd /opt/opsmgr/web/components/postgresql11linux64.1/data

[postgres@orcl144 data]$ ../bin/psql -p 7092 -U postgres

psql (11.10)

Type "help" for help.

postgres=# create role repuser replication login connection limit 5 encrypted password 'XXXX';

STEP02：修改pg_hba.conf文件增加以上创建用户的访问规则，将以下内容红框内容插入一行，位置自定义。

[postgres@orcl144 data]$ vim pg_hba.conf

STEP03：修改postgresql.conf文件增加以下参数，建议增加到文件末尾

[postgres@orcl144 data]$ vim postgresql.conf

• 参数解释：
• max_wal_senders：该参数控制主库上的最大WAL发送进程数，通过pg_basebackup命令在主库上做备份时消耗wal进程，此参数不能大于max_connections高，默认10.
• wal_keep_segments：该参数设置主库pg_wal目录保留的最小wal日志文件数，以便备库落后主库时可以通过主库保留的wal进行追回。该参数越大，备库在异常断开时追上主库的概率越大，如果没有开归档，该参数建议配置大些，但会消耗大量的磁盘空间（约：wal_keep_segments*16MB）

STEP04：重启pg库。非新体系请利用相关pg_ctl命令重启即可。

[root@orcl144 data]# systemctl restart postgresql11linux64.rdbms.1.service

• 前提条件：

以下操作均在备库上操作。同时备库pg数据库已安装完成。

情况一：若已经正常启动，请停止pg库，将data文件夹删除或者重命名

情况二：未启动时，若data文件夹存在，就删除即可。

STEP01：

切换到pg库所属系统用户下执行，新体系默认为postgres系统用户下。并cd到pg的安装目录下（注意：非data文件夹下），执行以下命令：

[root@orcl144 data]# su – postgres

[postgres@orcl144 ~]$ cd /opt/opsmgr/web/components/postgresql11linux64.1/

[postgres@zhdb01p postgresql11linux64.1]$ ./bin/pg_basebackup -Fp -Xs -v -p 7092 -h 192.168.59.144 -P -U repuser -D "/opt/opsmgr/web/components/postgresql11linux64.1/data"

看到以上的返回信息，即说明备库同步完成。若执行报错，请检查命令中的，端口，ip及data目录是否正确后再次执行。同时要注意主库的防火墙需要关闭

STEP02：编写recovery.conf，增加以下内容。可以复制模板，或者vim创建一个文件，本示例是采用拷贝模板方式。

[postgres@zhdb01p data]$ cp ../share/recovery.conf.sample ./recovery.conf

增加以下内容：

#

standby_mode = on

primary_conninfo = 'hostaddr=192.168.59.144 port=7092 user=repuser password=XXXX'

#--------------------------------------------------------------------------

STEP03：启动备库pg库，若能启动成功即说明同步完成，可以确认下节的验证是否正常。

主备验证：

在进行主备切换之前我们首先要知道当前您操作的服务器数据库的是主库还是备库的角色，这里提供五种方法进行判断数据库的角色，该案例为一主一备，但方式同样适用于一主多备环境。

方式一：操作系统上查看WALsender发送进程或 Wal recever接收进程

在数据库上执行以下命令【ps -ef | grep "wal" | grep -v grep】：

如果输出wal sender .. streaming 进程则说明当前数据库为主库：

如果输出wal receiver .. streaming进程说明当前数据库为备库，如下图所示：

方式二：数据库上查看WAL发送和或WAL接收进程

也可以在数据库层面查询WAL进程，例如在数据库上执行查询pg_stat_replication视图

【select pid,usename,application_name,client_addr,state,sync_state from pg_stat_replication;】，如果返回记录说明是主库，没有记录则是备库。如下图所示：

在数据库上查询pg_stat_wal_recevier视图

【select pid,status,last_msg_send_time,last_msg_receipt_time,conninfo from pg_stat_wal_receiver;】，如果返回记录说明是备库，而主库上是没有记录。如下所示：

方式三：通过系统函数查看。

登录数据库后执行以下函数【select pg_is_in_recovery();】，如下所示：

如果返回 t 说明是备库，返回 f 是主库

方式四：查看数据库控制信息

通过pg_controldata命令查看数据库控制信息，内容会包含wal日志信息，checkpoint，数据块等信息。其中的database cluster state信息可判断是【in production】主库还是【in archive recover】备库，如下图所示：

主库：

备库：

方式五：通过recovery.conf配置文件查看

根据之前案例的部署过程中，其中备库上是需要在PGDATA目录下创建recovery.conf文件来配置同步主库的相关信息，所以，如果存在这个文件的说明是备库，如果在PGDATA目录中不存在或者存在recovery.done文件说明是主库。