How To Setup a RealTime Database Replication and Backup on Linux Servers
If you have never experienced a Data Breach, you may be unaware of the importance and benefits of having a real-time database replication/backup configured on your database servers.
First off, let's begin by understanding what a Database Replication means and the scope of this post.
Database replication is a process of copying (syncing) data from a database on one server (the master server) to a database on another server (the slave server). The main benefit of this process is to distribute databases to multiple machines, so in the event of downtime, hack, or failure of the master server, there is a backup machine with same data available ready to kickoff for handling requests without interruption.
This is often known as a master/slave relationship.
In this post, we will setup replication from one database server to another.
PREREQUISITE
To follow this guide, the following will be required:
- 2 Linux Centos 7 / 8 servers with a running instance of PostgreSQL ≥ 9.5
- SSH access configured with root level access on both servers
If you do not have PostgreSQL 9.5 or higher installed, please follow the link below for an easy installation. Come back here when you are done. we will be waiting for you :)
SCENARIO
A PostgreSQL database running on a Linux Centos Server A with IP address (1.1.1.1) needs to be replicated to another Linux Centos server B with the IP address (2.2.2.2) running PostgreSQL in real time.
This guide covers the process for a full database cluster replication (Hot Standby) from server A to B. In this scenario, the entire database cluster from server A is replicated on server B. and server B is set up as a hot standby that can replace server A in the event of a failure.
STEP 1: FIREWALL SETUP
Firewalld is the default firewall management tool for CentOS 7. To ensure it works smoothly with Postgres, we need to start the service and open the port for Postgres connections.
Use the command below to start the firewall.
>> systemctl start firewalld
To enable firewalld start automatically when the system reboots, use the command below.
>> systemctl enable firewalld
Now add the Postgres service to firewalld using the command below
>> firewall-cmd –add-service=postgresql –permanent
Restart firewalld
>> firewall-cmd –reload
Check the open port of the service and verify postgres port is open using the below command.
>> firewall-cmd –list-all
NOTE: This same procedure must be followed for the slave server as well.
STEP 2: BACKUP CONFIGURATION FILES
Before we begin making changes to our database configuration files, it is important that we back up the configuration files. This way we can revert back to it in the event of a misconfiguration.
This section highlights steps to back up the PostgreSQL configuration files for both servers.
- Backup postgresql.conf
The postgresql.conf file contains configurations for the running PostgreSQL server. To back up this file, run the below command
>> cp /var/lib/pgsql/9.5/data/postgresql.conf /var/lib/pgsql/9.5/data/postgresql.conf.backup
The above Linux command creates a backup of the postgresql.conf file in the same directory named postgresql.conf.backup
- Backup pg_hba.conf
Use the below command to back up the pg_hba.conf file. This is the configuration file that holds details on how PostgreSQL communicates with other hosts.
>> cp /var/lib/pgsql/9.5/data/pg_hba.conf /var/lib/pgsql/9.5/data/pg_hba.conf.backup
NOTE: The above process should be done on both the master and replication server
Great! Now, we are ready to edit the configuration files.
STEP 3: MASTER SERVER CONFIGURATION
We will start with configuring the master server (main database server to be replicated). This will be the main server and will allow read and write. We will assume Postgres on the master server runs on the IP 1.1.1.1 and should perform a streaming replication to the slave servers. (note: you can configure more than one slave servers). For the scope of this post, we assume we have only one slave server.
- postgresql.conf
Change Directory to the path /var/lib/pgsql/9.5/data and edit the configuration file postgresql.conf.
>> cd /var/lib/pgsql/9.5/data
>> nano postgresql.conf
Uncomment the line listen_address and change the value to 1.1.1.1.
Change to reflect as shown below.
>> listen_addresses = ‘1.1.1.1’
In nano, you can search for a particular word using ‘ctrl/cmd w’
Next, uncomment the line wal_level and change it to hot_standby.
Change to reflect as shown below.
>> wal_level = ‘hot_standby’
Turn on the archive_mode by setting archive_mode = on and archive_command= ‘cp %p /var/lib/pgsql/9.5/archive/%f’
>> archive_mode = on
>>archive_command= ‘cp %p /var/lib/pgsql/9.5/archive/%f’
The above command sets the archive mode and location for archiving of the backup files.
To change the replication settings, uncomment the max_wal_senders line and set the value to 10 (these configures the number of concurrent sender processes to setup). Also, Change wal_keep_segment to 10 as shown below-
>> max_wal_senders = 10
>> max_keep_segment = 10
The wal_keep_segments sets the number of wal segments to keep in the master server at each point in time. Each segment is about 16MB.
Uncomment the line synchronous_standby_names and set the value to pgslave01 (This is where we specify the names of slave servers )
>> synchronous_standby_names = ‘pgslave01’
This is optional. As it only sets the names for standby slaves.
Save the file with all these changes. Since we have enabled the archive mode, we will create a new directory for archiving.
Once the directory is created, we will allocate to it the required permissions and change the owner to the postgres user, as shown below:
>> mkdir -p /var/lib/pgsql/9.5/archive/
>> chmod 700 /var/lib/pgsql/9.5/archive
>> chown -R postgres:postgres /var/lib/pgsql/9.5/archive
- Pg_hba.conf
Next, we will edit the pg_hba.conf file present at the directory /var/lib/pgsql/9.5/data. Use nano to edit this file. Add the below-mentioned lines to the bottom of the file to configure the master and slave settings.
#Localhost
>> host replication replica 127.0.0.1/32 md5
Above, replication specifies that we are referring to a replication DB user, replica is the name of the replication DB user we will create, 127.0.0.1/32 is the localhost IP address, and md5 is the hashing algorithm to be used
# Postgresql Master Ip Address
>> host replication replica 1.1.1.1/32 md5
#Postgresql Slave IP Address
>> host replication replica 2.2.2.2/32 md5
Here 2.2.2.2/32 is the IP address of the slave database server.
Save this file and restart postgres service using the command-
>> systemctl restart postgresql-9.5
Next, let’s create a new user with replication privileges. Create a new user with the name replica as shown below.
>> sudo -u postgres -i
>> createuser -replication -P replica
Enter password for the new role when prompted.
This completes Master configuration and creation of user for replication.
Next, we set up the slave server.
STEP 4: SLAVE SERVER CONFIGURATION
Next, we will configure the slave server to run on IP 2.2.2.2 We will allow only to read without write on this server.
We will begin this process by stopping the running Postgres instance using the command below -
>> systemctl stop postgresql-9.5
Next, go to postgres directory and back up the data directory as shown below-
>> cd /var/lib/pgsql/9.5/
>> mv data data-backup
Create a new directory and change the ownership permissions of the directory to the Postgres user as shown below-
>> mkdir -p data/
>> chmod 700 data/
>> chown -R postgres:postgres data/
Login as postgres user and copy all contents of the data directory from Master to the Slave server as replica user using the command below
>> su –u postgres -i
>> pg_basebackup -h 1.1.1.1 -U replica -D /var/lib/pgsql/9.5/data -P — xlog
Where 1.1.1.1 is the master server and /var/lib/pgsql/9.6/data is the location to store the replication on the slave server. Xlog is the name of the log file and replica the name of the replication user on the master server. Type in the password when prompted. It might take some time to transfer all the data from the master to slave.
Upon completion of the transfer, change the location using the command below.
>> cd /var/lib/pgsql/9.5/data/
open the file postgressql.conf with the command
>> nano postgresql.conf
Edit the following details in the postgresql.conf file, then save it:
>> listen_addresses = ‘2.2.2.2’
>> hot_standby = on
Now, it is time to create a recovery.conf file. Create the file using the command below.
>> nano recovery.conf
Paste the following configuration changes in the file, then save it:
standby_mode = ‘on’
primary_conninfo = ‘host=1.1.1.1 port=5432 user=replica password=password application_name=pgslave01’
trigger_file = ‘/tmp/postgresql.trigger.5432’
The above conninfo command sets the connection info for the replication connection.
Next, change the ownership permissions of the recovery.conf file to that of postgres user using commands below:
>> chmod 600 recovery.conf
>> chown postgres:postgres recovery.conf
Once all changes have been saved, start the server using the command -
>> systemctl start postgresql-9.5
With this, we complete the slave database server configuration. You can use the netstat command to verify that the slave connection is working using the command -
>> netstat -plntu
STEP 5: TESTING THE CONFIGURATION
Finally, let’s test that everything we’ve configured so far works as expected. Login to master server and switch to postgres user using the command -
>> su -postgres
Next, check the streaming state replication using the following commands:
>> psql -c “select application_name, state, sync_priority, sync_state from pg_stat_replication;”
>> psql -x -c “select * from pg_stat_replication;”
You should be able to see state value as ‘streaming‘ and sync_state as ‘sync‘.
We will now test the set up by inserting records into the master and ensuring that they’re replicated to the slave.
Login to Master and access PostgreSQL shell using commands -
>> su -postgres
>> psql
Create a table and insert some values using a couple of simple SQL queries:
>> CREATE TABLE test_table (test varchar(100));
>> INSERT INTO test_table VALUES (‘This is Test data’);
Once the records have been inserted, Login to the slave server as the posgres user using the commands -
>> su -postgres
>> psql
Check that the data was replicated to the slave using the following SQL query.
>> SELECT * FROM test_table;
This should return the data that you inserted into the master server.
This completes the full database replication setup for PostgreSQL database on a Linux CentOS 7 server.
I hope you have found this post helpful.
If you need additional support or explanation in any area, please leave a comment below and I will respond as soon as possible.
Till next time…
