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.
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:
- Import the repository signing key, and update the package lists:
- Now update the system repository with an apt command:
- Install PostgreSQL 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:
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:
- To verify the new replica user, use the following command:
- 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:
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:
- Now, add the replication connection by editing pg_hba.conf file:
Save and close the file.
- Restart PostgreSQL and check PostgreSQL running under IP of primary on 5432 port.
Configure Standby Server
- Before configuring standby server stop Postgres database service using the following command:
- Now, a user needs to edit postgresql.conf file located in the postgres data directory.
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
- Create a new directory, change ownership and permission of that directory
- Log in as a postgres user and copy data directory from the primary server to standby server with the following command:
- next, in data directory user need to create a file called recovery.conf and change permission with following data:
Save and close the file, then start PostgreSQL service using below command:
- Now, go to primary server and log in to postgres user
- Check the replication information with the following command:
- Next, create a database on a primary server and check on standby server whether it replicated or not.
- Now, login to standby server you should see devopstech name database is created.
- Let’s see if we can insert any data from slave: