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:
Step 1: Installing PostgreSQL on the Secondary Server
First, we need to install PostgreSQL on your secondary server.Â
Here’s how:
Â
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
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:
Â
Â
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:
Â
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replica';
psql -c "show hba_file;"
# Go to the directory and add this line to pg_hba.conf:
echo "host replication replicator 192.168.1.100/32 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 192.168.1.101 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 192.168.1.101 -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.conf 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:
#ON PRIMARY DATABASE
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');
#ON SLAVE DATABASE
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.
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.
Development of cloud applications following agile methods and strategies to ensure the use of microservices and advanced APIs.
Containerized solutions for the deployment of modern and advanced distributed applications.
Subscribe to get the latest news, industry trends, blog posts and updates.
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
Mobile applications have become a crucial aspect of modern-day businesses. From enhancing customer engagement to streamlining internal processes, mobile apps offer a wide range of benefits.
Talks by distinguished professionals from Arcana Info and WSO2, unpacking state of the art integration runtime for enterprise.
API lifecycle management, configuration management, and modernized CI/CD deployment options.
Explore our services and solutions to learn more about your industry options and in addition receive a quote from us.
