About Postgres

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. PostgreSQL runs on all major operating systems, including Linux, UNIX and Windows. It also supports storage of binary large objects, including pictures, sounds, or video.

In this guide, we are going to set streaming (asynchronous) replication for Psql database. Replication is a process that allows you to easily maintain multiple copies of a database by having them copied automatically from master to slave database. This will help to backup data.

PostgreSQL provides several ways to replicate a database. We will use hot standby mode. This blog will cover a very simple example of a psql replication-one primary server will send information to a standby slave.

 

Prerequisites

2 Ubuntu Server 16.04 machines with root privileges.(for eg: IP of machines given below) 

Primary (Master) Server: 192.168.2.131

Standby (Replica) Server: 192.168.2.132

 

Install and Setup PostgreSQL

In Ubuntu Server by default PostgreSQL 9.5 repository is available, to install PostgreSQL 9.6 or above, a user will need to install latest PostgreSQL repository directly.

Use following commands to install PostgreSQL with all its dependencies on both servers.
  • Add PostgreSQL apt repository:
# echo ‘deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main’ >> /etc/apt/sources.list.d/pgdg.list

 

  • Import the repository signing key, and update the package lists:
# wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –

 

  • Now update the system repository with an apt command:
# apt-get update

 

  • Install PostgreSQL 10:
# sudo apt-get install postgresql-10 postgresql-contrib-10

 

  • At the time of installation Postgres user is created, after completion of installation, we need to set a password for Postgres user with the following command:
# sudo passwd postgres

 

Configure Primary Server
  • Now, we have to create a new user and role and assign special permission to perform the replication.
  • For this first, log into PostgreSQL and create a new user, role using following commands:
# sudo -u postgres psql
# CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD ‘replicauser@’;

 

  • To verify the new replica user, use the following command:
postgres=#\du

 

  • This is the main server, in this user will have to configure a primary server for replication, allowing read and write process from applications running on it.
  • PostgreSQL Primary server running on 192.168.2.131
  • Now user have to edit postgresql.conf file as follows:

# sudo vim /etc/postgresql/10/main/postgresql.conf

(Find, uncomment and edit the values in postgresql.conf file as shown in below)

listen_addresses = 192.168.2.131

wal_level = hot_standby

synchronous_commit = local

max_wal_senders = 2

wal_keep_segments = 10

archive_mode = on

archive_command = ‘cp %p /var/lib/postgresql/10/main/archive/%f’

synchronous_standby_names = ‘slaveserver’

Save and close the file.

 

  • Since we enable archive mode, we have to create a new directory for the archive configuration. After directory creation, allocate required permission and change ownership of that directory using following commands:

# sudo mkdir -p /var/lib/postgresql/10/main/archive/

# sudo chmod 700 /var/lib/postgresql/10/main/archive/

# sudo chown -R postgres:postgres /var/lib/postgresql/10/main/archive/

 

  • Now, add the replication connection by editing pg_hba.conf file:

# vim /etc/postgresql/10/main/pg_hba.conf

# localhost
host     replication    replica     127.0.0.1/32     peer

# PostgreSQL Primary IP address
host     replication    replica     192.168.2.131/32     peer

# PostgreSQL Standby IP address
host     replication    replica     192.168.2.132/32     peer

(Note: User can set auth-method as per required, here we use peer. )

Save and close the file.

 

  • Restart PostgreSQL and check PostgreSQL running under IP of primary on 5432 port.

# systemctl restart postgresql

# netstat -plntu

 

Configure Standby Server
  • Before configuring standby server stop Postgres database service using the following command:
# sudo systemctl stop postgresql

 

  • Now, a user needs to edit postgresql.conf file located in the postgres data directory.

# sudo vim /etc/postgresql/10/main/postgresql.conf

listen_addresses = 192.168.2.132

wal_level = hot_standby

synchronous_commit = local

max_wal_senders = 2

wal_keep_segments = 10

hot_standby =on

synchronous_standby_names = ‘slaveserver’

Save and close the file when finished.

 

Replication initial data from primary server to standby server
  • On standby server, Go to Data directory  make changes as shown below

# cd /var/lib/ppostgresql/10/

# mv main main-backup

 

  • Create a new directory, change ownership and permission of that directory

# mkdir -p main/

# chmod 700 main/

# chown -R postgres:postgres main/

 

  • Log in as a postgres user and copy data directory from the primary server to standby server with the following command:

# su – postgres

# pg_backup -h 192.168.2.131 -U replica -D /var/lib/postgresql/10/main -P

 

  • next, in data directory user need to create a file called recovery.conf and change permission with following data:

#sudo vim /var/lib/postgresql/10/main/recovery.conf

standby_mode = ‘on’

primary_conninfo = ‘host=192.168.2.131 port=5432 user=replica password=replicauser@’

restore_command = ‘cp /var/lib/postgresql/10/main/archive/%f %p’

trigger_file = ‘/tmp/postgresql.trigger.5432’

 

# sudo chmod 600 recovery.conf

 

Save and close the file, then start PostgreSQL service using below command:

# sudo systemctl start postgresql

 

Test-Replication
  • Now, go to primary server and log in to postgres user

# su – postgres

 

  • Check the replication information with the following command:

# psql -x -c “select * from pg_stat_replication;”

 

  • Next, create a database on a primary server and check on standby server whether it replicated or not.

# sudo -u postgres psql

postgres=#create database devopstech;

 

  • Now, login to standby server you should see devopstech name database is created.

# sudo -u postgres psql

postgres=#\list

 

  • Let’s see if we can insert any data from slave:

# INSERT INTO replica_test VALUES (‘From slave server’);

 

 

Reference
https://www.howtoforge.com/tutorial/how-to-set-up-master-slave-replication-for-postgresql-96-on-ubuntu-1604/
https://www.postgresql.org

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


CAPTCHA Image
Reload Image