流复制
主库配置
根据实际情况分配流复制权限
vi pg_hba.conf
host replication all 10.2.0.0/0 trust
vi postgresql.conf
max_wal_senders = 10
wal_level = logical # minimal, replica, or logical
hot_standby = on # 正常在从库配置，如果在主库配置完毕，因为从库复制主库配置不需要再修改从库配置。
wal_log_hints = on
从库配置
数据库安装
从主库复制数据
pg_basebackup -h 10.2.0.14 -U postgres -F p -P -R -D /var/lib/pgsql/10/data/ --checkpoint=fast -l postgresback20181219
pg_basebackup支持两种全量备份的方式，
-
以fetch的方式，先备份数据在备份日志
-
以stream的方式，并行的备份数据和日志
pg_basebackup对于全量备份的数据和日志，提供了串行备份和并行备份的方式。fetch模式也就是串行备份需要保证在备份数据的过程中，备份开始时刻的日志需要一直保存下来， 也就说pg的wal_keep_segments需要足够大去保存日志文件，如果备份数据期间，日志开始时刻的日志已经被移除，那么备份就会失败。而stream模式，也就是并行备份过程中wal_max_sender必须保证不小于2。 而stream模式不支持，将数据和日志以流的方式输出到标准输出
限速，在生产系统中防止对正常业务的影响
-r, --max-rate=RATE maximum transfer rate to transfer data directory
(in kB/s, or use suffix "k" or "M")
注意新拷贝数据的权限
chown postgres:postgres data/ -R
chmod 0700 data
查看数据
ll data
总用量 88
-rw-------. 1 postgres postgres 203 12月 19 13:45 backup_label.old
drwx------. 7 postgres postgres 67 12月 19 14:12 base
-rw-------. 1 postgres postgres 44 12月 19 14:28 current_logfiles
drwx------. 2 postgres postgres 4096 12月 19 14:28 global
drwx------. 2 postgres postgres 4096 12月 19 14:28 log
drwx------. 2 postgres postgres 6 12月 19 13:45 pg_commit_ts
drwx------. 2 postgres postgres 6 12月 19 13:45 pg_dynshmem
-rw-------. 1 postgres postgres 4340 12月 19 13:45 pg_hba.conf
-rw-------. 1 postgres postgres 1636 12月 19 13:45 pg_ident.conf
drwx------. 4 postgres postgres 68 12月 19 15:08 pg_logical
drwx------. 4 postgres postgres 36 12月 19 13:45 pg_multixact
drwx------. 2 postgres postgres 18 12月 19 14:28 pg_notify
drwx------. 2 postgres postgres 6 12月 19 13:45 pg_replslot
drwx------. 2 postgres postgres 6 12月 19 13:45 pg_serial
drwx------. 2 postgres postgres 6 12月 19 13:45 pg_snapshots
drwx------. 2 postgres postgres 6 12月 19 14:28 pg_stat
drwx------. 2 postgres postgres 6 12月 19 13:45 pg_stat_tmp
drwx------. 2 postgres postgres 18 12月 19 13:45 pg_subtrans
drwx------. 2 postgres postgres 6 12月 19 13:45 pg_tblspc
drwx------. 2 postgres postgres 6 12月 19 13:45 pg_twophase
-rw-------. 1 postgres postgres 3 12月 19 13:45 PG_VERSION
drwx------. 3 postgres postgres 12288 12月 19 15:09 pg_wal
drwx------. 2 postgres postgres 18 12月 19 13:45 pg_xact
drwx------. 3 postgres postgres 17 12月 19 13:45 pipeline
-rw-------. 1 postgres postgres 88 12月 19 13:45 postgresql.auto.conf
-rw-------. 1 postgres postgres 22748 12月 19 14:28 postgresql.conf
-rw-------. 1 postgres postgres 58 12月 19 14:28 postmaster.opts
-rw-------. 1 postgres postgres 95 12月 19 14:28 postmaster.pid
-rw-r--r--. 1 postgres postgres 262 12月 19 13:45 recovery.conf
查看 recovery.conf
cat recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=postgres host=10.2.0.14 port=5432 sslmode=disable sslcompression=1'
从库配置
启动从库
systemctl start postgresql-10.service
systemctl enable postgresql-10.service
#recovery.conf
recovery_target_timeline='latest'
recovery_min_apply_delay = 5min #延迟多少分钟应用
trigger_file = '/home/postgres.trigger' #从库变主库时应用
验证
主库创建一个数据库
psql -U postgres
postgres=# create database testreplication;
从库查看结果
psql -U postgres
postgres=#\l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-------------------+----------------+----------+-------------+-------------+-----------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testreplication | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 行记录)
状态查看
主库 查看同步状态
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 21092
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr | 10.1.0.15
client_hostname |
client_port | 14703
backend_start | 2018-12-19 14:28:15.220479+08
backend_xmin |
state | streaming
sent_lsn | 2/B1BDC000
write_lsn | 2/B1B3C000
flush_lsn | 2/B1B3C000
replay_lsn | 2/B1B3A3B0
write_lag | 00:44:13.27416
flush_lag | 00:44:13.27416
replay_lag | 00:44:13.274217
sync_priority | 0
sync_state | async
从库 查看wal receiver的统计信息
select * from pg_stat_wal_receiver ;
-[ RECORD 1 ]---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 9329
status | streaming
receive_start_lsn | 0/3000000
receive_start_tli | 1
received_lsn | 2/DA5FC000
received_tli | 1
last_msg_send_time | 2018-12-19 15:25:43.662966+08
last_msg_receipt_time | 2018-12-19 15:25:43.846309+08
latest_end_lsn | A/D09F1758
latest_end_time | 2018-12-19 15:25:40.694523+08
slot_name | node_a_slot
conninfo | user=postgres passfile=/root/.pgpass dbname=replication host=10.1.0.14 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any
-- 暂停WAL的应用，例如要做一些排错时
select pg_wal_replay_pause();
-[ RECORD 1 ]-------+-
pg_wal_replay_pause |
-- 查看状态
select pg_is_wal_replay_paused();
-[ RECORD 1 ]-----------+--
pg_is_wal_replay_paused | t
-- 恢复wal
select pg_wal_replay_resume();
-[ RECORD 1 ]--------+-
pg_wal_replay_resume |
-- 查看状态
select pg_is_wal_replay_paused();
-[ RECORD 1 ]-----------+--
pg_is_wal_replay_paused | f
wal 日志保持时效
PostgreSQL 9.4 新增的一个特性, replication slot, - 可以被流复制的sender节点用于自动识别它xlog中的数据在下面的standby中是否还需要(例如, standby断开连接后, 还未接收到的XLOG), 如果还需要的话, 那么这些XLOG将不会被删除. - 对于tuples, 如果standby 配置了hot_standby_feedback=on, 那么发生冲突的tuples将不会在sender端被vacuum回收. 用于规避冲突.
配置比较简单, 需要在sender端使用函数创建slot, 在receiver端配置对应的slot name即可.
主库：
postgres=# SELECT * FROM pg_create_physical_replication_slot('node_a_slot');
slotname | xlog_position
-------------+---------------
node_a_slot |
postgres=# SELECT * FROM pg_replication_slots;
slot_name | slot_type | datoid | database | active | xmin | restart_lsn
-------------+-----------+--------+----------+--------+------+-------------
node_a_slot | physical | 0 | | f |
从库
cat recovery.conf
primary_slot_name = 'node_a_slot'
流复制协议也做了相应的改进 :
使用slot的好处 :
- 在没有replication slot这个特性以前, 有两种方法来保持standby需要的xlog, wal keep或者归档, 因为主节点不知道standby到底需要哪些XLOG信息, 配置一般需要较大的余量. slot可以解决这个浪费sender端存储wal空间的问题, 因为sender可以做到保留更精准的wal信息.
- 配合standby节点的feedback使用, 可以避免vacuum带来的冲突.
同步复制VS异步复制
PostgreSql的流复制是异步的，缺点是Standby上的数据落后于主库上的数据，如果使用Hot Standby做读写分离，就会存在数据一致性的问题。PostgreSql9.1版本后提供了同步流复制的架构。同步复制的要求在数据写入Standby数据库后，事务commit才返回。 存在问题：当Standby数据出现问题时，会导致主库被hang住。 解决方法：启动两个或两个以上的Standby数据库。
配置方法，在主从配置基础上
主库synchronous_standby_names 参数指定多个Standby的名称，各个名称通过逗号分割，而Standby连接到主库由application_name 参数指定
主库
vi postgresql.conf
synchronous_standby_names = 'standby01,standby02'
从库
cat recovery.conf
standby_mode = 'on'
primary_conninfo = 'application_name=standby02 user=postgres host=10.2.0.14 port=5432 sslmode=disable sslcompression=1'
主库查看结果
psql -U postgres
postgres=# select sync_priority,sync_state from pg_stat_replication ;
sync_priority,sync_priority 状态说明
从变主
简单有效的方式
在从库中配置 cat recovery.conf
trigger_file = '/home/postgres.trigger'
重启从库
将从库变成主库
touch /home/postgres.trigger
pg_ctl promote
pg_ctl promote -D $PGDATA
server promoting
pg_rewind
从库有查询业务时注意事项
1. 如果备库有LONG query，同时需要实时性，可以设置hot_standby_feedback=on，同时建议将主库的autovacuum_naptime，autovacuum_vacuum_scale_factor设置为较大值（例如60秒，0.1），
主库的垃圾回收唤醒间隔会长一点，如果突然产生很多垃圾，可能会造成一定的膨胀。
2. 如果备库有LONG QUERY，并且没有很高的实时性要求，建议设置设置hot_standby_feedback=off, 同时设置较大的max_standby_archive_delay， max_standby_streaming_delay。
扩展阅读
-
PostgreSQL 10加入了quorum based的同步复制功能，用户可以配置若干standby节点，并配置需要将WAL发送多少份才返回给客户端事务结束的消息。
冲突及解决
冲突
FATAL: terminating connection due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
原因
报错是备库事务或者单SQL查询时间长，和备库的日志apply发生冲突，如果业务上有长事务、长查询，主库上又再修改同一行数据，很容易造成备库的wal日志无法apply。
wal无法apply数据库有两个策略：
-
备库告诉主库需要哪些版本，让主库保留，备库查询始终能拿到需要的版本，不阻塞apply，因为备库总能拿到需要的版本
-
备库apply进入等待，直到备库冲突查询结束，继续apply。可以设置超时时间。max_standby_streaming_delay
对应解决方法
方法一
vacuum_defer_cleanup_age > 0
或
hot_standby_feedback=on
代价1，主库膨胀，因为垃圾版本要延迟若干个事务后才能被回收。
代价2，重复扫描垃圾版本，重复耗费垃圾回收进程的CPU资源。（n_dead_tup会一直处于超过垃圾回收阈值的状态，从而autovacuum 不断唤醒worker进行回收动作）。
代价3，如果期间发生大量垃圾，垃圾版本可能会在事务到达并解禁后，爆炸性的被回收，产生大量的WAL日志，从而造成WAL的写IO尖刺。
方法二
max_standby_streaming_delay
max_standby_archive_delay和max_standby_streaming_delay
代价，如果备库的QUERY与APPLY（恢复进程）冲突，那么备库的apply会出现延迟，也许从备库读到的是N秒以前的数据。
12 pg 12 变更
-
recovery.conf 中的配置内容位置变更到postgresql.auto.conf
-
trigger_file -> promte_trigger_file
-
增加 standby.signal recovery.signal
13 虚拟备库
pg_receivewal
备份增量wal日志，并可压缩备份。结合wal-g 做数据备份
主从延迟查看
主库查看/字节大小
#数据延迟
select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn)) as latency from pg_stat_replication ;
#wal 不同阶段延迟
select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn)) as sent_latency, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn)) as write_latency ,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn)) as flush_latency,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn)) as replay_latency from pg_stat_replication ;
从库查看/时间
select now() - pg_last_xact_replay_timestamp();