In this tutorial, we will install the latest version of PostgreSQL 9.6. In the official Ubuntu repository, they provide PostgreSQL 9.5 only, so we need to install the latest version from the PostgreSQL repository directly.

• Master server IP address: 10.0.15.10
• Replica server IP address: 10.0.15.11

Step 1. Install PostgreSQL Server on both servers

Add the postgreSQL 9.6 repository to the sources.list.d directory.

sudo apt-get install -y postgresql-9.6 postgresql-contrib-9.6

sudo systemctl enable postgresql

sudo netstat -lpnt | grep 5432

sudo passwd postgres

Step 2 - Configure the PostgreSQL MASTER Server

The master server has the IP address 10.0.15.10, and the postgres service will run under that IP with default port. The master server will have permission for the READ and WRITE to the database, and perform streaming replication to the slave server.

Go to the postgres configuration directory '/etc/postgresql/9.6/main' and edit the postgresql.conf file with vim.

cd /etc/postgresql/9.6/main/
vim postgresql.conf

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

listen_addresses = '10.0.15.10'

Uncomment 'wal_level' line and change value to the '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 change the archive_command option to the command 'cp %p /var/lib/postgresql/9.6/main/archive/%f'.

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

For the 'Replication' settings, in this tutorial we use 2 servers only, master and slave, uncomment the 'wal_sender' line and change value to 2, 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 the value to the name 'pgslave001'.

synchronous_standby_names = 'pgslave001'

Save the file and exit the editor.

In the postgresql.conf file, the archive mode is enabled, so we need to create a new directory for the archive. Create a new archive directory, change the permission and change the owner to the postgres user.

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

Next, edit pg_hba.conf file for authentication configuration.

vim pg_hba.conf

Paste configuration below to the end of the line.

# Localhost
host all all 0.0.0.0/0 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, then restart PostgreSQL.

systemctl restart postgresql

PostgreSQL is running under the IP address 10.0.15.10, check it with netstat command.

netstat -lpnt

Next, create a new user for replication. We will create a new user named 'replica' with password '[email protected]'. Please choose a secure password here for your setup! Log in to the postgres user and access the postgres front-end terminal psql.

su - postgres
psql

Create new 'replica' user with password 'replica@' with postgres query below.

CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD '[email protected]';

Now check the new user with 'du' query below, and you will see the replica user with replication privileges.

\du

MASTER server configuration has been completed.

Step 3 - Configure SLAVE Server

The SLAVE server has IP address 10.0.15.11. And this server will only have a READ permission to the database. The Postgres database server will run under the IP address of the server, not a localhost IP.

Stop the postgres service on the slave server with the systemctl command below.

systemctl stop postgresql

Go to the Postgres configuration directory '/etc/postgresql/9.6/main', then edit the configuration file 'postgresql.conf'.

cd /etc/postgresql/9.6/main/
vim postgresql.conf

Uncomment the listen_addresses line and replace the value with the slave server IP address 10.0.15.11.

listen_addresses = '10.0.15.11'

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

wal_level = hot_standby

For the synchronization level, uncomment the synchronous_commit line and change the value as shown below.

synchronous_commit = local

For the replication setting, uncomment the max_wal_senders line and replace the value with '2' because just use 2 servers. And for the wal_keep_segments, change the value to '10'.

max_wal_senders = 2
wal_keep_segments = 10

Uncomment the 'synchronous_standby_names' line for the application name and change value to the name 'pgslave001'.

synchronous_standby_names = 'pgslave001'

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

hot_standby = on

Save the file and exit the editor.

Step 4. Synchronize Data from Master Server to Replica Server

Log in to the SLAVE server and access postgres user.

su - postgres

mv 9.6/main 9.6/main-bekup

mkdir main/
chmod 700 main/

pg_basebackup -h 10.0.15.10 -U replica -D /var/lib/postgresql/9.6/main -P --xlog
Password:

When the data transfer is complete, go to the main data directory and create a new recovery.conf file.

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

Paste the configuration below:

standby_mode = 'on'
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica [email protected] application_name=pgslave001'
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'

Save and exit, then change the permissions of the file to 600 with chmod.

chmod 600 recovery.conf

Now start PostgreSQL 9.6 on the SLAVE server and make sure the postgres service is running on IP address 10.0.15.11 with netstat.

systemctl start postgresql
netstat -lpnt | grep 5432

Data transfer and configuration for the SLAVE server has been completed.

Step 5. Check and Verify the Replication Clusters

From the Master server, run psql

 su - postgres
 psql

and then run this command

SELECT client_addr, state FROM pg_stat_replication;

If you see the similar output, then you have successfully created the PostgreSQL replication cluster.

Now try to create a new database on the Master server.

- su - postgres
- psql
- CREATE DATABASE manjarodb;
- \list

Now let’s check the Replica server

- su - postgres
- psql
- \list

Backup

#!/bin/bash
DISCORD_WEBHOOK=
date=$(date +%d-%m-%y)
hostname=postgres-master.dev
backup_dir="/backup/db-backup"

mkdir -p $backup_dir/$date
cd $backup_dir/$date

PGPASSWORD="password" pg_dump -v -Fc -h 10.15.0.17 -U usernam -d "database_name"

> db_name_$date.pgdump

if [ "$?" -ne "0" ] ; then
  curl -H "Content-Type: application/json" -d "{\"username\": \"Mobicloud\", \"content\": \"Mobicloud - Backup postgresql on $hostname failed on database dbname \"}" $DISCORD_WEBHOOK
fi

2. Restore postgresql

pg_restore -U username -d "database_name" -v filename_backup.pgdump