Data Replication Setup Using Master and Slave: PostgreSQL- III

In last few blogs, we have seen how to automate steps required to setup Master-slave nodes. This blog would be the last in this series.

Until now we have discussed how the architecture should look like and what parameters are needed to be modified to what value on master and slave node. In this blog, we will perform steps. We will setup Async node first.

We will use the configurations defined in the our “Master-Slave Replication: Postgres – II” blog. Let’s setup our automated script to perform all steps automatically. We will understand explanations for each line.

pgcreatemaster:
  # Set of work for command
  mkdir /home/sanjeeva/postgres/Archivelogs/$(pgname)
  pg_createcluster 11 $(pgname) -- --data-checksums
  cp pgrepl.conf /etc/postgresql/11/$(pgname)/
  sed -i "s|^#include = '…'|include = 'pgrepl.conf'|g" /etc/postgresql/11/$(pgname)/postgresql.conf
  sed -i "s|^# archive_command =|archive_command = 'cp %p /home/sanjeeva/postgres/Archivelogs/$(pgname)/%f'|g" /etc/postgresql/11/$(pgname)/pgrepl.conf
  #sed -i "s|^# synchronous_standby_names =|synchronous_standby_names = $(pgcgname)|g" /etc/postgresql/11/$(pgname)/pgrepl.conf
  pg_ctlcluster 11 $(pgname) start
  pg_lsclusters

The command here is “pgcreatemaster“. To make this script understandable, we have number lines of scripts. This will help us expalin better.

Image of Automation script with Line number

The first line for this command is to create a directory where database can put its archive log. To avoid multiple instance to put their archive at one place, we have make folder with the instance name using keyword $(pgname). This variable pgname will be passed while executing it.

Line no 2 will create the new instance of PostgreSQL (cluster) with name we will provide as input to script using variable $(pgname). This cluster will also use checksum on data pages which helps detecting corruption by I/O system. This feature is facilitated by flag --data-checksums

Line 3, 4, 5, 6 will be used to update all configuration parameter requited on master node. We have already created a file pgrpl.conf and copying this file into respective folder using line number 3. In next line, a hook is created in postgresql.conf for pgrpl.conf file. Line no 4 is enabling archive command.

Line no 6 addresses Sync or Async type of slave node on master node. It is defining synchronous_standby_names. This line should be commented for Async.

Line no 7 will start the cluster and line no 8 will list the cluster for us.

postgres@sanjeeva:/home/sanjeeva/postgres$ make pgcreatemaster pgname=pgmaster
Set of work for command
mkdir /home/sanjeeva/postgres/Archivelogs/pgmaster
pg_createcluster 11 pgmaster -- --data-checksums
Creating new PostgreSQL cluster 11/pgmaster …
/usr/lib/postgresql/11/bin/initdb -D /var/lib/postgresql/11/pgmaster --auth-local peer --auth-host md5 --data-checksums
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are enabled.
fixing permissions on existing directory /var/lib/postgresql/11/pgmaster … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default timezone … Asia/Calcutta
selecting dynamic shared memory implementation … posix
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
Success. You can now start the database server using:
pg_ctlcluster 11 pgmaster start
Ver Cluster Port Status Owner Data directory Log file
11 pgmaster 5444 down postgres /var/lib/postgresql/11/pgmaster /var/log/postgresql/postgresql-11-pgmaster.log
cp pgrepl.conf /etc/postgresql/11/pgmaster/
sed -i "s|^#include = '…'|include = 'pgrepl.conf'|g" /etc/postgresql/11/pgmaster/postgresql.conf
sed -i "s|^# archive_command =|archive_command = 'cp %p /home/sanjeeva/postgres/Archivelogs/pgmaster/%f'|g" /etc/postgresql/11/pgmaster/pgrepl.conf
sed -i "s|^# synchronous_standby_names =|synchronous_standby_names = |g" /etc/postgresql/11/pgmaster/pgrepl.conf
pg_ctlcluster 11 pgmaster start
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
11 main 5432 down postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
11 pgmaster 5444 online postgres /var/lib/postgresql/11/pgmaster /var/log/postgresql/postgresql-11-pgmaster.log
postgres@sanjeeva:/home/sanjeeva/postgres$

The last two lines of output lists the clusters available on our machine. It clearly lists pgmaster cluster created by our automated script. The listings are in red color as these are shutdown and green color as cluster is up and running. Our master is up and running. Let’s connect to the master using port

postgres@sanjeeva:/home/sanjeeva/postgres$ psql -p 5444
psql (11.10 (Ubuntu 11.10-1.pgdg18.04+1))
Type "help" for help.
postgres=# select version();
version
PostgreSQL 11.10 (Ubuntu 11.10-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)
postgres=#

Let’s create role in the master database to facilitate replication on slave. Use role name “replication” to avoid any changes in pg_hba.conf Let’s create user first.

postgres=# create role replication REPLICATION LOGIN ENCRYPTED PASSWORD 'replication123';
CREATE ROLE
postgres=#

Our master is ready. Its time to create slave now. for slave we have also created automated scripts. This will create Async slave.

pgcreateslave:
  # Set of work for command
  pg_createcluster 11 $(pgslavename) -- --data-checksums
  mv /var/lib/postgresql/11/$(pgslavename) /var/lib/postgresql/11/$(pgslavename).org
  pg_basebackup -U postgres -p $(masterport) -D /var/lib/postgresql/11/$(pgslavename) -P -R
  echo "restore_command = 'cp %p /home/sanjeeva/postgres/Archivelogs/$(pgmastername)/%f %p'" >> /var/lib/postgresql/11/$(pgslavename)/recovery.conf
  echo "recovery_target_timeline = 'latest'" >> /var/lib/postgresql/11/$(pgslavename)/recovery.conf
  pg_lsclusters
  rm -Rf /var/lib/postgresql/11/$(pgslavename).org
  pg_ctlcluster 11 $(pgslavename) start
  pg_lsclusters

Let’s understand this command. Line no 1 is used to create database instance with name as specified by $(pgslavename). Once db is created, we will rename the folder in line 2 as we need to restore this database from backup of master. For this we have used pg_basebackup in line number 3. The key factor here is -R which creates recovery.conf for slave in it’s data folder with all required parameters to make a slave Async node.

Line number 4 & 5 is updating archival information in recovery.conf. We are done with Async slave. Let’s use this automated script to create Async Slave.

postgres@sanjeeva:/home/sanjeeva/postgres$ make pgcreateslave pgslavename=pgslave pgmastername=pgmaster masterport=5444
Set of work for command
pg_createcluster 11 pgslave -- --data-checksums
Creating new PostgreSQL cluster 11/pgslave …
/usr/lib/postgresql/11/bin/initdb -D /var/lib/postgresql/11/pgslave --auth-local peer --auth-host md5 --data-checksums
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are enabled.
fixing permissions on existing directory /var/lib/postgresql/11/pgslave … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default timezone … Asia/Calcutta
selecting dynamic shared memory implementation … posix
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
Success. You can now start the database server using:
pg_ctlcluster 11 pgslave start
Ver Cluster Port Status Owner Data directory Log file
11 pgslave 5445 down postgres /var/lib/postgresql/11/pgslave /var/log/postgresql/postgresql-11-pgslave.log
cp pgrepl.conf /etc/postgresql/11/pgslave/
mv /var/lib/postgresql/11/pgslave /var/lib/postgresql/11/pgslave.org
pg_basebackup -U postgres -p 5444 -D /var/lib/postgresql/11/pgslave -P -R
23771/23771 kB (100%), 1/1 tablespace
echo "restore_command = 'cp %p /home/sanjeeva/postgres/Archivelogs/pgmaster/%f %p'" >> /var/lib/postgresql/11/pgslave/recovery.conf
echo "recovery_target_timeline = 'latest'" >> /var/lib/postgresql/11/pgslave/recovery.conf
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
11 main 5432 down postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
11 pgmaster 5444 online postgres /var/lib/postgresql/11/pgmaster /var/log/postgresql/postgresql-11-pgmaster.log
11 pgslave 5445 down,recovery postgres /var/lib/postgresql/11/pgslave /var/log/postgresql/postgresql-11-pgslave.log
rm -Rf /var/lib/postgresql/11/pgslave.org
pg_ctlcluster 11 pgslave start
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
11 main 5432 down postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
11 pgmaster 5444 online postgres /var/lib/postgresql/11/pgmaster /var/log/postgresql/postgresql-11-pgmaster.log
11 pgslave 5445 online,recovery postgres /var/lib/postgresql/11/pgslave /var/log/postgresql/postgresql-11-pgslave.log
postgres@sanjeeva:/home/sanjeeva/postgres$

We can see the master and its replication status in master.

postgres@Sanjeeva4156:/home/sanjeeva/postgres$ psql -p 5444
psql (11.10 (Ubuntu 11.10-1.pgdg18.04+1))
Type "help" for help.
postgres=# select pid, application_name, state, sync_state from pg_stat_replication;
pid | application_name | state | sync_state
-------+------------------+-----------+------------
22567 | walreceiver | streaming | async
(1 row)
postgres=#

To validate, we can connect on slave and perform some DDL.

postgres@Sanjeeva4156:/home/sanjeeva/postgres$ psql -p 5445
psql (11.10 (Ubuntu 11.10-1.pgdg18.04+1))
Type "help" for help.
postgres=# create database test;
ERROR: cannot execute CREATE DATABASE in a read-only transaction
postgres=#

Mark the error. It says that you can not create in read-only database as slave would be read-only.

We are done with Async node. In next blog we will create a Sync node and see the behavior of master node.

Leave a Reply

Your email address will not be published.