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:
- 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:
# CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD ‘replicauser@’;
- 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:
# 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:
- 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
The post PostgreSQL Replication on linux appeared first on DevOpsTech Solutions.
This post first appeared on Migrating XEN Virtual Machines To The AWS Cloud, please read the originial post: here
