PostgreSQL Master-Slave configuration

Source: Internet
Author: User
Tags postgresql postgresql version






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


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.