【 PG 高可用系列】PostgreSQL的流复制环境搭建（一）_pg数据库流复制默认端口-优快云博客

本文链接：https://blog.youkuaiyun.com/suoyue_py/article/details/121475066

PostgreSQL的流复制环境搭建（一）

1. 复制方式
- 1.1. 物理流复制
- 1.2. 逻辑复制
2. 流复制环境搭建
3. 主备检查与测试
4. 延迟应用
5. 多备库场景

PostgreSQL 高可用系列博文讲解（B站同步介绍视频：https://space.bilibili.com/282421760）
支持一下，关注一波微信公众号：【钥道不止】

1. 复制方式

（物理）流复制（streaming replication）
逻辑复制（logical replication）

1.1. 物理流复制

流复制只能对PG实例级进行复制
物理复制的核心原理是主库将预写日志WAL日志流发送给备库，备库接收到WAL日志流后进行重做
流复制能对DDL操作进行复制
流复制主库可读写，但从库只允许查询操作不允许写入
流复制要求PG大版本必须一致

在这里插入图片描述

1.2. 逻辑复制

https://www.cnblogs.com/gjc592/p/11649315.html

可基于表级别复制，是一种粒度可细的复制
逻辑主备角色分为发布者(Publication)和订阅者(Subscription)
逻辑主库和逻辑备库为不同的PostgreSQL实例，可以在同一主机上，也可以在不同主机上。

2. 流复制环境搭建

2.1. 操作系统配置

2.1.1. 修改hosts文件

vi /etc/hosts
192.168.56.101  pgdb1
192.168.56.102  pgdb2
192.168.56.102  pgdb3

2.1.2. 防火墙设定

将同PostgreSQL相关的服务、协议、IP以及端口添加到PostgreSQL各主机的防火墙白名单中，或关闭防火墙。

方式一：关闭防火墙

# 查看防火墙状态
systemctl status firewalld
# 停用防火墙
systemctl stop firewalld
# 禁用防火墙，避免重开机重启防火墙
systemctl disable firewalld

方式二：开放PostgreSQL端口，本次主备库均使用5432端口

# 加入开放5432端口
firewall-cmd --zone=public --permanent --add-port=5432/tcp
# 重加载服务生效
firewall-cmd --reload
# 查看追加开放的端口
firewall-cmd --list-ports
# 查看系统开放的网络配置
iptables -L -v -n

2.1.3. 关闭SELINUX

# 修改“SELINUX”的值“disabled”（重启生效）
vi /etc/selinux/config
SELINUX=disabled
# 临时关闭SELINUX（重启失效）
set enforce 0
# 查看SELINUX状态
sestatus -v

2.1.4. 设置时区和时间同步

使用 ntpdate 或 chronyc 配置时间同步。先检查避免两个同时配置导致冲突。

systemctl status chronyc
systemctl status ntpdate

如果当前网络环境不存在时间服务器，则可将数据库主节点设置为时间服务器节点。

主机节点配置ntp服务

# 安装ntp依赖
yum install -y ntp
# 以下定义是让NTP Server和其自身保持同步，如果在/etc/ntp.conf中定义的server都不可用时，将使用local时间作为ntp服务提供给ntp客户端。 
vi /etc/ntp.conf  
server 127.127.1.0 
fudge 127.127.1.0 stratum 10
# 接着启动主节点ntp服务
service ntpd start

备机节点配置ntpdate服务

# 安装ntp依赖
yum install -y ntp
# 配置ntp服务同步主节点时间
vi /etc/ntp.conf  
server pg01
# 启动ntp服务
service ntpd start
# 观察时间同步状况
ntpq -p
# 查看时间同步结果
ntpstat

# 安装ntpdate依赖
yum install -y ntpdate
# 手动测试同步时间
ntpdate -u pg01
# 使用crontab，于备机节点设置定时同步：
crontab -e
0 1 * * * /usr/sbin/ntpdate -u pg01 &>> /var/log/ntpdate.log 2>&1

2.1.5. 设置root允许远程登录

安装PostgreSQL时需要root帐户远程登录访问权限，修改PermitRootLogin配置，允许用户远程登录。

# 注释掉“PermitRootLogin no” 或 将“PermitRootLogin”改为“yes”
vi /etc/ssh/sshd_config
#PermitRootLogin no
PermitRootLogin yes
# 重启 ssh 服务使其生效
service sshd restart

2.1.6. 操作系统参数（根据性能需要可选修改）

vi /etc/sysctl.conf
#在linux6和7上，使用aio的话，需要设置它，适应异步IO，可以不需要重新修改该值
fs.aio-max-nr = 1048576
#在linux6和7上，允许打开文件数，pg参数max_files_per_process对应，不重新修改该值
fs.file-max = 76724600
# 信号量, ipcs -l 或 -u 查看，每16个进程一组，每组信号量需要17个信号量。不重新修改该值
(四个参数，第一个参数*第四个参数= 第二个参数， 第一个参数和第三个参数相等)
第一个代表信号量， 第四个代表是组， 第三个参数设置大于等于17 
kernel.sem = 4096 2147483647 2147483646 512000    
# 所有共享内存段相加大小限制(建议内存的80%，单位page，查看page大小getconf PAGE_SIZE)
# 例如：page大小4096， 在128G内存设置80%的值如下
kernel.shmall = 26843545     （需计算）
# 最大单个共享内存段大小(建议为大于shared_buffer值)，单位bytes。 
kernel.shmmax = 51539607552  (需计算)
# 一共能生成多少共享内存段，每个PG数据库集群至少2个共享内存段，不重新修改该值
kernel.shmmni = 819200        
#iptables防火墙链表相关,不重新修改该值 
net.core.netdev_max_backlog = 10000
# 网络接收buffer大小，单位bytes，不重新修改该值
net.core.rmem_default = 262144       
# 允许最大网络接收buffer大小,单位bytes，不重新修改该值
net.core.rmem_max = 4194304          
# 网络传输buffer大小，单位bytes,不重新修改该值
net.core.wmem_default = 262144       
# 网络传输最大buffer大小，单位bytes，不重新修改该值
net.core.wmem_max = 4194304          
# socket监听数，默认128，不重新修改该值
net.core.somaxconn = 4096
# 加快僵尸进程回收速度，不重新修改该值
net.ipv4.tcp_fin_timeout = 5
#系统脏页到达这个值,脏页刷到磁盘，当前设置390M，如果磁盘IO能力是512M/s 
vm.dirty_background_bytes = 409600000  （需计算）
#  比这个值老的脏页，将被刷到磁盘。3000表示30秒。不重新修改该值
vm.dirty_expire_centisecs = 3000   
#如果系统进程刷脏页太慢，使得系统脏页超过内存 80 % 时，则用户进程如果有写磁盘的操作（如fsync, fdatasync等调用），则需要主动把系统脏页刷出，不重新修改该值
vm.dirty_ratio = 80                          
#  有效防止用户进程刷脏页，在单机多实例，并且使用CGROUP限制单实例IOPS的情况下非常有效。
#  pdflush（或其他）后台刷脏页进程的唤醒间隔， 100表示1秒。  不重新修改该值
vm.dirty_writeback_centisecs = 50            
#  在分配内存时，设置为0，vm.overcommit_ratio参数可以不设置， 不重新修改该值
vm.overcommit_memory = 1     
#  关闭交换分区， 数据库服务器不建议使用swap，不重新修改该值
vm.swappiness = 0            
# 限制本地动态端口分配范围，防止占用监听端口 ，不重新修改该值
net.ipv4.ip_local_port_range = 40000 65535    
#建议shared buffer设置超过64GB时使用大页，查看页大小more /proc/meminfo |grep Hugepagesize， 大于数据库需要的shared_buffer内存即可，即HugePages_Total * Hugepagesize > shared_buffers。
vm.nr_hugepages = 66536    （需计算）
#单个进程的打开句柄不能大于fs.nr_open, 对于有很多对象（表、视图、索引、序列、物化视图等）的PostgreSQL数据库，建议设置为2000万,不重新修改该值
fs.nr_open=20480000


# 执行命令使参数生效
sysctl –p

2.1.7. 资源限制

vi /etc/security/limits.conf 
postgres soft nproc unlimited
postgres hard nproc unlimited
postgres soft nofile 100000
postgres hard nofile 100000
postgres soft stack unlimited
postgres hard stack unlimited
postgres soft core unlimited
postgres hard core unlimited
postgres soft memlock unlimited
postgres hard memlock unlimited

2.1.8. 主备互信

数据库wal日志使用cp归档到本地的可忽略此步骤。使用scp命令集中归档至同一存储节点的，需要对数据库节点的postgres用户之间配置互信。

#每个节点都执行
su - postgres
ssh-keygen -t rsa # 一路回车
# 将公钥添加到认证文件中
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
# 并设置authorized_keys的访问权限
chmod 600 ~/.ssh/authorized_keys
# 只要在一个节点执行即可。这里在192.168.56.101上执行
ssh 192.168.56.102 cat ~/.ssh/id_rsa.pub >>~/.ssh/authorized_keys
ssh 192.168.56.103 cat ~/.ssh/id_rsa.pub >>~/.ssh/authorized_keys
# 分发整合后的文件到其它节点
scp ~/.ssh/authorized_keys 192.168.56.102:~/.ssh/
scp ~/.ssh/authorized_keys 192.168.56.103:~/.ssh/
# 测试互信（节点均执行），第一次需要输入密码，之后就不需要输入密码了。
ssh 192.168.56.101
ssh 192.168.56.102
ssh 192.168.56.103

2.2. 主备规划

2.2.1. 创建数据库目录

su - root   
mkdir -p /pgdata/pg14
mkdir -p /pgdata/pgwal/archive_wals
mkdir -p /pg/{pghome,patroni}
chown -R postgres:postgres /{pg,pgdata}
chown -R postgres:postgres /pgdata/pgwal
chmod -R 700 /{pg,pgdata}

2.2.2. 配置环境变量

su - postgres 
vi .bashrc
export PGHOME=/pg/pghome
export PGDATA=/pgdata/pg14
export PGPORT=5432
export PGDATABASE=postgres
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH

source .bashrc

2.2.3. 源码安装

详细安装步骤参考：PostgreSQL的源码安装

tar zxvf postgresql-14.2.tar.gz
cd postgresql-14.2
./configure --prefix=/pg/pghome --with-ossp-uuid
gmake world
gmake install
# 编译安装PG自带的插件
cd contrib 
make && make install

2.2.4. 主库初始化

su - postgres
initdb -D /pgdata/pg14 -E UTF8 --locale=zh_CN.UTF-8

2.3. 主备流复制搭建

2.3.1. 主库参数配置

vi $PGDATA/postgresql.conf

listen_addresses = '0.0.0.0'
port = '5432'
max_connections = '1024'
wal_buffers=32MB
work_mem=4MB
superuser_reserved_connections = 10
max_locks_per_transaction = '64'
max_prepared_transactions = '0'
max_replication_slots = '10'
max_wal_senders = '50'
max_worker_processes = '8'
track_commit_timestamp = 'off'
logging_collector = on
log_destination=csvlog
log_filename = 'postgresql-%a.log'
log_connections = on
log_disconnections = on
log_checkpoints = on
log_lock_waits = on
log_statement = ddl
log_truncate_on_rotation = on
log_rotation_age = 1440
wal_level = 'replica'
wal_log_hints = 'on'
archive_mode = 'on'
archive_command = 'cp %p /pgdata/pgwal/archive_wals/%f'
archive_timeout = '1800s'
cluster_name = 'PGCluster'
hot_standby = 'on'
synchronous_commit = remote_write
hba_file = '/pgdata/pg14/pg_hba.conf'
ident_file = '/pgdata/pg14/pg_ident.conf'

restore_command = 'cp /pgdata/pgwal/archive_wals/%f %p'
recovery_target_timeline = 'latest'

2.3.1.1. synchronous_commit参数

单机环境下

on：当事务提交时，必须等待wal日志写入磁盘文件才能返回客户端成功
off：当事务提交时，wal日志写入wal_buffer即可返回客户端成功
local：与on类似

流复制环境下

remote_write：当主库事务提交时，必须等待wal日志传送到备库，当备库的系统缓存接受成功后才能返回客户端成功，这种对主库性能影响最小
on：当主库事务提交时，必须等待wal日志写入备库磁盘才能返回成功
remote_apply：当主库事务提交，必须等待wal日志在备库中成功应用才返回成功，这种模式主从数据完全一致，对主库影响最大

2.3.1.2. synchronous_standby_names参数

案例场景：一主两备环境（master/slave1/ slave2）

# 指定slave1为同步备库,slave2为默认的异步备库
synchronous_standby_names='slave1'

# master、slave1、slave1中任意一个同步提交成功就可返回主库成功，一主一同一异步
synchronous_standby_names='ANY 1(master,slave1,slave2)'

案例场景：一主四备（master/slave1/ slave2/ slave3/ slave4）

# slave1为同步备库,slave2为潜在同步备库,当1不可用时2升级为同步备库,3和4为异步
synchronous_standby_names='slave1,slave2'

# 1和2为同步备库，3为潜在同步备库，4为异步
synchronous_standby_names='FIRST 2(slave1,slave2,slave3)'

# 1、2、3中任意两个同步提交成功就可返回主库成功，4为异步
synchronous_standby_names='ANY 2(slave1,slave2,slave3)'

2.3.2. 主库认证参数配置

vi $PGDATA/pg_hba.conf
host replication replicator 192.168.56.101/32 md5
host replication replicator 192.168.56.102/32 md5
host replication replicator 192.168.56.103/32 md5
host all all 0.0.0.0/0 md5

2.3.3. 启动主库，并创建流复制用户

pg_ctl start
# 主库创建 replicator 流复制用户用于后面创建同步备库使用
psql -U postgres -c "CREATE USER replicator REPLICATION ENCRYPTED PASSWORD 'replicator';"

2.3.4 备库配置

pg_basebackup工具的具体使用参考：PostgreSQL的物理备份

# 在备库上使用 pg_basebackup 从主库同步数据
pg_basebackup -D $PGDATA -Fp -Xs -v -P -h 192.168.56.101 -p 5432 -U replicator

# 12版本之后将参数都放到了postgresql.conf文件中并编辑 standby.signal 文件（12之前参数都配置在recovery.conf文件中）
vi postgresql.conf
primary_conninfo = 'application_name=pgdb02 host=192.168.56.101 port=5432 user=replicator password=replicator'
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
recovery_target_timeline = 'latest'

# 编辑 standby.signal 文件
vi standby.signal
standby_mode = on # async：表示备库为异步同步方式。potential：表示备库当前为异步同步方式，如果当前的同步备库宕机后，异步备库可升级成为同步备库。sync：当前备库为同步方式。quorum：表示备库为quorum standbys的候选

# 启动备库
pg_ctl start

补充：pg_basebackup可带上 -R 参数自动生成 primary_conninfo 的参数配置

3. 主备检查与测试

3.1. 查看同步状态

# 主库使用 pg_stat_replication 监控流复制
\d pg_stat_replication;
select usename,application_name,client_addr,sync_state from pg_stat_replication;

# 备库使用 pg_stat_wal_receiver 监控流复制
\d pg_stat_wal_receiver;
select * from pg_stat_wal_receiver;

# 查看备库落后主库多少字节的WAL日志：  
select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_flush_lsn(),write_lsn)) delay_wal_size,* from pg_stat_replication ;
# 查看备库接收WAL日志和应用WAL日志的状态：  
select * from pg_last_wal_receive_lsn();
select * from pg_last_wal_replay_lsn();  
select * from pg_last_xact_replay_timestamp();

3.2. 主备的判断方式

数据库层面

# 通过系统函数查看(f为主库，t为备库:如果恢复仍在进行中为true)
select pg_is_in_recovery();

# 查看只读模式
show transaction_read_only;

操作系统层面

# 操作系统上查看WAL发送进程或WAL接收进程(看walsender或者walreceiver)
ps -ef | grep "wal" | grep -v "grep"
ps -ef | grep postgres 

# 查看数据库控制文件信息（返回in production表示为主库，返回in archive recovery表示是备库）
pg_controldata | grep cluster

# 12以前的版本还可以检查是否存在recovery.conf配置文件

3.3. 主备切换

pg12开始新增了一个pg_promote()函数，可以通过SQL命令激活备库。

# pg_promote()语法
pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60)

两个参数：

wait：表示是否等待备库的 promotion 完成或者 wait_seconds 秒之后返回成功，默认值为 true。
wait_seconds：等待时间，单位秒，默认 60秒切换实例

# 备库操作：激活备库
# 方式一：pg12开始及以后可用pg_promote()函数
select pg_promote(true,60);
# 方式二：在promote模式中，运行在指定数据目录中的后备服务器被命令退出恢复并且开始读写操作
pg_ctl promote -D $PGDATA     

# 验证备节点并使主备库数据块发生偏离
create table repl_t1(id int4);
insert into repl_t1(id) select from generate_series(1,10);

# 原主库操作：关闭主库，模拟主库故障
pg_ctl stop

# 原主库操作：主备库时间线偏离，无法将原主库以备库模式正常启动，可用pg_rewind先恢复
# 数据库在 initdb 时需要开启 checksums 或者设置  "wal_log_hints = on"， 设置主备节点的 wal_log_hints 参数并重启数据库
pg_rewind --target-pgdata $PGDATA --source-server='host=192.168.56.102 port=5432 user=postgres dbname=testdb' -P

# 旧主库转备库（12版本后在配置文件添加如下配置，修改primary_conninfo为新主库ip地址  ，12版本前配置recovery.conf文件）
vi postgresql.conf
primary_conninfo = 'application_name=pgdb01 host=192.168.56.102 port=5432 user=replicator password=replicator'
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
recovery_target_timeline = 'latest'

# 编辑 standby.signal 文件
vi standby.signal
standby_mode = on

# 启动备库，观察同步情况