First, Introduction
PostgreSQL introduces a master-slave stream replication mechanism after 9.0, so-called stream replication is the synchronization of the corresponding data from the server through the TCP stream from the primary server. This will still be backed up from the server when the primary server data is lost.
Stream replication allows updates to be kept from the server, as compared to file-log shipping. Connect the master server from the server, which generates a stream of Wal records to the slave server without waiting for the master to finish writing the Wal file (about Wal can look at the official argument https://www.postgresql.org/docs/10/static/ different-replication-solutions.html).
PostgreSQL stream replication is asynchronous by default. There is a small delay between committing transactions on the primary server and being visible from the server, which is much less than file-log-based, typically 1 seconds. If the primary server crashes suddenly, there may be a small amount of data loss.
Synchronous replication must wait for the primary server and the slave server to finish writing the Wal before committing the transaction. This increases the response time of the transaction to a certain extent.
Configuring synchronous replication requires only one additional configuration step: Synchronous_standby_names must be set to a non-null value. The synchronous_commit must also be set to ON.
Note: The master-slave server is located at the node's system, environment, etc. best consistent. The PostgreSQL version is also best consistent, otherwise there may be a problem.
Second, Installation Deployment (Installation deployment using Ubuntu for normal users)
Role |
IP Address |
System version |
Database version |
Primary server |
192.168.100.71 |
Ubuntu18.04 AMD64 |
PostgreSQL10 |
From the server |
192.168.100.70 |
Ubuntu18.04 AMD64 |
PostgreSQL10 |
Note: Version 18.04 of the default PostgreSQL version is 10, to install other versions can be compiled or apt-get the specified version number installation
1, both servers have to install PostgreSQL
sudo apt-get Updatel
sudo apt-get install PostgreSQL
sudo vi/etc/postgresql/10/main/postgresql.conf
Add or find the line at the end of the file and change the peer to trust
Local all All Trust
Restarting the PostgreSQL service
sudo service PostgreSQL Restartt
So that PostgreSQL is ready to install.
2. Configuring the master server
Switch to Postgres user
sudo su–postgres
Enter the database and create a normal database user for master-slave synchronization (just give login and copy permissions)
postgres=# Create role GUOXM login replication encrypted password ' GUOXM '
And then quit.
postgres=# \q
Exit to the original user at exit
Then, modify the pg_hba.conf of PostgreSQL, configure the GUOXM user you just created to connect
sudo vi/etc/postgresql/10/main/pg_hba.conf
Add the following two lines at the end of the file
host all all 192.168.100.70/32 trust #Run 70 server to connect to this machine
host replication guoxm 192.168.100.70/32 md5 #Run guoxm user to copy the native data on 70
Note: The second role must be a replication
Finally, configure postgresql.conf again
sudo vi/etc/postgresql/10/main/postgresql.conf
This time, we need to configure the host's role
listen_addresses = ‘*’ #listen all IPs
archive_mode = on #Enable archive mode
archive_command = ‘cp% p / var / lib / postgresql / 10 / main /% f’ #Archive command
wal_level = hot_standby #Hot standby mode
max_wal_senders = 1 #Maximum 1 stream replication connection
wal_sender_timeout = 60s #Stream replication timeout
max_connections = 100 #The maximum connection time must be less than the configuration of the slave
After saving exits, restart the database service
sudo service PostgreSQL Restartt
3. Configure the slave server
First, test if you can connect to the primary server
Psql-h 192.168.100.70-u Postgres
If possible, the host configuration is OK, otherwise check the host's pg_hba.conf configuration
Access to Postgres users
sudo su-postgres
First empty the data in the main directory (the main folder is the storage folder for PostgreSQL data)
RM-RF 10/main/*
Or
rm-rf/var/lib/postgresql/10/main/*
Because PostgreSQL's home directory is in the/var/lib/postgresql.
So the above two commands get the same effect.
Then, start copy data from the master server to this machine, this step is called "base backup"
Pg_basebackup-h 192.168.100.71-u guoxm–d 10/main/-X stream–p
Or
Pg_basebackup-f p--progress-d 10/main/-H 192.168.100.71-p 5432-u GUOXM--password
Pg_basebackup is the basic backup tool that comes with PostgreSQL
In this way, the underlying backup is completed
Then, create recovery.conf for recovering data from the main library from the library
VI recovery.conf
Then edit the following configuration
standby_mode = on # indicates that the node is a slave
primary_conninfo = ‘host = 192.168.100.71 post = 5432 user = guoxm password = guoxm’ # Slave information and connected users
recovery_target_timelint = ‘latest’ #Describe the recovery to the latest state
After holding and exiting,
Copy or move to the main folder
CP recovery.conf 10/main/
Or
MV Recovery.conf 10/main/
Then, exit exits to the original user
Finally, configure the postgresql.conf file from the slave
sudo vi/etc/postgresql/10/main/postgresql.conf
Add the following configuration at the end of the file
wal_level = hot_standby #Hot standby mode
max_connections = 300 #The maximum connection time must be less than the configuration of the slave
hot_standby = on #Describe that this machine is not only used for data archiving, but also for data query
max_standby_streaming_delay = 30s #Maximum delay time for streaming backup
wal_receiver_status_interval = 10s # Interval for reporting the status of the machine to the host
hot_standby_feedback = on #rError replication occurred, feedback to the host
Save and exit
Here, the slave configuration is complete
Restarting the database service
sudo service PostgreSQL restart
4, verify the master-slave configuration is successful
On the host, switch to the Postgres user
sudo su–postgres
Enter the database
Execute the following query
Select Client_addr,sync_state from Pg_stat_replication;
If you see the following query results
Description 192.168.100.70 is from the server, in the receive stream, and is asynchronous stream replication. Description successful master-slave configuration
Note: You must switch to the Postgres user to query
In addition, you can view related processes to verify that the configuration was successful
Execute separately on the host, slave
PS aux | grep postgres
The host can see the data stream being sent to the slave
You can see the data stream being received from the machine
To this, the master-slave configuration is completed;
Reference Links:
Https://www.jianshu.com/p/2d07339774c0
Https://www.cnblogs.com/yjf512/p/4499547.html
PostgreSQL Master-Slave configuration