【PG流複製】Postgresql流複製部署過程及效能測試

--非同步流複製 ，事務提交時不需要等待備庫接收並寫入wal日誌便返回成功。
--postgresql.conf  新增以下引數
wal_level=replica
archive_mode=on
archive_command='/bin/date'
max_wal_senders=10         ##max number of walsender processes
wal_keep_segments=512      ##in logfile segments,16MB each; 0 disables
hot_standby=on
--pg_hab.conf
#replication privilege
host	replication		repuser		192.168.8.81/32		md5
host	replication		repuser		192.168.8.25/32		md5
--create user
create user repuser replication login connection limit 5 encrypted password 're12a345';
--start backup
select pg_start_backup('fancs_bk1');
tar czvf pg_root.tar.gz data --exclude=data/pg_wal
scp pg_root.tar.gz postgres@192.168.8.25:/pgdata
--node2
tar xvf pg_root.tar.gz
mkdir pg_wal
select pg_stop_backup();
--node2
cp /pgdata/pgsql/share/postgresql/recovery.conf.sample  $PGDATA/recovery.conf
recovery_target_timeline='latest'
standby_mode=on
primary_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:re12a345
192.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/B000140
postgres   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=on
synchronous_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('','');