Setting Up Primary-Secondary Replication on PostgreSQL

What Is PostgreSQL?

PostgreSQL is an open source object-relational database management system (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 Primary Node

1. Run the following commands to install PostgreSQL on the 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 account-name 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 modify the /var/lib/pgsql/11/data/pg_hba.conf file and add the following content to the file:

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

   host 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-secondary-database>/24 md5 #Allows for data replication from the primary database to the secondary database.

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

   vim postgresql.conf

   wal_level = hot_standby #Hot backup mode
   max_wal_senders= 6 #Maximum number of streaming replication links
   wal_sender_timeout = 60s #Timeout for the streaming replication host to send data
   max_connections = 512 #The max_connections value of the secondary database must be greater than that of the primary database.
   archive_command='cp %p /var/lib/pgsql/11/data/archivelog/%f' #Archiving
   wal_keep_segments=10240
   archive_mode = on #Archiving is enabled.
   listen_addresses= xxx.xx.xx.xx  #Local IP address

7. Run the following command to restart PostgreSQL:

   systemctl restart postgresql-11

Configuring the Secondary Node

1. Run the following commands to install PostgreSQL on the secondary 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 primary node:

   pg_basebackup -h <IP-address-of-the-primary-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 secondary database.
   primary_conninfo = 'host=<IP-address-of the primary 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 generated after primary/secondary 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' #Clearing of 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  #Local IP address
   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 the Primary-Standby Replication

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

   ps aux |grep sender

   

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

   ps aux | grep receiver

   

3. Run the following commands on the primary database to check the status of the secondary database:

   su - postgres
   -bash-4.2# psql
   replication=# select * from pg_stat_replication;

   

4. Create a database on the primary database and check whether the newly created database is synchronized to the secondary database.
   1. Run the following commands to create a database on the primary database:

      postgres=# create database  testdb;
      postgres=# \l

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

      postgres=# \l