ItsTikk's Tech Note: Postgresql 12 Master/Slave Replication

In this article, I will demonstrate how to setup Master - Slave database for PostgreSQL. In case you have a lot for transaction hit your database, and you have performance issue on your database. You can improve your database performance by this method.

Fig 01 Architecture diagram

Merit (Why you need Master / Slave)

I have mentioned above. Master/slave helps to impove your database performance. Since your master database will synchronize with the slave database. So for the read operation you can read from your slave. And for write operation, you can conduct through the master database. Base on this logic, you could reduce the access to the master databast which is the bottleneck by distributing access traffic for read to the slave.

Set Up Master/Slave PostgreSQL

First, you need to install PostgreSQL to the servers. Following environment is my set up

Fig 02 Deploy Diagram


    Load Balance : pgpool.itstikk.pro (OS : CentOS 7 64 bit)
    Master : pgmaster.itstikk.pro (OS : CentOS 7 64 bit)
	Slave 00 : pgslave00.itstikk.pro (OS : CentOS 7 64 bit)
	Slave 01 : pgslave01.itstikk.pro (OS : CentOS 7 64 bit)

Install PostgreSQL

First, we need to install Database Server to the all of the components (Master, Slaves).


    
     #yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    
    #yum -y install epel-release yum-utils
#yum-config-manager --enable pgdg12
#yum install postgresql12-server postgresql12

After installation, you need to initiate the database before starting service and enble service.


  #/usr/pgsql-12/bin/postgresql-12-setup initdb
  #systemctl enable --now postgresql-12
  #systemctl status postgresql-12

In order to check whether your service is active or not, execuet below command


  #systemctl status postgresql-12

After your Postgresql 12 run successfully. you need to change postgres password


  #su - postgres
  $psql -c "alter user postgres with password 'StrongPassword'"

Next is to configure master

Configure Master

After installing PostgreSQL to the server, you start with configuring Master


  #su - postgres
  $ psql -c "ALTER SYSTEM SET listen_addresses TO '*';"

Above command will change configuration in /var/lib/pgsql/12/data/postgresql.auto.conf directly. You can view configuration as following


  #cat /var/lib/pgsql/12/data/postgresql.auto.conf

Then you should see as below


  # Do not edit this file manually!
  # It will be overwritten by the ALTER SYSTEM command.
  listen_addresses = '*'
  synchronous_standby_names = '*'

Next is create replication role that will use make connecton from slave to master as following.


  # su – postgres
  $ createuser --replication -P -e replicator

Then allow remote connection from slavee under replicator role which created above by edit pg_hba.conf and add following entry to the end of the file


  #vi /var/lib/pgsql/12/data/pg_hba.conf

Add below entry


  
    # TYPE  DATABASE        USER     
          ADDRESS             
       METHOD
  
  
    
      

    
    
      # "local" is for Unix domain socket connections only
    
    
      local   all           
           all               
                             
           md5
    
    
      # IPv4 local connections:
    
    
      host    all           
           all           
           0.0.0.0/0            md5
    
    
      # IPv6 local connections:
    
    
      host    all           
           all             ::1/128 
                      md5
    
    
      # Allow replication connections from localhost, by a user with
          the
    
    
      # replication privilege.
    
    
      local   replication     all   
                             
                       ident
    
    
      host    replication     all   
                   127.0.0.1/32     
                ident
    
    
      host    replication     all   
                   ::1/128       
                   ident
    
    
      

    
    
      host    replication     all   
                   0.0.0.0/0       
                 md5

Restart postgresql and enable firewall


  #systemctl restart postgresql-12
  #firewall-cmd --add-port=5432/tcp --permanent
  #firewall-cmd --reload

Configuer Slave

Next is to configure your slave sever to get the stream data from the master database to create replication. But first you need to stop postgresql service in your slave server and remove all ot the data file of PostgreSQL. All of this process is executed in the slave server. So keep in mind, you need confuse between master and slave server, otherwise you will get the messy.


  #systemctl stop postgresql-12
  #su - postgres
  $cp -R /var/lib/pgsql/12/data /var/lib/pgsql/12/data_orig
  $rm -rf /var/lib/pgsql/12/data/*

Next is create backup from master to the slave by using pg_basebackup


  $pg_basebackup -h pgmaster.itstikk.pro -D /var/lib/pgsql/12/data -U replicator -P -v  -R -X stream -C -S pgstandby01
  $exit

Now, you can confirm backup in the slave as below


  #ls -la /var/lib/pgsql/12/data

The result should be as following


total 144
drwx------ 20 postgres postgres  4096 May 24 00:00 .
drwx------  5 postgres postgres  4096 May 20 15:01 ..
-rw-------  1 postgres postgres     3 May 20 15:07 PG_VERSION
-rw-------  1 postgres postgres   224 May 20 15:07 backup_label.old
drwx------  9 postgres postgres  4096 May 20 15:19 base
-rw-------  1 postgres postgres    30 May 24 00:00 current_logfiles
drwx------  2 postgres postgres  4096 May 20 15:15 global
drwx------  2 postgres postgres  4096 May 20 15:07 log
drwx------  2 postgres postgres  4096 May 20 15:07 pg_commit_ts
drwx------  2 postgres postgres  4096 May 20 15:07 pg_dynshmem
-rw-------  1 postgres postgres  4297 May 20 15:07 pg_hba.conf
-rw-------  1 postgres postgres  1636 May 20 15:07 pg_ident.conf
drwx------  4 postgres postgres  4096 May 20 15:29 pg_logical
drwx------  4 postgres postgres  4096 May 20 15:07 pg_multixact
drwx------  2 postgres postgres  4096 May 20 15:13 pg_notify
drwx------  2 postgres postgres  4096 May 20 15:07 pg_replslot
drwx------  2 postgres postgres  4096 May 20 15:07 pg_serial
drwx------  2 postgres postgres  4096 May 20 15:07 pg_snapshots
drwx------  2 postgres postgres  4096 May 20 15:07 pg_stat
drwx------  2 postgres postgres  4096 May 20 15:07 pg_stat_tmp
drwx------  2 postgres postgres  4096 May 20 15:19 pg_subtrans
drwx------  2 postgres postgres  4096 May 20 15:07 pg_tblspc
drwx------  2 postgres postgres  4096 May 20 15:07 pg_twophase
drwx------  3 postgres postgres  4096 May 20 15:19 pg_wal
drwx------  2 postgres postgres  4096 May 20 15:07 pg_xact
-rw-------  1 postgres postgres   331 May 20 15:07 postgresql.auto.conf
-rw-------  1 postgres postgres 26563 May 20 15:07 postgresql.conf
-rw-------  1 postgres postgres    58 May 20 15:13 postmaster.opts
-rw-------  1 postgres postgres    96 May 20 15:14 postmaster.pid
-rw-------  1 postgres postgres     0 May 20 15:07 standby.signal

Then, restart postgresql server


  #systemctl start postgresql-12
  #systemctl status postgresql-12

Confirm Configuration and Test Stream Replication

To confirm whether configuration is working, you need to go back to the master server. To check whether replication is created properly or not, execute as following


  # su - postgres
  $ psql -c "SELECT * FROM pg_replication_slots;"
  $ exit

To view the connection settings appended in the postgresql.auto.conf file


  #less /var/lib/pgsql/12/data/postgresql.auto.conf

The result below should be appeared


  # Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'

  primary_conninfo = 'user=replicator password=qf48d8uv
  host=pgmaster.itstikk.pro port=5432 sslmode=prefer sslcompression=0
  gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

  primary_slot_name = 'pgstandby01'

Once connection between slave and master is established successfully. You can see WAL (Write Ahead Log) process status is show as stream on the slave server. you can check as below


    #su - postgres
    $psql -c "\x" -c "SELECT * FROM pg_stat_wal_receiver;"

The result should be as following.


  Expanded display is on.
-[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 10885
status                | streaming
receive_start_lsn     | 0/3000000
receive_start_tli     | 1
received_lsn          | 0/30014B8
received_tli          | 1
last_msg_send_time    | 2020-05-24 08:53:52.243157+00
last_msg_receipt_time | 2020-05-24 08:53:52.260069+00
latest_end_lsn        | 0/30014B8
latest_end_time       | 2020-05-24 07:41:13.004802+00
slot_name             | pgstandby01
sender_host           | pgmaster.itstikk.pro
sender_port           | 5432
conninfo              | user=replicator password=******** dbname=replication host=pgmaster.itstikk.pro port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

And to response to the slave streaming, On your master server you could see the status


    #su - postgres
    $psql -c "\x" -c "SELECT * FROM pg_stat_replication;"

You should see as below.


Expanded display is on.
-[ RECORD 1 ]----+------------------------------
pid              | 16899
usesysid         | 16499
usename          | replicator
application_name | walreceiver
client_addr      | 192.243.100.25
client_hostname  |
client_port      | 54478
backend_start    | 2020-05-24 03:41:13.107278-04
backend_xmin     |
state            | streaming
sent_lsn         | 0/30014B8
write_lsn        | 0/30014B8
flush_lsn        | 0/30014B8
replay_lsn       | 0/30014B8
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 1
sync_state       | async
reply_time       | 2020-05-24 04:40:50.820605-04

To test Postgresql stream replication, you can create a database on the master server


    #su - postgres
    $psql -U postgres
    #CREATE DATABASE db02

On your the slave server, you could see the same database is created as well.


    #su - postgres
    $psql -U postgres
    #\l

The result should be as following.


                               List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges
-----------+----------+-----------+---------+-------+-----------------------
 db00      | usr01    | SQL_ASCII | C       | C     |
 db01      | usr01    | SQL_ASCII | C       | C     |
 db02      | postgres | SQL_ASCII | C       | C     |
 postgres  | postgres | SQL_ASCII | C       | C     |
 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 usr01     | postgres | SQL_ASCII | C       | C     | =Tc/postgres         +
           |          |           |         |       | postgres=CTc/postgres+
           |          |           |         |       | usr01=CTc/postgres
(7 rows)

Enabling Synchronous Replication

Synchronous mode enbale ablity to once transaction is commited, master and slave synchronize simulatniously. To set synchronous mode, you just need to go to the master server and follow as below.


    #su - postgres
    $psql -c "ALTER SYSTEM SET synchronous_standby_names TO  '*';"
    $exit

Next is to restart postgresql-12


    #systemctl restart postgresql-12

Go to check synchronous mode by


    #su - postgres
    $psql -c "\x" -c "SELECT * FROM pg_stat_replication;"

The result should be as below.


  Expanded display is on.
-[ RECORD 1 ]----+------------------------------
pid              | 16899
usesysid         | 16499
usename          | replicator
application_name | walreceiver
client_addr      | 192.243.100.25
client_hostname  |
client_port      | 54478
backend_start    | 2020-05-24 03:41:13.107278-04
backend_xmin     |
state            | streaming
sent_lsn         | 0/30014B8
write_lsn        | 0/30014B8
flush_lsn        | 0/30014B8
replay_lsn       | 0/30014B8
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 1
sync_state       | sync
reply_time       | 2020-05-24 04:51:42.092933-04

ItsTikk's Tech Note

Postgresql 12 Master/Slave Replication

Merit (Why you need Master / Slave)

Set Up Master/Slave PostgreSQL

Install PostgreSQL

Configure Master

Configuer Slave

Confirm Configuration and Test Stream Replication

Enabling Synchronous Replication

Reference

No comments:

Post a Comment

Feature Recently

Running Wildfly Application Server in Domain Mode

Most Views