PostgreSQL Master-Slave configuration

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