Introduction
Storing data in one place is usually a risky thing to do. It is usually recommended that you have a plan in place to ensure you recover from a disaster as soon as it strikes. One good way to ensure an effective disaster recovery plan is to have the same data on multiple servers and to keep them synchronized. The database servers usually work together for one to take over in case the primary/master server fails hence creating high availability. Several database servers may also serve the same data at the same time, this is referred to as Load Balancing. In this guide, you’ll learn how to configure a master-slave replication where one database server will have read-write permissions granted to it while the second one will have read only permissions granted to it.
Before you start
- 2 Ubuntu 18.04 Virtual Private Servers (VPS’s) or Dedicated Servers with a nonroot user having sudo privileges. One will act as a master while the other as a standby/slave server.
- PostgreSQL installed and configured on both servers. You can learn How To Install PostgreSQL Database Server On Ubuntu 18.04 here
Steps
. The server needs to be able to listen to all ports.
$ sudo vim /etc/postgresql/9.5/main/postgresql.conf
Special Note: The postgresql version might be a different one and hence your path might differ from the one above.
Search listen_addresses word on your editor or set numbers and scroll down to line 59. Uncomment the line and change the localhost name to * which enables the server listen to all IP addresses. It needs to look as the following excerpt.
... #------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all ...
Close your editor while saving changes and reload the postgresql service to accommodate the changes made
$ sudo systemctl restart postgresql
Create a Sample Database
For replication test purposes, create a sample table called characters. Log into your postgres database
$ sudo -i -u postgres psql postgres
# CREATETABLEcharacters(fname text, character_id serial, datetimestamp, description text);
Now insert sample data in your newly created table.
# INSERTINTOcharacters (fname, date, description) VALUES ( 'Griffin', current_date, 'This is a sample character' );
INSERT01
Primary Server Configuration
PostgreSQL requires a role/user with special permissions for replication purposes.
$ sudo -u postgres createuser -U postgres replicationuser -P -c 5 --replication
Special Note: You can learn about the createuser command options here. Also make sure in a production environment, the replicationuser password is STRONG to avoid incidents such as bruteforcing or easy password guessing.
You then need to create an archive directory which will be a sub-directory of your cluster’s default main directory.
$ sudo mkdir -p /var/lib/postgresql/9.5/main/mnt/server/archivedir
Configure the pg_hba.conf file
$ sudo vim /etc/postgresql/9.5/main/pg_hba.conf
Search the replication section, then add the following entry on replication user to look as follows. Remember to replace your slave server IP address.
# Allow replication connections host replication replicationuser 192.168.56.20/24 md5
Exit while saving changes. Edit the postgresql.conffile
$ sudo vim /etc/postgresql/9.5/main/postgresql.conf
Edit the following settings. Remember to uncomment each setting that you edit.
listen_addresses= '*' wal_level = hot_standby archive_mode= on Archive_commend = 'test ! -f mnt/server/archivedir/%f && cp %p mnt/server/archivedir/%f' max_wal_senders=3
Exit while saving changes and restart the postgresql service
$ sudo systemctl restart postgresql
Slave server configuration
First, stop the postgresql service on your slave server.
$ sudo systemctl stop postgresql
Rename the main in the postgresql directory to something else since the backup won’t replace the existing files in the same folder.
$ sudo mv /var/lib/postgresql/9.3/main /var/lib/postgresql/9.3/main_old
Run the pg_basebackup utility. Replace the ip with your master server ip address.
$ sudo -u postgres pg_basebackup -h 192.168.56.20 -D /var/lib/postgresql/9.5/main -U replicationuser -v -P --xlog-method=stream
Enter the replicationuser password that you set on your master server when prompted. Edit the postgresql.conf file
$ sudo vim /etc/postgresql/9.5/main/postgresql.conf
Search for the hot_standby setting option and change it to on. Exit while saving changes. You now need to provide the recovery settings which will be used in the event of data recovery.
$ sudo cp -avr /usr/share/postgresql/9.5/recovery.conf.sample /var/lib/postgresql/9.5/main/recovery.conf
Edit the recovery file
$ sudo vim /var/lib/postgresql/9.5/main/recovery.conf
Search for the standby_mode and change the option to on to enable the server to stay on standby mode. Search for the primary_conninfo and add the master server details
primary_conninfo= 'host=192.168.56.20 port=5432 user=replicationuser password=.Breotmas@12P12sr%'
Exit while saving changes. Start the postgresql service on your slave server
$ sudo systemctl start postgresql
Test-Replication
Go to your master server in the table you created. Add a new entry.
$ sudo -u postgres psql postgres
# INSERTINTOcharacter(fname, date, description) VALUES ( 'Sanchez', current_date, 'This is a family guy character'); INSERT01
Go back to your slave server in the Postgres database and view the data
$ sudo -u postgres psql postgres
# SELECT * FROMcharacter
fname | character_id | date | description ---------+--------------+---------------------+---------------------------- Griffin | 1 | 2018-10-22 00:00:00 | This is a sample character Sanchez | 2 | 2018-10-2200:00:00 | A Rick and Morty character (2 rows)
Conclusion
If you have been able to view the data on your slave server after inserting it in your master server, it means you have successfully set up your PostgreSQL master-slave replication. There are two ways to invoke a failover when the master server has suddenly stopped working; you can either use the pg_ctl command or you may set a trigger file location in your slave server’s /etc/postgresql/9.5/main/postgresql.conf file. In the file, simply search and uncomment the trigger_file = ‘/tmp/postgresql.trigger.5432’line.
Check out these top 3 Best web hosting services
- Looking for Top web hosting? Clicking on this link can be the solution.