postgresql 主备

主备
warm standby server: 只接收同步数据，不能提供只读服务的，比如备份数据库
hot standby server: 既同步数据，又可做只读，slave，9.x才有的功能
PITR: Point-in-Time Recovery, 基于时间点的备份，简单的cp数据库文件到另一台机器，这叫基础备份。后续通过WAL日志的备份与基础备份构成完整的备份，不停的重放WAL日志就就可以把数据推到备份结束后的任意一个时间点，这就是PITR
将WAL日志传送到另一个机器的方法有两种
WAL日志归档: 配置将WAL复制到某个地方，由两个参数控制
archive_mode=on 表示打开归档
archive_command=‘cp %p /backup/pgarch/%f’ %p WAL全路径，%f去掉全路径的WAL文件名，配置主备同步就用scp命令就好了
这样主备复制延迟一个WAL文件，所以不适合主备同步
流复制: 9.0开始提供的一种新的传递WAL日志的方法，只要产生日志就传到备库，其中包括同步模式9.1之后和异步模式，9.2后增加了级联复制
手动创建流复制的方法: /u01/ppas1000_20190101/bin/pg_receivewal -h11.239.188.71 -p3004 -Ureplicator -D/home/pgsql/downloads/3020’
创建standby步骤
生成基础备份，把基础备份拷贝到备机上，配置recovery.conf把备库启动在standby模式下
生成基础备份步骤，pg_basebackup命令行工具完成了整个步骤9.1版本开始提供的
超级用户执行 select pg_start_backup(‘lable’)
执行备份 可以压缩如tar
超级用户执行 select pg_stop_backup() 终止备份模式并自动切换到下一个WAL段
拷贝备份过程中产生的增量WAL文件
pg_basebackup使用
使用replication协议连接到数据库实例上，所以pg_hba.conf中要允许热；replication连接
pg_basebackup option
-D directory --pgdata=directory 设置备份到哪个目录，为-表示输出到标准输出以便用管道与其他工具配合
-F format --format=format 指定输出格式，format=p/plain(原样输出) t/tar(压缩为tar)
-z --gzip，与tar连用，压缩用的
-Z level --compress=level gzip的压缩级别，1-9的数字
-x --xlog 备份时同时备份WAL，需要设置wal_keep_segments参数以保证备份过程中WAL日志不会被覆盖
-X f/s --xlog-method=fetch/stream -X f与-x相同意义，s表示备份开始很厚启动另一个流复制连接组库接受WAL日志，避免了覆盖问题，但是需要与主库建立两个连季节，需要主库的max_wal_senders参数>=2
-c fast|spread --checkpoint=fast|spread 设置checkpoint的模式是fast还是会spread
-l label --lable=label 指定备份的一个标识，便于维护，基础备份pg_start_backup参数中的label，在backup_lable文件中记录
-P --progress 允许在备份过程中实时地打印备份的进度，不是百分百精确因为备份过程中还有写入
-R 产生recovery.conf文件，设置为备库需要摄者这个文几件的，意思是备份完就可以直接修改这个文件启动为备库了
-h host --host=host -p port --port=port
-s interval --status-interval=interval: 多长时间向服务器反馈状态，如果配置了流复制超时就需要设置次参数，不然流复制就超时了
-U username --username=username
-w --no-password 不提示输入密码 -W --password 强制输入密码
eg: pg_basebackup -h xxx -u xx -F p -P -x -R -D /hoame/xxx/xxx -l backup_xxx
9.2后有级联复制，从备库备份注意事项
搭建异步复制的hot_standby
主库配置三个参数
在standby机器上生成基础备份 pg_basebackup -h xx -U xx -F p -P -x -R -D xxx -l xx
备库上检查recovery.conf文件
standby_mode=‘on’
primary_conninfo=‘host=10.125.51.225 port=3006 user=replicator password=YUT0hCmiBe27Joxy sslmode=prefer sslcompression=1 krbsrvname=postgres application_name=standby1’
recovery_target_timeline=‘latest’
trigger_file=’/home/pgsql/rm-rdszjk_pg10_test/postgresql.trigger’
修改standby的postgresql.conf 文件中的hot_standby=on
启动standby
同步复制standby (一般是一主多备，防止一个备挂了影响主库)
主库要配置参数synchronous_standby_names 指定standby的名称，这个名称就是standby中recovery.conf文件中primary_conninfo中设置的application_name，主库设置次参数需要reload配置，参数中的顺序决定了谁默认是同步谁是异步，当第一个同步有问题时会自动切换为第二个
如果备库有问题，影响到主库只会影响写不会影响读
流复制监控
select *from pg_stat_replication 主库上 select * from pg_replication_slots; 这个是查看有没有消费log的地方
sent_location: 发送WAL位置
write_location: standby写WAL日志的位置
flush_location: standby写WAL日志刷新到磁盘的位置
replay_location: standby重放WAL日志位置
延迟存在的点，1.主库实际WAL位置和发送位置延迟，2.发送位置和重放位置延迟
查看备库落后主库多少字节的WAL 只能主库上查询
pg9: select pg_xlog_location_diff(pg_current_xlog_location(),replay_location) from pg_stat_replication
pg10: select pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) from pg_stat_replication
将WAL位置转换为偏移量，select * from pg_xlogfile_name_offset(‘位置十六进制’)
备库上查看位置
select pg_is_wal_replay_paused(); 查看wal应用是否暂停了 这个只能在备库上执行
select pg_last_xlog_receive_location() select pg_last_wal_receive_lsn() 查看wal接收到的位点
select pg_last_xlog_replay_location() select pg_last_wal_replay_lsn() 查看wal应用到的位点
select pg_last_xact_replay_timestamp() select pg_last_xact_replay_timestamp() 应用wal最后一个事务执行的时间，这个参数不可以作为延迟时间，因为是执行时间，只能看最后同步时间
备库上操作主备
select pg_wal_replay_pause(); 暂停wal的应用 等于stop slave sql_thread
select pg_is_wal_replay_paused(); 查看是否返回t，t证明暂停了
select pg_wal_replay_resume(); 恢复复制 等于start slave sql_thread
select pg_is_wal_replay_paused(); 查看是否返回f，f证明恢复了
查看当前的xlog:
pg9: select pg_xlogfile_name(pg_current_xlog_location()); select pg_walfile_name(pg_current_wal_location())
pg10:
主库查看同步状态
pg9 没用的，备库pg_stat_replication中没记录，所以下面语句只能在主库执行才有记录
select client_addr, application_name,
case pg_is_in_recovery() when true then
pg_xlog_location_diff(pg_last_xlog_replay_location(), flush_location)
else
pg_xlog_location_diff(pg_current_xlog_location(), flush_location)
end
from pg_stat_replication;

pg10
select client_addr, application_name,
case pg_is_in_recovery() when true then
pg_wal_lsn_diff(pg_last_wal_replay_lsn(), flush_lsn)
else
pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)
end
from pg_stat_replication;

select client_addr, application_name, sync_state, state,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) as sent_delay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) as write_delay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) as flush_delay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_delay
from pg_stat_replication;
pg_size_pretty 自动把数字转换为了KB MB 需要自己做判断
sent_lsn: 发送到备库的上个事务日志位置
write_lsn: 发送到备库并写入到磁盘的上个事务日志位置
flush_lsn: 发送备库并写入磁盘且刷新到磁盘的上个事务日志位置
replay_lsn: 备库上应用到数据库的上个事务日志位置
``
查看备库状态
select pg_is_in_recovery() : 如果当前是备库返回true，主库返回false
不登录数据库可以用pg_controldata来判断，pg_controldata -D xxx |grep cluster, 主库 in production 备库 in archive recovery
激活备库为主库
1.通过备库recovery.conf中的trigger文件
2.pg_ctrl promote -D datadir 此时备库中recovery.conf会变为recovery.done
主库降级为备库
修改recovery.conf 设置host和port 重启
如果发现new timeline 2 forked off current database system timeline 1 before current recovery point 说明时间线不同了需要修复时间线
先停止服务
pg_rewind /u01/pgsql/bin/pg_rewind --target-pgdata=/u01/pg3306/data --source-server=‘host=10.125.50.199 port=3306 user=pgsql dbname=postgres password=pgsql’
执行pg_rewind需要wal_log_hints 设置成 on 或者 PG 在初始化时开启 checksums 功能
流复制注意事项
wal_keep_segments: 主库的wal日志会回卷覆盖，默认这个参数为0，就是不特意保存wal，当打开流复制时一定要设置这个参数，防止主备延迟日志覆盖导致备库永远跟不上主库，这个值是个数字，表是保留多少个wal，每个wal默认16M，所以16*wal_keep_segments就是日志最大占用磁盘空间
vacuum_defer_cleanup_age: 主库的清理进程vacuum会定时清理那些旧版本(删除标记变更标记)的数据，因为他知道这些数据主库已经不需要了，但是不知道备库还需不需要，可以设置这个参数代表延迟多少个事务清理，让主库延迟清理
注意
pg变更参数会检查主备参数一致性(max_connections, max_worker_processes, max_prepared_transactions)，如果备库的比主库小，则备库拉不起来

原文链接：https://blog.csdn.net/zhangjikuan/article/details/88670871