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

PostreSQL is a popular database management system that can organize and manage the data associated with websites or applications. Replication is a means of copying database information to a second system in order to create high availability and redundancy.

There are many ways to set up replication on a postgres system. In this tutorial, we will cover how to configure replication using a hot standby, which has the advantage of being relatively simple to configure.

To do this, we will need two Ubuntu 16.04 VPS instances. One will serve as the master database server and the other will function as a slave, which will replicate.

Installing PostgreSQL 9.6

To install the latest version of PostgreSQL, open a terminal window and type in the following command to add PostgreSQL 9.6 repository to the sources.list.d directory.

echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' | tee /etc/apt/sources.list.d/postgresql.list

Import the PostgreSQL signing key to the system by using the following command:

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

Its a good practice to update system libraries before continuing so you can use the following commands to update your system

apt-get update
apt-get upgrade

Use the apt command to install PosgreSQL 9.6 package as shown below

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

After the completion of succesfull install, we have to add it in the startup file so that it starts automatically when the system boots.

systemctl enable postgresql

PostgreSQL uses the default IP address (127.0.0.1) on the localhost. You can verify it using the following command

netstat -plntu

This verifies the working of PostgreSQL.

In the next step, we are required to create users for PostgreSQL. To do so, login to the root account to access the front-end interface

postgrespsql
postgres@codeposts:~$ psql
psql (9.6.2)
Type "help" for help.

Now, here you can change the password for the root user and check the connection info with queries. The password would not be visible as you type for privacy purposes.

postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Configure the PostgreSQL Master Server

The master serve has a predefined static IP address and the service will run under that IP with default port. The master server will have both the permissions to read and write to database and start replication to the slave server

Find the postgres configuration directory /etc/postgresql/9.6/main and the edit the file postgresql.conf with a text editor

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

In our case, we are setting the master and slave IP addresses to be 192.168.1.100 and 192.168.1.101 respectively. Make the following changes in the file according to your desires master and slave IP addresses:

  • Uncomment listen_addresses line and change the value to the master server IP address 192.168.1.100.
  • Uncomment wal_level line and change value to the hot_standby.
  • For the synchronization level, we will use local sync. Uncomment and change value line as below.
  • Enable archiving mode and change the archive_command option to the 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.
  • For the application name, uncomment synchronous_standby_names line and change the value to the name pgslave001.

The file should look like:

/etc/postgresql/9.6/main/postgresql.conf 
listen_addresses = '192.168.1.100'
wal_level = hot_standby
synchronous_commit = local
archive_mode = onarchive_command = 'cp %p /var/lib/postgresql/9.6/main/archive/%f'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'pgslave001'

Save and close the file.

Create a new archive directory, change its permission and change the owner too.

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/

Now open up the pg_hba.conf file using a text editor and edit the file

nano pg_hba.conf

Paste the following line at the end of the line

/etc/postgresql/9.6/main/archive/pg_hba.conf 
# Localhosthost    replication     replica          127.0.0.1/32            md5 # PostgreSQL Master IP addresshost    replication     replica          192.168.1.100/32            md5 # PostgreSQL SLave IP addresshost    replication     replica          192.168.1.101/32            md5

Save the file and close the editor. Restart PostgreSQL to make changes take effect

systemctl restart postgresql

Check the status using

netstat -plntu
Active internet connection (only servers)
Proto   Recv-Q   Sent-Q   Local Address   Foreign Address State  PID/Program name
tcp     0       0      192.168.1.100:5437     0.0.0.0:*      LISTEN  5337/postgres

Now create a new user named “replica” with any password such as in my case it is “abcde1234@”. Make sure the password is strong enough. Login to the postgre user and access the front-end terminal

postgrespsql
CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'abcde1234@';

You can check the configuration using:

\du

Following these steps, you have successfully configured the Master server

Configure the PostgreSQL Slave Server

The Slave server would only be granted read permissions to the database. The Postgres database server will run under the IP address of the server, not a localhost IP.

First Stop the service using the following command

systemctl stop postgresql

Navigate to the Postgre Configuration file and open it with a text editor to edit it 

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

Make the following changes in the file:

  • Uncomment listen_addresses line and change the value to the slave’s IP address 192.168.1.101.
  • Uncomment wal_level line and change value to the hot_standby.
  • For the synchronization level, we will use local sync. Uncomment and change value to 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.
  • For the application name, uncomment synchronous_standby_names line and change the value to the name pgslave001.
  • Enable hot_standby for the slave server by uncommenting the line and change value to on.

The file should look like:

/etc/postgresql/9.6/main/postgresql.conf 
listen_addresses = '192.168.1.101'
wal_level = hot_standby
synchronous_commit = local
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'pgslave001'
hot_standby = on

Save and close the file.

Copying PostgreSQL Data from the Master to the Slave

To start replicating the data from the master’s main directory to the slave’s directory, login to the slave and access the user

postgres

Navigate to the postgres main directory and make a backup of the existing data

cd 9.6/mv main main-backedupdatafolder

Create a new main directory as a postgres user and assign it appropriate permissions

mkdir main/
chmod 700 main/

Copy the main directory from the MASTER server to the SLAVE server with pg_basebackup command, here we will use replica user to perform this copy operation

pg_basebackup -h 192.168.1.100 -U replica -D /var/lib/postgresql/9.6/main -P --xlogPassword:

As soon as the data transfer is complete, navigate to the main data directory and create a new recovery.conf file

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

Paste the following lines in the file

/var/lib/postgresql/9.6/main/recovery.conf 
standby_mode = 'on'primary_conninfo = 'host=192.168.1.100 port=5432 user=replica password=abcde1234@ application_name=pgslave001'restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'trigger_file = '/tmp/postgresql.trigger.5432'

Save the file and close the editor. Assign required permissions to the file

chmod 600 recovery.conf

Start the service on the slave again and make sure the postgres service is running on IP address 192.168.1.101 with netstat 

systemctl start postgresql
netstat -plntu
Active internet connection (only servers)
Proto   Recv-Q   Sent-Q   Local Address   Foreign Address State  PID/Program name
tcp     0       0      192.168.1.101:5432     0.0.0.0:*      LISTEN  57777/postgres

Congratulations, you have finished the data transfer and Slave Configurations!