PostgreSQL Stream Replication

Principle mechanism

Reference--https://yq.aliyun.com/articles/51009

Primary and standby overall structure

The core part of PG Master and Standby stream replication consists of walsender,walreceiver and startup three processes.

• The Walsender process is used to send the Wal log records
• The walreceiver process is used to receive the Wal log records
• The startup process is used to apply the log

Configuring the Environment

Host name    IP address    role    Data directory postgres202    192.168.  1.202    Primary    /home/postgres/datapostgres203    192.168.  1.203    Standby    /home/postgres/data

Synchronization type

1. Async mode
2. Synchronization mode

"Async Mode Configuration"

Primary Database Configuration1. Host 192.168.1.2021.1first, you need to configure an account for master-slave synchronization. Create replica user settings password, login and backup permissions. CREATE ROLE Replica Login replication encrypted password'Replica'1.2Modify the Pg_hba.conf, add replica user, synchronize. Host replication Replica192.168.1.0/ -MD5 this way, the replica is set to the user from 192.168.1.0 The corresponding network segment for the stream replication request. 1.3Modify PostgreSQL.conf, note the next few places: Wal_level= Hot_standby#This is the host that sets the main as WalMax_wal_senders =5       #This setting can have up to a few stream replication connections, almost a few from, to set a fewWal_keep_segments = -   #set the maximum number of xlog that stream replication retainsWal_sender_timeout = 60s#to set the time-out period for streaming replication host to send dataMax_connections = -     #This setting should be noted that the max_connections from the library must be larger than the main library1.4Restarting the Main library2.Standby Database Configuration host 192.168.1.2032.1use Pg_basebackup to build a library pg_basebackup-F P--progress-d/home/postgres/data-h192.168.1.202-P5432-U Replica--Password used the pg_basebackup command here,/home/postgres/after the data directory is empty, you can see that the existing files in this directory are the same. 2.2Go to the/home/postgres/data directory and modify the recovery.conf, just change a few places. Standby_mode= On#This indicates that the machine is from the libraryPrimary_conninfo ='host=192.168.1.202 port=5432 User=replica password=replica'  #This indicates that this machine corresponds to the main library informationRecovery_target_timeline ='Latest' #This indicates that this stream replication is synchronized to the latest data2.3PostgreSQL.There are also several places to make changes in conf max_connections= $              #generally Chado to write applications the maximum number of connections from the library is largerHot_standby = On#This machine is not only used for data archiving, but also for data queryMax_standby_streaming_delay = 30s#maximum delay time for data stream backupsWal_receiver_status_interval = 10s#How often do you report to the main state from the state, of course, from each data copy will report the status to the main, here just set the maximum interval timeHot_standby_feedback = On#whether to give feedback to the master if there are incorrect data replication2.4boot the main library to view the configuration results Postgres=#select * from Pg_stat_replication;-[RECORD1]----+------------------------------PID|1793Usesysid|24576Usename|Replicaapplication_name|walreceiverclient_addr|192.168.1.203Client_hostname|Client_port|32250Backend_start| .- the- on  A: at:16.924496+ ,Backend_xmin|1676 State|streamingsent_location|0/60003E0write_location|0/60003E0flush_location|0/60003E0replay_location|0/60003E0sync_priority|0sync_state| Async

"Asynchronous change to synchronous mode"

1.master Database Configuration host 192.168.1.2021.1Modify PostgreSQL.Confsynchronous_standby_names='standby01'2.Standby Database Configuration host 192.168.1.2032.2VI recovery.conf Standby_mode=Onprimary_conninfo='application_name=standby01 host=192.168.1.202 port=5432 user=replica password=replica'Recovery_target_timeline='latest3. Restart Master Repository "Main Library" view postgres=# select * from Pg_stat_replication;-[RECORD 1]----+---------------------------- PID | 1706usesysid | 24576usename | Replicaapplication_name | standby01client_addr | 192.168.1.203client_hostname | Client_port | 59550backend_start | 2017-09-01 23:36:05.7176+08backend_xmin | 1676state | streamingsent_location | 0/70000d0write_location | 0/70000d0flush_location | 0/70000d0replay_location | 0/70000d0sync_priority | 1sync_state | Sync

PostgreSQL Stream Replication