1588.PostgreSQL主从库搭建
PostgreSQL的流复制环境搭建
- 复制方式
1.1. 物理流复制
1.2. 逻辑复制 - 流复制环境搭建
2.1. 操作系统配置
2.1.1. 修改hosts文件
2.1.2. 防火墙设定
2.1.3. 关闭SELINUX
2.1.4. 设置时区和时间同步
2.1.5. 设置root允许远程登录
2.1.6. 操作系统参数（根据性能需要可选修改）
2.1.7. 资源限制
2.1.8. 主备互信
2.2. 主备规划
2.2.1. 创建数据库目录
2.2.2. 配置环境变量
2.2.3. 源码安装
2.2.4. 主库初始化
2.3. 主备流复制搭建
2.3.1. 主库参数配置
2.3.1.1. synchronous_commit参数
2.3.1.2. synchronous_standby_names参数
2.3.2. 主库认证参数配置
2.3.3. 启动主库，并创建流复制用户
2.3.4 备库配置 - 主备检查与测试
3.1. 查看同步状态
3.2. 主备的判断方式
3.3. 主备切换 - 延迟应用
- 多备库场景
PostgreSQL 高可用系列博文讲解（B站同步介绍视频：https://space.bilibili.com/282421760）
支持一下，关注一波微信公众号：【 钥道不止 】 - 复制方式
（物理）流复制 （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
推荐使用chrony来同步时间
配置NTP服务,首选chrony
1） 中控机服务器
– root用户，中控机服务器操作如下
– yum 安装 ntp服务
[root@obproxy-node ~]# yum install ntp ntpdate -y
– 启动服务
[root@obproxy-node ~]# systemctl start ntpd
– 设置服务自启
[root@obproxy-node ~]# systemctl enable ntpd
– 查看ntp状态
[root@obproxy-node ~]# systemctl status ntpd
– 配置NTP服务端/etc/ntp.conf 文件
– 编辑/etc/ntp.conf 文件，添加如下内容，没有ntp服务器，添加自己网段地址
server 0.centos.pool.ntp.org iburst
server 1.centos.pool.ntp.org iburst
server 2.centos.pool.ntp.org iburst
server 3.centos.pool.ntp.org iburst
restrict 10.110.3.0 mask 255.255.255.0 nomodify notrap
– 重启ntp服务
[root@obproxy-node ~]# systemctl restart ntpd
– 查看ntp服务
[root@obproxy-node ~]# ntpq -p
remote refid st t when poll reach delay offset jitter
-a.chl.la 131.188.3.222 2 u 319 1024 275 275.417 6.935 3.770
+pingless.com 36.224.68.195 2 u 478 1024 377 328.498 -2.550 2.018
+ntp6.flashdance 192.36.143.130 2 u 1322 1024 376 276.299 1.376 23.159
*119.28.183.184 100.122.36.196 2 u 122 1024 277 50.314 -1.267 4.023
2）Observer服务器
– root用户，每台Observer服务器都需执行
– yum 安装 ntp服务
[root@oceanbase-dbxxx ~]# yum install ntp ntpdate -y
– 启动服务
[root@oceanbase-dbxxx ~]# systemctl start ntpd
– 设置服务自启
[root@oceanbase-dbxxx ~]# systemctl enable ntpd
– 查看ntp状态
[root@oceanbase-dbxxx ~]# systemctl status ntpd
– 配置客户端/etc/ntp.conf配置文件，每台Observer服务器都需操作
– 编辑/etc/ntp.conf 文件，添加如下内容
server 10.110.3.155 iburst
– 重启ntp服务
[root@oceanbase-dbxxx ~]# systemctl restart ntpd
– 查看ntp服务
[root@oceanbase-dbxxx ~]# ntpq -p
remote refid st t when poll reach delay offset jitter
*obproxy-node 119.28.183.184 3 u 115 1024 377 0.307 3.360 2.080
使用clockdiff 来查看延时
clockdiff 10.110.3.155 要求不超过100ms
–推荐使用chrony配置
判断是否使用 ntpd 同步时间
systemctl status ntpd
Unit ntpd.service could not be found
如果出现上述提示信息，表示没有使用ntpd您可继续进行后续步骤。
如果提示有ntpd 服务，则需卸载ntpd 软件。
安装 chrony 服务
这里采用 YUM 安装方法。您也可以下载相应的 RPM 包安装。
yum -y install chrony
chrony 配置说明
chrony 服务守护进程名为 chronyd ， chronyc 是用来监控 chronyd 性能和配置参数的命令行工具。 chrony 的主配置
文件为 /etc/chrony.conf 。配置方法如下：
vi /etc/chrony.conf
server 后面跟时间同步服务器
使用 pool.ntp.org 项目中的公共服务器。按 server 配置，理论上您想添加多少时间服务器都可以。
或者使用 阿里云的 ntp 服务器
Please consider joining the pool (http://www.pool.ntp.org/join.html).
server ntp.cloud.aliyuncs.com minpoll 4 maxpoll 10 iburst
server ntp.aliyun.com minpoll 4 maxpoll 10 iburst
server ntp1.aliyun.com minpoll 4 maxpoll 10 iburst
server ntp1.cloud.aliyuncs.com minpoll 4 maxpoll 10 iburst
server ntp10.cloud.aliyuncs.com minpoll 4 maxpoll 10 iburst
如果是测试环境，没有时间同步服务器，那就选取一台配置为时间同步服务器。
如果选中的是本机，则取消下面 server 注释
server 127.127.1.0
根据实际时间计算出服务器增减时间的比率，然后记录到一个文件中，在系统重启后为系统做出最佳时间补偿调整。
driftfile /var/lib/chrony/drift
chronyd 根据需求减慢或加速时间调整，
在某些情况下系统时钟可能漂移过快，导致时间调整用时过长。
该指令强制 chronyd 调整时期，大于某个阀值时步进调整系统时钟。
只有在因 chronyd 启动时间超过指定的限制时（可使用负值来禁用限制）没有更多时钟更新时才生效。
makestep 1.0 3
将启用一个内核模式，在该模式中，系统时间每 11 分钟会拷贝到实时时钟（RTC）。
rtcsync
Enable hardware timestamping on all interfaces that support it.
通过使用 hwtimestamp 指令启用硬件时间戳
#hwtimestamp eth0
#hwtimestamp eth1
#hwtimestamp *
Increase the minimum number of selectable sources required to adjust
the system clock.
#minsources 2
指定一台主机、子网，或者网络以允许或拒绝 NTP 连接到扮演时钟服务器的机器
allow 192.168.0.0/16
#deny 192.168/16
即使没有同步到时间源，也要服务时间
local stratum 10
指定包含 NTP 验证密钥的文件。
#keyfile /etc/chrony.keys
指定日志文件的目录。
logdir /var/log/chrony
Select which information is logged.
#log measurements statistics tracking
最简单的配置文件如下：
server 127.127.1.0
allow 172.20.0.0/16
local stratum 10
常用命令
使用 chrony 时间服务是为了保证 OceanBase 集群各个节点时间尽可能同步，下面这些命令供参考。具体使用请
查看 chrony 官方使用说明：Chronyc Frequently Asked Questions。
#开启时间同步服务
systemctl start chronyd
systemctl daemon-reload
systemctl --all
查看时间同步活动
chronyc activity
查看时间服务器
chronyc sources
查看同步状态
chronyc sources -v
校准时间服务器：
chronyc tracking
说明
使用 clockdiff 命令可以检查本机跟目标机器的时间同步误差，以该命令结果为准。
clockdiff 127.127.1.0
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
简要配置：
fs.aio-max-nr = 1048576
fs.file-max = 76724600
kernel.sem = 4096 2147483647 2147483646 512000
#kernel.shmmax/4096
kernel.shmall = 262144
#配置为1G内存时
kernel.shmmax = 1073741824
kernel.shmmni = 819200
net.core.netdev_max_backlog = 10000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 4194304
net.core.somaxconn = 4096
net.ipv4.tcp_fin_timeout = 5
vm.dirty_background_bytes = 409600000
vm.dirty_expire_centisecs = 3000
vm.dirty_ratio = 80
vm.dirty_writeback_centisecs = 50
vm.overcommit_memory = 1
vm.swappiness = 0
net.ipv4.ip_local_port_range = 40000 65535
vm.nr_hugepages = 0
fs.nr_open=20480000
#PG使用hugepage计算问题
对于重度依赖于内存的应用数据库而言，hugepage是经常会被使用的。例如在Oracle中我们一般建议当SGA大于8GB时使用hugepage，而在postgresql中我们也建议当内存较大的时候开启hugepage。
为什么呢？这是因为Linux需要维护虚拟内存地址与物理内存的映射关系，为了提升转换性能，最好这部分能够cache在cpu的cache里面。页越大，映射表就越小。使用huge page可以减少页表大小。
接下来我们来看看pg中hugepage的设置方法。
设置方法：
1、首先我们需要看看数据库使用了多少内存。
获得PID
$ head -1 PGDATA/postmaster.pid
4170
计算数据库启动用了多少内存，指定进程ID
pmap 4170 | awk ‘/rw-s/ && /zero/ {print $2}’
6490428K
2、计算需要使用多少hugepage
查看hugepage页大小
grep ^Hugepagesize /proc/meminfo
Hugepagesize: 2048 kB
计算需要多少hugepage（或者直接使用shared_buffer/hugepage页大小）
6490428 / 2048 = 3169.15
一般建议设置大于内存数，因为这里我们取整为：3170
3、分配hugepage
vi /etc/sysctl.conf
vm.nr_hugepages = 3170
sysctl -p
4、设置数据库参数
huge_pages = on # 或者try
shared_buffers = 64GB # 使用64G内存
5、重启数据库并查看hugepage使用情况
cat /proc/meminfo |grep -i huge
AnonHugePages: 6144 kB
HugePages_Total: 3170 ## 设置的HUGE PAGE
HugePages_Free: 3170 ## 这个是当前剩余的，但是实际上真正可用的并没有这么多，因为被PG锁定了3170个大页
HugePages_Rsvd: 3170 ## 启动PG时申请的HUGE PAGE
HugePages_Surp: 0
Hugepagesize: 2048 kB ## 当前大页2M
注意事项：
1、当配置参数huge_pages设置为on时，若PG启动时需要注册的共享内存大于操作系统提供的Huge Page大小时，数据库将无法启动。推荐将huge_pages参数设置为try，在此种场景下，PostMaster将会改为申请普通内存。
2、如果连接数较少时，使用HUGE PAGE性能不如不使用。因此我们可以尽量使用连接池，减少连接数，提升性能。
执行命令使参数生效
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. 主备互信
创建用户和用户组
groupadd dba -g 2000
useradd postgres -g 2000 -u 2000
id postgres
uid=2000(postgres) gid=2000(dba) groups=2000(dba)
#设置密码
echo “postgres” | passwd --stdin postgres
数据库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}
– 一些依赖包
yum install -y cmake make gcc zlib zlib-devel gcc-c++ perl readline readline-devel
python36 tcl openssl ncurses-devel openldap pam flex
2.2.2. 配置环境变量
原理上讲，“登陆shell”启动时会加载“profile”系列的startup文件/.bash_profile，而“交互式非登陆shell”启动时会加载“rc”系列的startup文件,/.bashrc。
su - postgres
vi ~/.bashrc ~/.bash_profile 两个文件都添加
export PGHOME=/pg/pghome
export PGDATA=/pgdata/pg14
export PGPORT=5432
export PGDATABASE=postgres
export LD_LIBRARY_PATH=LD_LIBRARY_PATH
export PATH=PATH
source .bashrc
2.2.3. 源码安装
详细安装步骤参考：PostgreSQL的源码安装
tar zxvf postgresql-14.2.tar.gz
cd postgresql-14.2
实际加–with-ossp-uuid报错，只./configure --prefix=/pg/pghome即可
./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’
full_page_writes = on
#wal_keep_segments=10 旧版本,13以后使用wal_keep_size
wal_keep_size=512 单位MB
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参数
实现同步复制主要是在主库上设置postgresql.conf配置参数synchronous_standby_names;
这个参数指定多个Standby的名称,各个名称通过逗号分隔，而Standby名称是在Standby连接到主库时，由连接参数"application_name"指定的。
配置参数为备库的主库连接信息中的application_name,也可以是主机名/etc/hosts
primary_conninfo = ‘application_name=pgdb02 host=192.168.56.101 port=5432 user=replicator password=replicator’
案例场景：一主两备环境（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’;”
alter user postgres password ‘postgres’;
psql -h 192.168.1.31 -U postgres -p 5432 password=postgres
2.3.4 备库配置
注意：
实验前首先要清除/pgdata/pg14目录下所有文件，/pgdata/pgwal/archive_wals目录下所有文件。
pg_basebackup工具的具体使用参考：PostgreSQL的物理备份
在备库上使用 pg_basebackup 从主库同步数据
pg_basebackup -D $PGDATA -Fp -Xs -v -P -h 192.168.56.101 -p 5432 -U replicator
-R --write-recovery-conf ,自动生成恢复信息
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. 主备切换
在流复制激活备库前如果不先关闭主库，这时老的主库不能直接切换成备库角色，此时
只能重做备库，如果数据库很大，重做备库的时间将很长。PostgreSQL 提供 pg_rewind 工
具，当激活主库时如果忘记关闭老的主库，可以使用这个工具重新同步新主库的数据，此工
具和 pg_basebackup 主要差异是 pg_rewind 不是全量从主库同步数据，而是只复制变化的数据。
1.首先将备库提升为主库
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
查看是否切换成主库
select pg_is_in_recovery();
查看当前库的时间线
pg_controldata | grep TimeLineID
3.#模拟原备库已经切换为主库，新主库写入数据
验证备节点并使主备库数据块发生偏离
create table repl_t1(id int4);
insert into repl_t1(id) select from generate_series(1,10);
4.# 原主库操作：此时原主库和原备库已经失联，关闭原主库，模拟原主库故障
pg_ctl stop
5.原主库添加新主库连接串
原主库转备库（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’
6.原主库添加触发文件
vi standby.signal
standby_mode = on # async：表示备库为异步同步方式。potential：表示备库当前为异步同步方式，如果当前的同步备库宕机后，异步备库可升级成为同步备库。sync：当前备库为同步方式。quorum：表示备库为quorum standbys的候选
7.原主库重新启动报错
原主库操作：主备库时间线偏离，主库此时启动会报错，无法将原主库以备库模式正常启动，可用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 password=postgres’ --restore-target-wal --write-recovery-conf -P
–restore-target-wal
在进行pg_rewind恢复时，新主库(源库)pg_wal目录下的wal文件可能因为某些原因不存在，因此会出现下面的报错提示：
pg_rewind: error: could not open file “/var/lib/pgsql/data/pg_wal/000000010000000000000002”: No such file or directory
当出现这种情况时PostgreSQL可以使用restore_command参数配置的命令来获取所需的WAL文件。restore_target-wal依赖于restore_command，当需要的wal日志在新主库的pg_wal目录中不存在时，通过restore_command命令将新主库archive的wal日志拷回到pg_wal的目录中去，并且传回到新备库。
pg_rewind: connected to server
pg_rewind: servers diverged at WAL location 0/4A000000 on timeline 7
pg_rewind: rewinding from last common checkpoint at 0/490000C8 on timeline 7
pg_rewind: reading source file list
pg_rewind: reading target file list
pg_rewind: reading WAL in target
pg_rewind: need to copy 167 MB (total source directory size is 199 MB)
171560/171560 kB (100%) copied
pg_rewind: creating backup label and updating control file
pg_rewind: syncing target data directory
pg_rewind: Done!
–write-recovery-conf 在postgresql.auto.conf中生成primary_conninfo语句
primary_conninfo = ‘user=postgres password=postgres channel_binding=disable host=192.168.1.31 port=5432 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any’
8.#新主库postgresql.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’
#主库更新配置
pg_ctl reload
启动备库，观察同步情况
- 延迟应用
使备库与主库永远保持指定的时间延迟
必须配置为异步复制，否则主库将等待
在备库中配置：
recovery_min_apply_delay=xxoo --毫秒 - 多备库场景
9.0开始支持1+n的异步流复制.
9.1支持1+1+n的同步和异步流复制
9.2开始支持级联流复制
9.3开始支持跨平台的流复制协议
9.3开始流复制协议增加了时间线文件传输的协议, 支持自动切换时间线
9.4可以使用流复制做增量数据同步，所以停机服务时间会非常短
文章知识点与官方知识档案匹配，可进一步学习相关知识
PostgreSQL技能树集群和复制standby5833 人正在系统学习中
清除wal命令：pg_resetwal $PGDATA
实验1
1.原备库切成新主库，增加表test3
2.原主库关闭
3.原主库pg_rewind后启动，成为新备库
4.原主库新增test3
5.主从重新建立
实验2
1.原备库切成新主库，增加表test3
2.原主库仍然读写，增加表test4
3.原主库pg_rewind后启动，成为新备库
4.原主库test4消失,新增test3
5.主从重新建立
