Achieving PostgreSQL Master Slave Replication: 7 Easy Steps

on Data Integration, Data Replication, Database Management Systems, PostgreSQL, Tutorials • June 20th, 2020 • Write for Hevo

POSTGRESQL MASTER SLAVE REPLICATION

PostgreSQL is an extremely robust open-source database used by noted players like Skype, Reddit, Instagram, and OpenStreetMap. As the traffic on websites and applications increases the frequency of reading and writing data from the Database increases and so does the load.

PostgreSQL allows replication to nodes that can run read-only queries. If the primary server fails then to avoid the whole website or application to go down, one can use a secondary server. PostgreSQL Master Slave Replication allows companies to keep their business activities running even the one server fails because the website can access the replicated server.

PostgreSQL Master Slave Replication helps in scaling the application without worrying about system failure. This article walks you through the steps to achieve PostgreSQL Master Slave Replication. Read along to learn the steps and importance of PostgreSQL Master Slave Replication!

Table of Contents

Prerequisites

  • PostgreSQL master instance.
  • PostgreSQL slave instance.
  • PostgreSQL 10.

What is PostgreSQL?

PostgreSQL Master Slave Replication: PostgreSQL Logo | | Hevo Data
Image Source

PostgreSQL is an Open-source and free-to-use Object-Relational Database Management System that also supports both SQL (relational) and JSON (non-relational) querying making it easier for developers to build modern applications. PostgreSQL supports transactions with Atomicity, Consistency, Isolation, Durability (ACID) properties. 

PostgreSQL can easily connect with other Databases, applications, and services to store data such as making PostgreSQL partitions and loading data to business applications. Data Integration Platforms such as Hevo Data can smoothen your data movement from PostgreSQL to applications such as Google Data Studio, Power BI, etc. It automatically updates materialized views, foreign keys, triggers, and stored procedures.

Key Features of PostgreSQL

Some of the main features of PostgreSQL are listed below: 

  • Data Recovery: PostgreSQL offers data replication and point-in-time recovery features that ensure data recovery at a time of system failure. 
  • High Performance: PostgreSQL delivers fast query speed by parallelly reading queries and building B-tree indexes.
  • Security: PostgreSQL offers multiple authentication options such as SCRAM-SHA-256, Certificate, GSSAPI, SSPI, LDAP, etc, with the access control system.

To learn more about PostgreSQL, click here.

What is PostgreSQL Master Slave Replication?

PostgreSQL Master Slave Replication Process | Hevo Data
Image Source

It is very likely that your website or application has a lot of read-queries and very few write queries. In such a case, the ideal strategy would be to implement PostgreSQL Master Slave Replication which means replicating the master server into several slave servers. In PostgreSQL Master Slave Replication, the slave servers are used only for read queries and when there is a fail-over. In the event of a fail-over, one of the slaves can become a master.

PostgreSQL Master Slave Replication allows changes happening in the cluster of the primary database to be copied to the standby database on another server. What is the point of PostgreSQL Master Slave Replication? PostgreSQL Master Slave Replication distributes the database to several different machines. This way, we have a backup, and queries can be handled without downtime.

Importance of PostgreSQL Master Slave Replication

PostgreSQL Master Slave Replication | Hevo Data
Image Source

When you start to face a spike in your website traffic or when you finally start to have a large number of subscribers to your services, your database might start to slow down. Once your database becomes the weak link in the chain, it will end up being vulnerable to all sorts of unexpected failures.

So how should you protect your database from this problem? You could upgrade your server to make it more powerful and allow it to handle more queries. It does solve the database’s bottleneck problem. But it is still vulnerable. If it fails, you have nothing to fall back on.

Let us think of another solution. If we add multiple servers in parallel, not only do you have a scale but you also ensure that the failure of one instance does not collapse the entire system. So if the main server fails, you have a second server ready to take over. This is PostgreSQL Master Slave Replication. Or instead like other organizations, one can also use automated Data Replication tools such as Hevo that saves time and automated the process.

First of all, for implementing PostgreSQL Master Slave Replication, you need two server instances. Let us call one of them master_node and the other slave_node.

Replicate PostgreSQL Data Seamlessly Using Hevo’s No-Code Data Pipeline

Hevo Data, a Fully-managed ETL/ELT solution, can help you automate, and simplify your Replication process quickly. With Hevo’s support for a holistic number of connectors and quick Data Pipelines, you can extract load, and transform data from PostgreSQL or 150+ Data Sources (Including 40+ free sources) straight into your Data Warehouse, Database, or any destination of your choice. You can also use Hevo for automating your PostgreSQL Replication without writing a single line of code!

Get Started with Hevo for Free

“With Hevo in place, you can reduce your PostgreSQL Data Replication time & effort by many folds! Moreover, Hevo’s pre-built integrations with various Business Intelligence Tools such as Power BI, Tableau, and Looker can help you generate insights by connecting with Database or Data Warehouse”

Experience an entirely automated hassle-free No-code PostgreSQL Replication process. Try our 14-day full access free trial today!

Steps to Achieve PostgreSQL Master Slave Replication

Now that you have a brief knowledge of PostgreSQL Master Slave Replication and why it is important to use PostgreSQL Master Slave Replication for the smooth functioning of websites and applications. In this article, you will learn about the steps to achieve PostgreSQL Master Slave Replication. The following steps to implement PostgreSQL Master Slave Replication are listed below:

Step 1: Creating a User in the Master Node

In the master_node, create a user called user_name. In order to do that, in the master node, enter the following commands.

$ psql 
CREATE ROLE user_name REPLICATION LOGIN PASSWORD ‘my_pass123’;

You will get an output that looks like this:

CREATE ROLE

Step 2: Authentication

The next step is to authenticate the user. To do that, we will edit the pg_hba.conf file. You have to know the path where this file is installed for you.

sudo vim path/of/your/file/pg_hba.conf

Create a line where, USER is user_name, the address is slave_node and the method is md5.

PostgreSQL Master Slave Replication : User Authentication

The purpose of this step is to allow  ‘user_name’ to connect to all the databases from the slave node.

Step 3: Configuring the wal_level

The next step is to configure the wal_level. wal_level determines the amount of information written to the WAL. It needs to be changed from the default ‘replica’ to ‘logical’. This will add the necessary amount of info to the wal_log which will allow the slave_node to get access to the master_node and copy the tabular data.

To edit the postgres.conf file, enter the following command in the master_node:

sudo vim path/of your/file/postgres.conf
PostgreSQL Master Slave Replication : wal_level Configuration

Since you have made changes to the wal_level, you need to restart PostgreSQL.

Use this command to restart:

sudo service postgresql-10 restart

Step 4: Creating a Publication

In this step, we will be creating a publication. That is, we set up a table in a database in the master node so that the slave node can copy it

To get a list of the databases, enter the following command :

$psql
l

Connect to one of the databases in the list.

c my_db

Pick a table in the database my_db and create a PUBLICATION using the following command :

CREATE PUBLICATION my_pub FOR TABLE my_table;

Next, we will grant user_name all the rights to my_table using the following command :

GRANT ALL ON my_table TO user_name;

Step 5: Configuring the Slave Node

In this step, we will configure the slave node. The first thing to do is to create the my_db database using this command in slave_node.

createdb temp_db

Make sure that the pg_hba.conf file is prepared on the slave_node to allow the postgres user to authenticate from the master_node using the following command: 

sudo cat path/of/your/file/pg_hba.conf
PostgreSQL Master Slave Replication : Slave Node Configuration

Now go on master_node and type in the following command to get the structure of your data in my_table using a schema only dump. We do this step because PostgreSQL needs the table to exist in slave_node as well.

pg_dump temp_db –t my_table –s

You can now pipe this over to slave_node.

pg_dump temp_db –t my_table –s | psql temp_db –h slave_node

This command will pipe the schema of my_table into the host – slave_node. The data inside the table doesn’t come with this because we have specified that we are piping schema only with the -s.

If you now check, you will find that both master_node and slave_node have my_table in the my_db database, but in the slave node, we only have the schema with the table being empty.

Step 6: Creating a Subscription in the Slave Node

Now let us begin replication to move the data in this table to the slave node. We do this by creating a SUBSCRIPTION.

Go to slave_node instance and enter the command stated below :

CREATE SUBSCRIPTION my_sub CONNECTION ‘dbname = my_db host = master_node user = user_name password = my_pass123’ PUBLICATION my_pub;

This will create the replication slot on the publisher.

Step 7: Verifying PostgreSQL Master Slave Replication

In order to verify the PostgreSQL Master Slave Replication, you can enter this command in slave_node instance.

SELECT * FROM my_table;

Now you should see the data inside the table in the slave node instance as well.

To continue the verification further, go back to master_node, to enter the below command :

INSERT INTO my_table (column_1) VALUES (value);

Go back to slave_node and verify if the change can be noticed on the slave node instance.

That’s it! You have successfully achieved PostgreSQL Master Slave Replication.

What Makes Hevo’s PostgreSQL Replication Process Interesting

Manually implementing PostgreSQL Master Slave Replication can be a tedious process and having an efficient automated solution is essential to keep business applications and services keep active all the time! This problem and many more can be solved using Hevo’s No-Code Data Pipeline seamlessly.

This PostgreSQL Replication tool lets you connect to 150+ Data Sources (including 40+ free sources) and leverage Hevo’s blazing-fast Data Pipelines to help you seamlessly extract, transform, and load data or to set up your PostgreSQL Clusters in a matter of minutes without writing any code!

Check out why Hevo is the Best:

  • Auto Schema Mapping: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
  • Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo.

Conclusion

The goal of replication is to make your Database more available and secure. In this article, you saw how logical PostgreSQL Master Slave Replication can be achieved with  PostgreSQL. The PostgreSQL Master Slave Replication allows master and slave instances can now communicate with each other and your data will now be more secure.

But in order to set up PostgreSQL Master Slave Replication using the above approach, you need to manually configure many details and that can eat away a lot of time and effort. The PostgreSQL Master Slave Replication process requires a very detailed understanding of PostgreSQL and data engineering concepts. Moreover, this article has shown the PostgreSQL Master Slave Replication process using the example of some simple data. But the reality is that a lot of data needs to be transformed before loading it into its destination.

Hevo Data provides an Automated No-code Data Pipeline that empowers you to overcome the above-mentioned limitations. Hevo caters to 100+ data sources (including 40+ free sources) and can seamlessly perform PostgreSQL Replication in real-time. Furthermore, Hevo’s fault-tolerant architecture ensures a consistent and secure replication of your PostgreSQL data. Using Hevo will make your life easier and make Data Replication hassle-free.

Visit our Website to Explore Hevo

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

What are your thoughts on PostgreSQL Master Slave Replication? We would love to hear from you in the comments.

No-code Data Pipeline for PostgreSQL