Shulou(Shulou.com)06/01 Report--

-- Asynchronous flow replication. When a transaction is committed, it does not need to wait for the standby database to receive it and write to the wal log to return success. -- postgresql.conf adds the following parameter 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 md5host replication repuser 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'-- to write password files Password-free login [postgres@mystandby ~] $touch .pgpass [postgres@mystandby ~] $chmod 0600. Pgpass [postgres@mystandby ~] $cat .pgpass 192.168.81 wal sender process repuser 5432 streaming 0/B000140postgres replicationre12a345192.168.25 postgres@mystandby 5432 replicationflerre12a345 color-View process postgres 4902 4838 0 15:14? 00:00:00 postgres: wal sender process repuser 192.168.25 (30137) streaming 0/B000140postgres 5670 5668 0 15:14? 00:00:00 postgres: wal receiver process streaming 0amp B000140Mui-Test create table T7 (id int4 Name text) Insert into T7 values; select * from T7 Mattel-View streaming replication synchronization mode select usename,application_name,client_addr,sync_state from pg_stat_replication -- synchronous stream replication requires waiting for the standby database to receive wal logs, which increases transaction response time-- postgresql.conf single instance environment synchronous_commit # on indicates that when committing a transaction, you need to wait for the local wal to write the wal log before returning success to the client. Security, performance loss # off may lose data. Improve performance # local is similar to on-streaming replication environment # remote_write waiting for backup database to be written to the system cache # on backup Library writes wal log # remote_apply slave library completes redo-- recovery.conf node2 standby alias primary_conninfo='host=192.168.8.25 port=5432 user=repuser application_name=node2'--node1 sets the following parameters synchronous_commit=onsynchronous_standby_names='node2'-- synchronous stream replication The slave database is down and the master database is waiting. Synchronous stream replication is not recommended. Performance testing is related to the number of cpu. Performance aspect-test script 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 | |'_ per1' from generate_series (1m 10000000) n alter table test_per1 add primary key (id);-- select script\ set v_id random (1m 1000000) select name from test_per1 where id=:v_id;-- write test\ set v_id random (1m 1000000) update test_per2 set flag='1' where id=:v_id -- read test, single instance is the best, asynchronous flow replication takes second place, write test, there is no significant difference between single instance and asynchronous, synchronous stream replication slow pgbench-c 2-T 120-d postgres-U postgres-n N-M prepared-f update_per2.sql > update_2.out 2 > & 1 &-- stream replication monitoring select * from pg_stat_replication -- Master / slave delays the write_lag master library wal to be unloaded, waits for the standby database to receive the wal log (in the operating system cache) and returns a confirmation message Flush_lag (written to the wal log but not applied) Replay_lag (applied) select pid,usename,client_addr,state,write_lag,flush_lag,replay_lag from pg_stat_replication;--replay_lag > flush_lag > write_lag--10 previous version select extract (second from now ()-pg_last_xact_replay_timestamp ()) -measure the number of bytes returned by streaming replication wal log application delay 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 -- the receiving process attempts to select * from pg_stat_wal_receiver;-- to prepare the database, and whether the recovery process is in recovery mode select pg_is_in_recovery ();-- the recently received wal location select pg_last_wal_receive_lsn ();-- the recently applied wal log select pg_last_wal_replay_lsn ();-- the application time of the most recent transactions of the standby database select pg_last_xact_replay_timestamp () -- the current write location of the main library wal select pg_current_wal_lsn ();-- calculate the offset of the two wal log locations select pg_wal_lsn_diff (',')

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.