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.
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.