--异步流复制，事务提交时不需要等待备库接收并写入wal日志便返回成功。--postgresql.conf添加以下参数wal_level=replicaarchive_mode=onarchive_command='/bin/date'max_wal_senders=10##maxnumberofwalsenderprocesseswal_keep_segments=512##inlogfilesegments,16MBeach;0disableshot_standby=on--pg_hab.conf#replicationprivilegehostreplicationrepuser192.168.8.81/32md5hostreplicationrepuser192.168.8.25/32md5--createusercreateuserrepuserreplicationloginconnectionlimit5encryptedpassword're12a345';--startbackupselectpg_start_backup('fancs_bk1');tarczvfpg_root.tar.gzdata--exclude=data/pg_walscppg_root.tar.gzpostgres@192.168.8.25:/pgdata--node2tarxvfpg_root.tar.gzmkdirpg_walselectpg_stop_backup();--node2cp/pgdata/pgsql/share/postgresql/recovery.conf.sample$PGDATA/recovery.confrecovery_target_timeline='latest'standby_mode=onprimary_conninfo='host=192.168.8.81port=5432user=repuser'--编写密码文件，免密码登录[postgres@mystandby~]$touch.pgpass[postgres@mystandby~]$chmod0600.pgpass[postgres@mystandby~]$cat.pgpass192.168.8.81:5432:replication:repuser:re12a345192.168.8.25:5432:replication:repuser:re12a345--查看进程postgres49024838015:14?00:00:00postgres:walsenderprocessrepuser192.168.8.25(30137)streaming0/B000140postgres56705668015:14?00:00:00postgres:walreceiverprocessstreaming0/B000140--测试createtablet7(idint4,nametext);insertintot7values(1,'firsouler');select*fromt7;--查看流复制同步方式selectusename,application_name,client_addr,sync_statefrompg_stat_replication;--同步流复制，需要等待备库接收wal日志，增加了事务响应时间--postgresql.conf单实例环境synchronous_commit#on表示提交事务时需要等待本地wal写入wal日志后才向客户端返回成功，安全，性能损耗#off可能数据丢失，提高性能#local与on类似--流复制环境#remote_write等待备库写入系统缓存中#on备库写入wal日志#remote_apply备库完成重做--recovery.confnode2备库别名primary_conninfo='host=192.168.8.25port=5432user=repuserapplication_name=node2'--node1设置以下参数synchronous_commit=onsynchronous_standby_names='node2'--同步流复制，备库宕机，主库一直等待，不建议同步流复制--性能测试，并发跟cpu数量有关系，性能方面--测试脚本createtabletest_per1(idint4,nametext,create_timetimestamp()withouttimezonedefaultclock_timestamp());insertintotest_per1(id,name)selectn,n||'_per1'fromgenerate_series(1,10000000)n;altertabletest_per1addprimarykey(id);--select脚本\setv_idrandom(1,1000000)selectnamefromtest_per1whereid=:v_id;--写测试\setv_idrandom(1,1000000)updatetest_per2setflag='1'whereid=:v_id;--读测试，单实例最佳，异步流复制次之，写测试，单实例与异步差异不明显，同步流复制慢pgbench-c2-T120-dpostgres-Upostgres-nN-Mprepared-fupdate_per2.sql>update_2.out2>&1&--流复制监控select*frompg_stat_replication;--主备延迟write_lag主库wal落盘，等待备库接收wal日志，(操作系统缓存中)并返回确认信息；flush_lag（已写入wal日志，但没应用）；replay_lag(已应用）selectpid,usename,client_addr,state,write_lag,flush_lag,replay_lagfrompg_stat_replication;--replay_lag>flush_lag>write_lag--10之前的版本selectextract(secondfromnow()-pg_last_xact_replay_timestamp());--通过流复制wal日志应用延迟衡量返回字节数selectpid,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_delayfrompg_stat_replication;--接收进程相关试图select*frompg_stat_wal_receiver;--备库，恢复进程是否处于恢复模式selectpg_is_in_recovery();--备库最近接收的wal位置selectpg_last_wal_receive_lsn();--备库最近应用的wal日志selectpg_last_wal_replay_lsn();--备库最近事务的应用时间selectpg_last_xact_replay_timestamp();--主库wal当前写入位置selectpg_current_wal_lsn();--计算两个wal日志位置的偏移量selectpg_wal_lsn_diff('','');