How to Setup Replication in PostgreSQL using pg_logical?

PostgreSQL is one of the most popular Object-Relational Database Management Systems that allow you to store, process, and analyze data using SQL. It is capable of highly promoting data integrity by maintaining Data Accuracy and Consistency throughout the data life cycle, achieving ACID compliance.

PostgreSQL not only has a Data Integrity feature but also has fault tolerance capability, which allows the database system to run continuously without any interruption when one or more of its components or instances fail due to unprecedented events. One such fault-tolerant function of PostgreSQL is pg_logical, which allows you to implement logical streaming replication for replicating or copying data from one database instance to another, thereby ensuring data availability across servers. 

In this article, you will learn about PostgreSQL, pg_logical extension, and how to implement logical replication in PostgreSQL using pg_logical extension.

What is PostgreSQL?

Introduced in the late 1980s, PostgreSQL is an Open-Source Object-Relational Database Management System with more than 30 years of active development history. PostgreSQL is a Relational Database System that allows you to store, manage, process, and fetch data present in the database servers or instances. Even though PostgreSQL has some object-oriented features for handling unstructured and non-relational data, it is extensively used as a relational database. Compare PostgreSQL with MySQL and choose which database suits your usecase.

Since PostgreSQL is an open-source DBMS, it is free and easy for everyone to download and run queries to perform data-related tasks like data filtering, wrangling, and processing. In addition, PostgreSQL is highly compatible with operating systems, including Windows, MACOS, and Linux, and supports multiple programming languages such as C, C++, Java, and Python.

What is pg_logical?

pg_logical is one of the extensions available in PostgreSQL, which allows you to implement a logical replication mechanism for replicating data between two database servers. The logical replication mechanism is also known as Transactional Replication, in which the subscriber (destination database server) receives a copy of the replicated database object from the publisher (source database server) whenever any real-time change or update is made on the source server. Look at the other replication types in PostgreSQL.

The pg_logical extension should be installed in both Source and Destination database instances to implement the replication process seamlessly. You have to install PostgreSQL version 10 or higher to implement the logical replication technique.

How to Setup Replication in PostgreSQL using pg_logical?

In the further steps, you will use Google Cloud SQL to set up replication in PostgreSQL using pg_logical. 

Prerequisites

For setting up logical replication in PostgreSQL using pg_logical, you have to satisfy certain prerequisites. You should already have 

• an account with Google Cloud SQL, 
• a fully managed database service to set up, organize, and 
• manage databases on the Google Cloud Platform. 

Google Cloud SQL is highly compatible with the most popular Database systems like PostgreSQL, MySQL, and SQL Server. For this tutorial, you will use PostgreSQL in Cloud SQL to set up replication using pg_logical. 

Step 1: Configuring the PostgreSQL instance

Step 1.1: Enable Logical Replication Settings

• Set the necessary flags in the Cloud SQL workspace:

cloudsql.logical_decoding = on
cloudsql.enable_pg_logical = on

• Now, you are all set to enable pg_logical extension on both primary (source) and replica (destination) instances. shared_preload_libraries = 'pg_logical'

Step 1.2: Enable Connections Between Primary and Replica Instances 

• Add the replica’s IP address to the primary instance’s authorized networks. You can refer to how to add an IP address to the authorized networks.

Step 1.3: Create a Replication User

• Create a new user account with replication privileges. Execute the following command to create a new replication user. 

CREATE USER replication_user WITH REPLICATION
IN ROLE cloudsqlsuperuser LOGIN PASSWORD 'secret';

Step 2: Installing pg_logical Extension

Step 2.1: Install the pg_logical extension. 

• Execute the command below to install the pg_logical extension.

CREATE EXTENSION pg_logical;

• After installing, you have to enable the pg_logical extension on both primary and replica instances. 

Step 2.2: Register Primary and Replica Instances as Nodes

• Now, you have to run the following command to enable the pg_logical extension on both the primary and replica instances to register them as separate nodes.

source-instance$ SELECT pg_logical.create_node(
       node_name := 'primary',
       dsn := ‘host=<primary-ip>=5432 dbname=postgres
       user=replication_user password=secret’
);
 
dest-instance$ SELECT pg_logical.create_node(
       node_name := 'replica',
       dsn := ‘host=<replica-ip>=5432 dbname=postgres
       user=replication_user password-secret’
);

Step 3: Setting Up Replication

Step 3.1: Create a Table with Sample Data on the Primary Instance

• Execute the following code to create a table named replica_test in the primary instance and insert some sample values into the table.

CREATE TABLE replica_test (id SERIAL PRIMARY KEY, data text);
INSERT INTO replica_test (data) VALUES ('apple'), ('banana'), ('cherry');

Step 3.2: Create the Same Table on the Replica Instance

• Now, you have to create the same table named replica_test in the replica instance so that it can accurately fetch changes from the primary instance and store them in appropriate columns.

CREATE TABLE replica_test (id SERIAL PRIMARY KEY, data TEXT);

Step 4: Create a Subscription on the Replica Instance

• Execute the following command to create a subscription.

SELECT pg_logical.create_subscription(
    subscription_name := 'test_sub',
    provider_dsn := 'host=<primary-ip> port=5432 dbname=postgres user=replication_user password=replicapassword'
);
SELECT * FROM pg_logical.show_subscription_status('test_sub');

• After executing the above command, if the status appears as “replicating” in the status bar, you can confirm that the replication setup was successful.

Following the above-mentioned steps, you successfully implemented logical replication in PostgreSQL using the pg_logical extension.

Limitations of pg_logical Extension

• Both the publisher (Source) and subscriber (Destination) instances must run on PostgreSQL 9.4+ versions to implement the logical replication process.
• Tables present on both the Publisher and Subscriber instances should have the same name and schema so that future additions or modifications map to the same table in the Subscriber instance.
• The provider and subscriber tables must have the same number of columns and data types.
• The subscriber instance’s CHECK constraints and NOT NULL constraints should be the same as the provider’s.
• The PRIMARY KEYs in all tables of both publisher and subscriber instances must be the same. Besides the PRIMARY KEY, it is not recommended to create any extra UNIQUE constraints.

Conclusion

In this article, you learned about PostgreSQL, a pg_logical extension, and how to implement logical replication in PostgreSQL using the pg_logical extension. Although this article mainly focused on setting up replication in PostgreSQL using the logical replication process, PostgreSQL also supports streaming replication or physical replication, which you can explore later. If you want to export data from a source of your choice into your desired database/destination like PostgreSQL, then Hevo Data is the right choice for you! 

While PostgreSQL is a good solution for Data Integration, it requires a lot of Engineering Bandwidth and expertise. This can be challenging, resource-intensive, and costly in the long run. Hevo offers a much simpler, scalable, and economical solution that allows people to create Data Pipelines without any code in minutes without depending on Engineering teams.

Want to take Hevo for a spin? Sign Up for a 14-day free trial. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of learning about how to use the pg_logical extension! Let us know in the comments section below!

Frequently Asked Questions