Arcana Info

Strengthening Data Safety: An In-Depth Guide to PostgreSQL Replication

PostgreSQL is a versatile open-source database system known for its performance and reliability. In this guide, we'll simplify the process of setting up PostgreSQL replication between your primary (master) and secondary (slave) servers.


Before we get started, here’s what you need:

  1. A compatible operating system for PostgreSQL (e.g., CentOS 7).
  2. Administrator-level access to the secondary server.
  3. A foundational understanding of PostgreSQL functionality.

Step 1: Installing PostgreSQL on the Secondary Server

First, we need to install PostgreSQL on your secondary server. 

Here’s how:


  • Install the PostgreSQL repository RPM:
  • Install PostgreSQL server and client packages:
					sudo yum install -y
sudo yum install -y postgresql12-server postgresql-12


Step 2: Initializing and Starting PostgreSQL on the Secondary Server

Now that PostgreSQL is installed, let’s set it up on your secondary server. Follow these steps:


  • Initialize the database and enable automatic start:


					sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12
sudo systemctl status postgresql-12


Step 3: Configuring the Master Database

To make replication possible, we’ll create a special user on primary (master) server and allow secondary (slave) server to connect to it. 

Here’s what to do:


  1. Create a replication user on the master:
  2. Allow the slave server to connect to the master by making a change in the master’s configuration: 
psql -c "show hba_file;"
# Go to the directory and add this line to pg_hba.conf:
echo "host replication replicator md5" >> pg_hba.conf
# Reload the configuration:
psql -c "select pg_reload_conf();"


Step 4: Configuring the Slave Database

Now, let’s set up the slave server to receive data from the master. 

Follow these steps: 

					# Verify that the slave can communicate with the master:
telnet 5432

# Copy data directory files from the master:
# Backup the existing PostgreSQL data directory on the slave
cp -rp /var/lib/pgsql/12/data /var/lib/pgsql/12/data_orig

# Remove the contents of the existing PostgreSQL data directory on the slave
rm -rf /var/lib/pgsql/12/data

# Ensure that the ownership of the PostgreSQL directory matches the master's:
# Change ownership to 'postgres' user and 'postgres' group recursively
chown -R postgres:postgres /var/lib/pgsql

# Set appropriate permissions on the PostgreSQL directory
chmod -R 775 /var/lib/pgsql

# Empty specific directories on the slave:
# Remove the contents of certain PostgreSQL directories
rm -rf /var/lib/pgsql/pg_tablespace_02/*
rm -rf /var/lib/pgsql/pg_tablespace_01/*
rm -rf /var/lib/pgsql/pg_wal/*


Step 5: Take the pg_basebackup on the slave database

pg_basebackup is a utility in PostgreSQL used for creating a physical backup of a PostgreSQL database cluster. It allows you to make a copy of the entire database cluster at a point in time, including all data files, transaction logs, and configuration files. 

					-- Create a physical backup using pg_basebackup
-- -h: Hostname or IP of the master server.
-- -D: Destination directory on the slave for the backup.
-- -U: Username for connecting to the master server.
-- -P: Prompt for the replication user's password.
-- -v: Enable verbose output.
-- -R: Create recovery.conf for setting up a standby server.
-- -X stream: Use streaming replication for continuous data transfer.

pg_basebackup -h -D /var/lib/pgsql/12/data -U replicator -P -v -R -X stream;


Step 6: Creating Backup Copies of Critical Configuration Files on the Slave Database:

On some OS the below configurational files are not in the data directory which is why you need to replace them separately with the primary database files.


					cd /var/lib/pgsql/12/data
mv pg_hba.conf pg_hba_backup.conf
mv postgresql_auto_backup.conf
mv postgresql.conf postgresql_backup.conf

Step 7: Starting the Database on the Slave server:
It’s time to start the DB service on the slave server. Don’t forget to tail the logs for the database to see what’s heppening.


					systemctl start postgresql-12

Step 8: Replication Monitoring
Now once our DB has started successfully, We will check the replication status. Postgresql provides views for it which we will be using as well. We can also monitor replication by comparing the data on both servers i.e, Primary & Slave.


					psql -c "\x" -c "SELECT * FROM pg_stat_wal_receiver;"


Step 9: Testing the Replication: Confirming Data Synchronization

Now that everything is set up, let’s make sure it works. Here’s what to do:


On Primary Server:

  • Create a Database and Table on the Primary.
  • Insert Test Records.
On Slave Server:
  •  Login into the salve Database and verify the DB and Table created on primary.
  • Query the table created the records inserted on primary should be visible on slave as well.

CREATE DATABASE test_replication;
\c test_replication
CREATE TABLE test_table (id serial primary key, value text);
INSERT INTO test_table (value) VALUES ('Record 1');
INSERT INTO test_table (value) VALUES ('Record 2');

psql -U postgres -d test_replication
SELECT * FROM test_table;


By following these steps, you’ve successfully set up PostgreSQL replication on your secondary server. This ensures that your data is always available and secure, even in the face of unexpected challenges.

Remember, managing a PostgreSQL environment requires regular attention and care. With our guide, you have the knowledge to build a resilient and highly available PostgreSQL setup. For any further assistance or advanced topics like failover and load balancing, don’t hesitate to reach out to Arcana-Info. We’re here to make your data management a breeze.

Arcana Services

Mobile Application Development

From the initial idea and formulation of product strategy, through building a prototype and testing it with users, right to the creation of the product itself, we help clients at every stage of the development lifecycle.

Web Apps and Microservices Development

Development of cloud applications following agile methods and strategies to ensure the use of microservices and advanced APIs.

Container Adoption

Containerized solutions for the deployment of modern and advanced distributed applications.

Stay updated

Subscribe to get the latest news, industry trends, blog posts and updates.

5 1 vote
Article Rating
Notify of
Inline Feedbacks
View all comments

PostgreSQL Replication

Unlock Data Safety with PostgreSQL Replication

Discover the power of PostgreSQL replication in our guide. Learn how to safeguard your data with ease, step by step. No tech jargon, just straightforward protection for your valuable information

Oxygenate Pakistan

Oxygenate Pakistan

Talks by distinguished professionals from Arcana Info and WSO2, unpacking state of the art integration runtime for enterprise.

All digital services in one place

Explore our services and solutions to learn more about your industry options and in addition receive a quote from us.

Would love your thoughts, please comment.x