How to Install and Configure Master-Slave Replication with PostgreSQL 9.6 on CentOS 7

https://www.howtoforge.com/tutorial/how-to-install-and-configure-master-slave-replication-with-postgresql-96-on-centos-7

PostgreSQL or Postgres is an open source object-relational database management system (ORDBMS) with more than 15 years of active development. It's a powerful database server that can handle high workloads. PostgreSQL can be used on Linux, Unix, BSD, and Windows servers.
The master/slave database replication is a process of copying (syncing) data from a database on one server (the master) to a database on another server (the slave). The main benefit of this process is to distribute databases to multiple machines, so when the master server has a problem, there is a backup machine with same data available for handling requests without interruption.
PostgreSQL provides several ways to replicate a database. It can be used for backup purposes and to provide a high availability database server. In this tutorial, we will show you how to install and configure PostgreSQL 9.6 Master-Slave replication on a CentOS 7 server. We will use Hot standby mode, and it's a very good starting point to learn PostgreSQL in depth.

What we will do

1. Install PostgreSQL 9.6
2. Start and configure PostgreSQL 9.6
3. Configure Firewalld
4. Configure Master server
5. Configure Slave server
6. Testing

Prerequisite

• 1 CentOS 7 server
• Master - Read and Write Permission - IP: 10.0.15.10
• 1 CentOS 7 server
• Slave - Only Read Permission- IP: 10.0.15.11
• Root Privileges

Step 1 - Install PostgreSQL 9.6

In this tutorial, we will discuss how to install the latest PostgreSQL version 9.6 on the CentOS 7 server. By default, the CentOS official repository offers an older version, so we need to install PostgreSQL from the official repository.

Add new PostgreSQL 9.6 repository to the system.

yum -y install https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Now install PostgreSQL 9.6 using the following yum command.

yum -y install postgresql96-server postgresql96-contrib

So this way, you can install PostgreSQL 9.6.

Step 2 - Start and configure PostgreSQL 9.6

Before starting the postgres service, we need to initialize the database. For this, go to the '/usr/pgsql-9.6/bin' directory and initialize the database with postgres setup 'postgresql96-setup'.

cd /usr/pgsql-9.6/bin
./postgresql96-setup initdb

Next, start postgres service and enable it to start automatically at system boot.

systemctl start postgresql-9.6
systemctl enable postgresql-9.6

By default, postgres runs on port 5432. So make sure port 5432 state is 'LISTEN' by executing the netstat command in the following way.

netstat -plntu

If you do not have a netstat command, install net-tools. It's part from the net-tools.

yum -y install net-tools

So PostgreSQL 9.6 has been started. But we still need to configure the password for postgres user. Login as 'postgres' user, and then access the postgres 'psql' shell.

su - postgres
psql

Give the 'postgres' user new password with the query below.

\password postgres
Enter new password:

So PostgreSQL 9.6 has been started and a new password for postgres user has been configured.

Step 3 - Configure Firewalld

Firewalld is default a firewall management tool on CentOS 7. We will start this service and open the port for PostgreSQL connection.
Start firewalld and enable it to start automatically at system boot using the following commands:

systemctl start firewalld
systemctl enable firewalld

Next, add new postgres service to firewalld with the following commands.

firewall-cmd --add-service=postgresql --permanent
firewall-cmd --reload

Now check the open port of the service.

firewall-cmd --list-all

And you will see the PostgreSQL service has been added to the firewalld.

NOTE: Run the Step 1, Step 2 and Step 3 on all Master and Slaves.

Step 4 - Configure Master server

In this step, we will configure a master server for the replication. This is the main server, allowing read and write process from applications running on it. PostgreSQL on the master runs only on the '10.0.15.10' IP address, and performs streaming replication to the slave server.
Go to the pgsql data directory '/var/lib/pgsql/9.6/data' and edit the configuration file 'postgresql.conf'.

cd /var/lib/pgsql/9.6/data
vim postgresql.conf

Uncomment the 'listen_addresses' line and change value of the server IP address to '10.0.15.10'.

listen_addresses = '10.0.15.10'

Uncomment 'wal_level' line and change the value to 'hot_standby'.

wal_level = hot_standby

For the synchronization level, we will use local sync. Uncomment and change value line as below.

synchronous_commit = local

Enable archiving mode and give the archive_command variable a command as value.

archive_mode = on
 archive_command = 'cp %p /var/lib/pgsql/9.6/archive/%f'

For the 'Replication' settings, uncomment the 'wal_sender' line and change value to 2 (in this tutorial,  we use only 2 servers master and slave), and for the 'wal_keep_segments' value is 10.

max_wal_senders = 2
 wal_keep_segments = 10

For the application name, uncomment 'synchronous_standby_names' line and change value to 'pgslave01'.

synchronous_standby_names = 'pgslave01'

That's it. Save these changes and exit the editor.
Moving on, in the postgresql.conf file, the archive mode is enabled, so we need to create a new directory for archiving purposes.
Create a new directory, change its permission, and change the owner to the postgres user.

mkdir -p /var/lib/pgsql/9.6/archive/
chmod 700 /var/lib/pgsql/9.6/archive/
chown -R postgres:postgres /var/lib/pgsql/9.6/archive/

Now edit the pg_hba.conf file.

vim pg_hba.conf

Paste configuration below to the end of the line.

# Localhost
 host    replication     replica          127.0.0.1/32            md5
 
 # PostgreSQL Master IP address
 host    replication     replica          10.0.15.10/32            md5
 
 # PostgreSQL SLave IP address
 host    replication     replica          10.0.15.11/32            md5

Save and exit. All configuration is complete. Now, restart PostgreSQL 9.6 using the following command.

systemctl restart postgresql-9.6

Next, we need to create a new user with replication privileges. We will create a new user named 'replica'.
Login as postgres user, and create a new 'replica' user with password 'aqwe123@'.

su - postgres
createuser --replication -P replica
Enter New Password:

So with this, PostgreSQL 9.6 Master configuration has been completed, and the user for replication is created.

Step 5 - Configure Slave server

In this step, we will configure the Slave server. We want to replace postgres data directory on the slave server with the postgres data from the master server, and then configure slave server to run under the IP address '10.0.15.11', and finally enable hot_standby on it for allowing only read without write.
Before we start to configure the slave server, stop the postgres service using the systemctl command below.

systemctl stop postgresql-9.6

Then go to the postgres directory, and backup data directory.

cd /var/lib/pgsql/9.6/
mv data data-backup

Create new data directory and change the ownership permissions of the directory to the postgres user.

mkdir -p data/
chmod 700 data/
chown -R postgres:postgres data/

Next, login as the postgres user and copy all data directory from the 'Master' server to the 'Slave' server as replica user.

su - postgres
pg_basebackup -h 10.0.15.10 -U replica -D /var/lib/pgsql/9.6/data -P --xlog
Password:

Type your password and wait for data transfer from the master to the slave server.

After the transfer is complete, go to the postgres data directory and edit postgresql.conf file on the slave server.

cd /var/lib/pgsql/9.6/data/
vim postgresql.conf

Change the value of 'listen_addresses' to the slave server ip address '10.0.15.11'.

listen_addresses = '10.0.15.11'

Enable 'hot_standby' on the slave server by uncommenting the following line and change the value to 'on'.

hot_standby = on

That's it. Save the changes and exit the editor.
Then create new 'recovery.conf' file with vim.

vim recovery.conf

Paste the following configuration in it.

standby_mode = 'on'
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica password=aqwe123@ application_name=pgslave01'
trigger_file = '/tmp/postgresql.trigger.5432'

Save the changes and exit the file.
Note: On the primary_conninfo, input your own server details.
Change the ownership permissions of the recovery.conf file to that of the 'postgres' user.

chmod 600 recovery.conf
chown postgres:postgres recovery.conf

And start PostgreSQL 9.6 on the slave server.

systemctl start postgresql-9.6

The slave server configuration has been completed.
Now when you check the Slave server, you will see that PostgreSQL 9.6 is running on the server with IP address '10.0.15.11'.

netstat -plntu

Step 6 - Testing

Installation and configuration for PostgreSQL 9.6 Master-Slave replication are complete. To test the setup, check the state stream of the replication and test for data replication from the Master to the Slave.
Log in to the Master server and switch to the postgres user.

su - postgres

Next, check the streaming state replication of PostgreSQL with the following commands.

psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
psql -x -c "select * from pg_stat_replication;"

You should see the state value is 'streaming', and the sync_state is 'sync'.

Next, test by inserting data from the master and then check all data on the slave server.
Log in as the postgres user and access the PostgreSQL shell on the 'MASTER' server.

su - postgres
psql

Create new table 'replica_test' and insert some data into it with the following insert queries.

CREATE TABLE replica_test (test varchar(100));
INSERT INTO replica_test VALUES ('howtoforge.com');
INSERT INTO replica_test VALUES ('This is from Master');
INSERT INTO replica_test VALUES ('pg replication by hakase-labs');

And now login to the 'SLAVE' server similar to the way you did on the master.

su - postgres
psql

Check all data from the table 'replica_test' using the query below.

select * from replica_test;

So you'll see that all the data is replicated from the 'MASTER' server to the 'SLAVE' server.

Additional test

Test to perform the 'WRITE' action from the 'SLAVE' server.

INSERT INTO replica_test VALUES ('this is SLAVE');

And the result should be 'Cannot execute INSERT'.
So the installation and configuration of the PostgreSQL 9.6 Master-Slave Replication on CentOS 7 is successful.

References

• https://www.postgresql.org/download/linux/redhat/
• https://www.server-world.info/en/note?os=CentOS_7