How To Install And Configure PostgreSQL Replication On Ubuntu

This comprehensive Linux guide expects that you run the following commands as root user but if you decide to run the commands as a different user then ensure that the user has sudo access and that you precede each of the privileged commands with sudo

PostgreSQL is an open-source object-relational database system that is highly extensible and compliant with ACID (Atomicity, Consistency, Isolation, Durability) and the SQL standard. It is a powerful database server which can handle high workloads. PostgreSQL can be used in Linux, Unix, FreeBSD and Windows.

Installing PostgreSQL 9.6

The Ubuntu’s default repository contains the PostgreSQL installation files, so it can be easily installed using apt command

apt-get update
apt-get install postgresql postgresql-contrib

Configuring UFW

Install the Uncomplicated Firewall using the command:

apt-get install -y ufw

The Uncomplicated Firewall is a tool to manage firewall on linux operating systems. Now you need to allow the PostgreSQL and SSH service to the firewall and then enable the firewall. To do this, execute the following command.

ufw allow ssh
ufw allow postgresql
ufw enable

Configure PostgreSQL Master Server

Open the PostgreSQL main configuration file /etc/postgresql/9.6/main/postgresql.conf using an editor such as vim or nano

nano /etc/postgresql/9.6/main/postgresql.conf

Make the following changes to the file,

• Uncomment the listen_addresses line and edit adding the master server IP address, which in my case is 172.168.1.100
• Also uncomment the wal_level line changing its value to wal_level = hot_standby
• To use local syncing for the synchronization level, uncomment and edit it to synchronous_commit = local
• In this tutorial, we are using two servers, so the uncomment and edit the lines as max_wal_senders = 2 and wal_keep_segments = 10

/etc/postgresql/9.6/main/postgresql.conf

listen_addresses = 172.168.1.100 wal_level = hot_standby synchronous_commit = local max_wal_senders = 2 wal_keep_segments = 10

Save and close the file.

Now open pg_hba.conf file for the authentication configuration.

nano /etc/postgresql/9.6/main/pg_hba.conf

Add the following lines in the file, keeping the Master server and Slave Server IP Address according to your configurations. In may case Master and Slave server IP addresses are 172.168.1.100 and 172.168.1.101 respectively.

/etc/postgresql/9.6/main/pg_hba.conf

# Localhost host replication replica 127.0.0.1/32 md5 # PostgreSQL Master IP address host replication replica 172.168.1.100/32 md5 # PostgreSQL SLave IP address host replication replica 172.168.1.101/32 md5

Save and exit the file and then restart PostgreSQL:

systemctl restart postgresql

Create Users

Login to the PostgreSQL shell and create users for replication process.

su - postgres
psql

Create a new user and replace replic111 by your new username and 1_very_str@ng_Pwd with your new user password and then close the shell.

postgres=# CREATE USER replic111 REPLICATION LOGINENCRYPTED PASSWORD '1_very_str@ng_Pwd';

This concludes the master server configuration.

Configure Slave

The Slave will not be granted write permissions to the server. It will only have read permissions. You have to stop the already running PostgreSQL service on the slave to configure it.

systemctl stop postgresql

Open up the PostgreSQL main configuration file for editing

nano /etc/postgresql/9.6/main/postgresql.conf

Make the following changes to the file:

• Uncomment the listen_addresses line and edit adding the slaves IP address, that is 172.168.1.101 in my case.
• Also uncomment the wal_level line changing its value to wal_level = hot_standby
• To use local syncing for the synchronization level, uncomment and edit it to synchronous_commit = local
• In this tutorial, we are using two servers, so the uncomment and edit the lines as max_wal_senders = 2 and wal_keep_segments = 10
• Enable hot_standby for the slave server by uncommenting the following line and changing its value to on

/etc/postgresql/9.6/main/postgresql.conf

listen_addresses = 172.168.1.101 wal_level = hot_standby synchronous_commit = local max_wal_senders = 2 wal_keep_segments = 10 hot_standby = on

Save and exit the file

Copying Data From Master To Slave

To set-up master to slave server syncing, the PostgreSQL main directory on the slave must be replaced with the main directory from the master. In the slave server, log in to the postgres user using:

su - postgres

Make a backup of actual files using the following command

cd/var/lib/postgresql/9.6/
mv main a_backup_of_main

Create a new main directory and assign permissions

mkdir main/
chmod 700 main 

Copy the main directory from the master to the slave server by using pg_basebackup:

pg_basebackup -h master_IP_address -U replica -D /var/lib/postgresql/9.6/main -P --xlog

Once the transfer is complete, in the main directory create a new recovery.conf file, and paste the following content:

touch recovery.conf

/var/lib/postgresql/9.6/main/recovery.conf

standby_mode = 'on' primary_conninfo = 'host=172.168.1.100 port=5432 user=replic111 password=1_very_str@ng_Pwd' trigger_file = '/tmp/postgresql.trigger.5432'

Save and exit the file and then assign permissions

chmod 600 recovery.conf

Start PostgreSQL:

systemctl start postgresql

The Configuration has been completed!

The post How To Install And Configure PostgreSQL Replication On Ubuntu appeared first on CODESPOSTS.COM.