【PG流复制】Postgresql流复制部署过程及性能测试

--异步流复制 ，事务提交时不需要等待备库接收并写入wal日志便返回成功。--postgresql.conf  添加以下参数wal_level=replicaarchive_mode=onarchive_command='/bin/date'max_wal_senders=10         ##max number of walsender processeswal_keep_segments=512      ##in logfile segments,16MB each; 0 disableshot_standby=on--pg_hab.conf#replication privilegehost    replication             repuser         192.168.8.81/32         md5host    replication             repuser         192.168.8.25/32         md5--create usercreate user repuser replication login connection limit 5 encrypted password 're12a345';--start backupselect pg_start_backup('fancs_bk1');tar czvf pg_root.tar.gz data --exclude=data/pg_walscp pg_root.tar.gz postgres@192.168.8.25:/pgdata--node2tar xvf pg_root.tar.gzmkdir pg_walselect pg_stop_backup();--node2cp /pgdata/pgsql/share/postgresql/recovery.conf.sample  $PGDATA/recovery.confrecovery_target_timeline='latest'standby_mode=onprimary_conninfo='host=192.168.8.81 port=5432 user=repuser'--编写密码文件，免密码登录[postgres@mystandby ~]$ touch .pgpass[postgres@mystandby ~]$ chmod 0600 .pgpass [postgres@mystandby ~]$ cat .pgpass 192.168.8.81:5432:replication:repuser:re12a345192.168.8.25:5432:replication:repuser:re12a345--查看进程postgres   4902   4838  0 15:14 ?        00:00:00 postgres: wal sender process repuser 192.168.8.25(30137) streaming 0/B000140postgres   5670   5668  0 15:14 ?        00:00:00 postgres: wal receiver process   streaming 0/B000140--测试create table t7 (id int4,name text);insert into t7 values(1,'firsouler');select * from t7;--查看流复制同步方式select usename,application_name,client_addr,sync_state from pg_stat_replication;--同步流复制，需要等待备库接收wal日志，增加了事务响应时间--postgresql.conf    单实例环境synchronous_commit   #on 表示提交事务时需要等待本地wal写入wal日志后才向客户端返回成功，安全，性能损耗                                         #off 可能数据丢失，提高性能                                         #local 与on类似                                         --流复制环境                                         #remote_write  等待备库写入系统缓存中                                         # on 备库写入wal日志                                         #remote_apply 备库完成重做                                         --recovery.conf  node2  备库别名primary_conninfo='host=192.168.8.25 port=5432 user=repuser application_name=node2'--node1 设置以下参数synchronous_commit=onsynchronous_standby_names='node2'--同步流复制，备库宕机，主库一直等待， 不建议同步流复制--性能测试，并发 跟cpu数量有关系，性能方面--测试脚本create table test_per1(id int4,name text,create_time timestamp() without time zone default clock_timestamp());insert into test_per1(id,name) select n,n||'_per1' from generate_series(1,10000000) n;alter table test_per1 add primary key(id);--select 脚本\set v_id random(1,1000000)select name from test_per1 where id=:v_id;--写测试\set v_id random(1,1000000)update test_per2 set flag='1' where id=:v_id;--读测试，单实例最佳，异步流复制次之，写测试，单实例与异步差异不明显，同步流复制慢pgbench -c 2 -T 120 -d postgres -U postgres -n N -M prepared -f update_per2.sql > update_2.out 2>&1 &--流复制监控select * from pg_stat_replication;--主备延迟 write_lag 主库wal落盘，等待备库接收wal日志，(操作系统缓存中)并返回确认信息；flush_lag（已写入wal日志，但没应用）；replay_lag(已应用）select pid,usename,client_addr,state,write_lag,flush_lag,replay_lag from pg_stat_replication;--replay_lag>flush_lag>write_lag--10之前的版本select extract(second from now()-pg_last_xact_replay_timestamp());--通过流复制wal日志应用延迟衡量 返回字节数select pid,usename,client_addr,state,pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn) write_delay,pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn) flush_delay,pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) replay_delay from pg_stat_replication;--接收进程相关试图select * from pg_stat_wal_receiver;--备库，恢复进程是否处于恢复模式select pg_is_in_recovery();--备库最近接收的wal位置select pg_last_wal_receive_lsn();--备库最近应用的wal日志select pg_last_wal_replay_lsn();--备库最近事务的应用时间select pg_last_xact_replay_timestamp();--主库wal当前写入位置select pg_current_wal_lsn();--计算两个wal日志位置的偏移量select pg_wal_lsn_diff('','');