Setting Up Master-Slave Replication on PostgreSQL

What Is PostgreSQL?

PostgreSQL is an open source object-relational DBMS (ORDBMS) with an emphasis on extensibility and standards compliance. It applies to business-oriented online transaction processing (OLTP) scenarios and supports NoSQL (JSON, XML, or hstore) and geographic information system (GIS) data types. It has won a good reputation in reliability and data integrity, and applies widely to Internet websites, location-based applications, and complex data object processing.

This section helps you use Huawei Cloud ECSs to set up PostgreSQL.

Preparations

• Create two ECSs.
• Configure a security group rule for the ECSs to allow port 5432.
  

  The CentOS 7.6 64bit is used as an example.

  The PostgreSQL 11.2 version is used as an example.

Configuring the Master Node

1. Run the following commands to install PostgreSQL on the master node:

   # yum update -y

   # yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

   # yum install postgresql11-server

   # yum install postgresql11

   # /usr/pgsql-11/bin/postgresql-11-setup initdb

   # systemctl enable postgresql-11

   # systemctl start postgresql-11

2. Run the following command to switch to the default user postgres:

   # su - postgres

3. Run the following command to enter the database:

   # psql

4. Run the following command to create an account and assign permissions to it:

   create role Username login replication encrypted password 'Password'

   

   The password in the preceding command must be enclosed in single quotation marks.

   Assume the username is dbar and the password is xxxxx. Run the following command:

   create role dbar login replication encrypted password 'xxxxx';

5. Run the following command to open configuration file /var/lib/pgsql/11/data/pg_hba.conf:

   # vim /var/lib/pgsql/11/data/pg_hba.conf

   Add the following content to the file:

   host all all 192.168.1.0/24 md5 #Allows for MD5 password authentication connection in the VPC network segment.
   host replication dbar IP address of the slave database/24 md5 #Allows for data replication from the master database to the slave database.

6. Run the following command to open file /var/lib/pgsql/11/data/postgresql.conf:

   # vim postgresql.conf

   Add the following content to the file:

   wal_level = hot_standby
   max_wal_senders= 6
   wal_sender_timeout = 60s
   max_connections = 512 #The max_connections value of the slave database must be greater than that of the master database.
   archive_command= 'cp %p /var/lib/pgsql/11/data/archivelog/%f'
   wal_keep_segments=10240
   archive_mode = on
   listen_addresses= xxx.xx.xx.xx

7. Run the following command to restart PostgreSQL:

   # systemctl restart postgresql-11

Configuring the Slave Node

1. Run the following commands to install PostgreSQL on the slave node:

   # yum update -y

   # yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

   # yum install postgresql11-server

   # yum install postgresql11

2. Run the following commands to copy the configuration file from the master node:

   # pg_basebackup -h IP address of the master node -U dbar -D /var/lib/pgsql/11/data -X stream -P

   # cp /usr/pgsql-11/share/recovery.conf.sample /var/lib/pgsql/11/data/recovery.conf

   

3. Run the following command to modify the recovery.conf file:

   # vim recovery.conf

   standby_mode = on  # This node is used as the slave database.
   primary_conninfo = 'host=IP address of the master node  port=5432 user=dbar password=xxxxx (Do not enclose the password in single quotation marks.)
   trigger_file = '/var/lib/pgsql/11/data/trigger.kenyon'    #Trigger file for master/slave switchover
   recovery_target_timeline = 'latest' 
   restore_command = 'cp /var/lib/pgsql/11/data/archivelog/%f %p'
   archive_cleanup_command = 'pg_archivecleanup /var/lib/pgsql/11/data/archivelog %r' #Clear outdated archives.

4. Run the following command to modify the postgresql.conf file:

   # chown -R postgres.postgres /var/lib/pgsql/11/data

5. Add the following content to the /var/lib/pgsql/11/data/postgresql.conf file.

   listen_addresses= xxx.xx.xx.xx

   max_connections = 600

6. Run the following commands to start PostgreSQL and enable it to start automatically upon ECS startup:

   #systemctl enable postgresql-11

   #systemctl start postgresql-11

Verifying Master-Slave Replication

1. Run the following command to check whether process sender runs on the master node:

   # ps aux |grep sender

   

2. Run the following command to check whether process receiver runs on the slave node:

   # ps aux | grep receiver

   

3. Run the following commands to check whether the status of the slave database can be viewed from the master database:

   # su - postgres

   -bash-4.2# psql

   replication=# select * from pg_stat_replication;

   

4. Create a database from the master database and check whether the newly created database is synchronized to the slave database.
   1. Run the following commands to create a database from the master database:

      postgres=# create database testdb;

      postgres=# \l

   2. Run the following command to check whether the newly created database is synchronized to the slave database.

      postgres=# \l