Postgres流复制-同步复制

1.主机规划

角色	主机名	IP	操作系统	PostgreSQL版本
master	pg01	192.168.86.141	CentOS 7	postgresql(16.2)
slave	pg02	192.168.86.142	CentOS 7	postgresql(16.2)
slave	pg03	192.168.86.143	CentOS 7	postgresql(16.2)
slave	pg04	192.168.86.144	CentOS 7	postgresql(16.2)

2.配置主机

• 配置ip:

#配置ip
vi /etc/sysconfig/network-scripts/ifcfg-eth0 
IPADDR="192.168.86.141" 
 
systemctl restart network
 
vi /etc/sysconfig/network-scripts/ifcfg-eth0 
IPADDR="192.168.86.142" 

......
 
systemctl restart network

• 修改主机名

--修改主机名 
 
hostnamectl set-hostname pg01 
hostnamectl set-hostname pg02 
hostnamectl set-hostname pg03
hostnamectl set-hostname pg04
 
--master,slave 都要操作 
vi /etc/hosts 
192.168.86.141 pg01
192.168.86.142 pg02
192.168.86.143 pg03
192.168.86.144 pg04

 3.编译安装postgres 16.2

步骤见：

内容管理-CSDN创作中心，编译安装postgresql

4.配置主库&从库

• 在主库操作

su - postgres

--初始化主库 
initdb -E UTF8 --locale C -D /home/postgres/pgdata16 -W 
mkdir /home/postgres/archives 

--修改 postgresql.conf 
listen_addresses = '*' 
wal_level = replica 
wal_log_hints = on 
logging_collector = on 
archive_mode=on 
archive_command='cp %p /home/postgres/archives/%f' 
synchronous_standby_names= 'pg02,pg03,pg04' #指定同步复制的 standby 的名称 
synchronous_commit= on 

--启动数据库 
pg_ctl start 

--创建同步用户 
create user repl replication password 'repl'; 

--配置 pg_hba.conf，运行主库接受流复制连接 
host    all             all     192.168.86.0/24         scram-sha-256
host    replication     repl    192.168.86.0/24         scram-sha-256

•  生成备库

su - postgres

--直接从主库备份过来 
pg_basebackup -h pg01 -Urepl -R -Fp -P -D /home/postgres/pgdata16

--在 postgresql.auto.conf 文件中需要手动配置连接参数 application_name 
application_name=pg02&pg03&pg04 指定备库节点的名称 

primary_conninfo = 'application_name=pg0x user=repl password=repl 
channel_binding=prefer host=pg01 port=5432 sslmode=prefer sslcompression=0 
sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable 
krbsrvname=postgres target_session_attrs=any' 

--启动备库 
pg_ctl start

5.检查同步状态

• 主库执行

postgres=#  select application_name,client_addr,state, sync_priority, sync_state
from pg_stat_replication;
 application_name |  client_addr   |   state   | sync_priority | sync_state
------------------+----------------+-----------+---------------+------------
 pg02             | 192.168.86.142 | streaming |             1 | sync
 pg04             | 192.168.86.144 | streaming |             3 | potential
 pg03             | 192.168.86.143 | streaming |             2 | potential

主库为自己管理的每一个备库指定一个同步优先级（sync_priority） 与 同步状态

（sync_state） 。

同步优先级（sync_priority）表示备库在同步模式下的优先级，它是一个固定值。较小的

值表示较高的优先级，而 0 是一个特殊值，表示 异步模式。备库优先级是一个有序列表，

在主库配置参数 synchronous_standby_names 中依序给出。

可以看到备库 pg02 的同步状态是 sync， 其他备库的状态都是 potential， 表示是潜在的

同步库。

pg02 的优先级是 1， pg03 的优先级是 2，pg04 的优先级是 3， 这个优先级是由

synchronous_standby_names 参数配置中的顺序决定的。

当所有从库都故障了则主库会hang住

• 配置两个同步节点

配置主库的 synchronous_standby_names

synchronous_standby_names = '2(pg02,pg03,pg04)' 

--reload 生效 
pg_ctl reload

查看同步状态

postgres=#  select application_name,client_addr,state, sync_priority, sync_state
from pg_stat_replication;
 application_name |  client_addr   |   state   | sync_priority | sync_state
------------------+----------------+-----------+---------------+------------
 pg02             | 192.168.86.142 | streaming |             1 | sync
 pg04             | 192.168.86.144 | streaming |             3 | potential
 pg03             | 192.168.86.143 | streaming |             2 | sync

注：两个同步节点pg02&pg03都故障时则主库hang住

• 配置 quorum 模式

配置主库的 synchronous_standby_names

synchronous_standby_names = 'ANY 2(pg02,pg03,pg04)' 

--reload 生效 
pg_ctl reload 


--查看同步状态
postgres=#  select application_name,client_addr,state, sync_priority, sync_state
from pg_stat_replication;
 application_name |  client_addr   |   state   | sync_priority | sync_state
------------------+----------------+-----------+---------------+------------
 pg02             | 192.168.86.142 | streaming |             1 | quorum
 pg04             | 192.168.86.144 | streaming |             1 | quorum
 pg03             | 192.168.86.143 | streaming |             1 | quorum

现在各个节点的优先级都是一样的， 同步状态全部变成了 quorum。

如果停掉其中任意一个备库，还有一个正常工作的备库时，主库还是可以做数据更新的，

但如果再停掉一个备库，主库的更新操作就会 hang 住。

5.主从切换

--停掉主库 
pg_ctl stop -m fast 

--备库 pg02 进行 promote 
pg_ctl promote 

--在原主库 pg01 上执行 pg_rewind 
pg_rewind --target-pgdata $PGDATA --source-server='host=pg02 
port=5432 user=postgres password=postgres dbname=postgres' -R 

在原主库的 postgresql.auto.conf 文件中添加如下内容： 
primary_conninfo = 'application_name=pg01 user=repl password=repl 
channel_binding=prefer host=pg02 port=5432 sslmode=prefer sslcompression=0 
sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable 
krbsrvname=postgres target_session_attrs=any' 

启动原主库， 这时该主库变成了备库。 
pg_ctl start 

测试数据同步...... 

其他备库修改下设置，follow 主库 pg02 
primary_conninfo = 'application_name=pg0x user=repl password=repl 
channel_binding=prefer host=pg02 port=5432 sslmode=prefer sslcompression=0 
sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable 
krbsrvname=postgres target_session_attrs=any'

6.异常处理-查询冲突

主库上的一些操作可能会与 Hot Standby 库上的查询产生冲突， 会导致正在执行的查询

被取消，并报如下错误：

ERROR： canceling statement due to conflict with recovery

产生冲突的原因有：

• 主库上运行的 VACUUM 清理掉了备库上的查询需要的多版本数据。
• 主库上执行 LOCK 命令或各种 DDL 语句会在表上产生 Exclusive 锁， 而在备库上对
  这些表进行查询时， 这两个操作之间会有冲突。
• 在主库上删除了一个数据库， 而备库上有很多 session 还连接在该数据库上。
• 在主库上删除了一个表空间， 而备库上的查询需要存放一些临时文件在此表空间中。​​​​​​

查询冲突处理方法：

•  让备库上的应用 WAL 日志的过程等待一段时间， 等备库上的查询结束后再应用 WAL
  日志。
• 取消备库上正在执行的查询。

  如果备库上的查询是一个大查询， 需要运行很长的时间， 让应用 WAL 日志的过程一直等

  待， 会导致备库延迟主库太多的问题， 因此 PostgreSQL 在 postgresql.conf 中增加了

  两个参数用于控制应用 WAL 日志的最长等待时间， 超过设定时间就会取消备库上正在执

  行的 SQL 查询。

  •  max_standby_archive_delay： 备库从 WAL 归档中读取时的最大延迟。 默认为 30

  秒，如果设置为-1， 则会一直等待。

  • max_standby_streaming_delay： 备库从流复制中读取 WAL 时的最大延迟。 默认

  为 30 秒， 如果设置为-1， 则会一直等待。

  如果备库就是用来执行一些大查询的， 可以把这两个参数设置成较大的值。

  大多数冲突发生的原因是主库上把备库需要的多版本数据给清理掉了， 可以通过在备库上的 postgresql.conf 中设置参数 hot_standby_feedback 为 true 来解决此问题。 设置此

  参数为 true 后， 备库会通知主库， 哪些多版本数据在备库上还需要， 这样主库上的

  AutoVacuum 就不会清理掉这些数据， 就能大大减少冲突的发生。 还有一个办法是把主

  库上的参数 vacuum_defer_cleanup_age 的值调得大一些， 以延迟清理多版本数据。

  在备库上因为冲突而被取消执行的 SQL 命令的数量可以在视图pg_stat_database_conflicts 中查询到。

  • hot_standby_feedback：备库定时将最小活跃事务 ID(xmin)告诉 master，使得 master

  在执行 vacuum 时对备库还需要的 tuple 手下留情，但这样可能会导致主库膨胀。

  • vacuum_defer_cleanup_age：指定 vacuum 延迟清理死亡元组的事务数，vacuum 会延

  迟清除无效的记录，延迟的事务个数通过 vacuum_defer_cleanup_age 进行设置。默认

  为 0，在主库上设置一个稍大的值也可以减少冲突的发生，但是不太好计量