Achieving PostgreSQL Master Slave Replication: 7 Easy Steps

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!

What is PostgreSQL?

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?

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

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.

Steps to Achieve PostgreSQL Master Slave Replication

Prerequisites

• Basic familiarity with PostgreSQL concepts
• A slave server running a compatible operating system (in this guide, we use CentOS 7).
• Root or sudo access on the slave server.

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.

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

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

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.

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 150+ 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.

FAQ