Master in PostgreSQL DBA |Join Free Demo Session on 19 May 2025 at 7.30 PM IST| Register for Free Demo

Know More

Setting Up Logical Replication in PostgreSQL: A Step-by-Step Guide

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarPradip
  • 29 Apr, 2025
  • 0 Comments
  • 5 Mins Read

Setting Up Logical Replication in PostgreSQL: A Step-by-Step Guide

Imagine this, you’re managing a high-traffic PostgreSQL database, and suddenly, you need to migrate data, perform a version upgrade, or set up a real-time reporting system. But there’s a catch: downtime is not an option!

This is where logical replication becomes a lifesaver. Unlike traditional replication, which blindly copies everything, logical replication gives you complete control over what gets replicated. You can sync only specific tables, making it perfect for large-scale applications, real-time analytics, and seamless migrations.

In this article, I’m going to walk you through every step, from enabling replication to monitoring and troubleshooting issues. Whether you’re a DBA, data engineer, or developer, you’ll find this guide practical, easy to follow, and packed with real-world use cases.

By the end of this guide, you’ll not only understand logical replication but also set it up like a pro, without breaking a sweat! So, let’s dive in and unlock the full potential of PostgreSQL’s logical replication.

Got it, Ankush! I’ll make the article more personal, like you’re guiding the reader step by step. I’ll also add a bit more detail to make it as complete and engaging as possible. Here’s the refined version with a personal touch:

1. What is Logical Replication in PostgreSQL?

Before we get into the setup, let’s quickly understand what logical replication is and why you should care about it.

Breaking it Down: The Publisher-Subscriber Model

PostgreSQL’s logical replication follows a publisher-subscriber model:

Publisher→ The main server that sends out changes.

Subscriber → The receiving server that applies those changes.

Unlike physical replication (which copies the entire database), logical replication is table-level. That means you can choose exactly what you want to replicate, which makes it super efficient and flexible.

Where is Logical Replication Used?

I know what you’re thinking—”Is this actually useful for real-world applications?” The answer is YES! Some big players rely on logical replication for critical operations:

  • Netflix→ Syncs data globally for real-time video analytics.
  • Uber→ Tracks live ride requests across different regions.
  • Banking & Fintech → Ensures zero downtime during software upgrades.

When Should You Use It?

  • You need to sync specific tables(instead of the whole DB).
  • You want to upgrade PostgreSQL without downtime.
  • You need real-time analyticsfrom production data.
  • You want to migrate data from one server to another without locking tables.

When Should You NOT Use It?

  • If you need full database backups → Use physical replicationinstead.
  • If you have very high write workloads → Logical replication may lag.
  • If you expect schema changes (DDL statements) → Those are NOT replicated!

Now that you’ve got a solid understanding, let’s set this up!

2. Step-by-Step: Setting Up Logical Replication in PostgreSQL

Step 1: Enable Logical Replication on Your Server

Before you do anything, you must configure your PostgreSQL server to allow logical replication.

Open the postgresql.conf file:

sudo nano /etc/postgresql/15/main/postgresql.conf

Modify (or add) these settings:

wal_level = logical         # Enables logical replication
max_replication_slots = 5   # Number of replication slots
max_wal_senders = 5         # Number of concurrent replication connections

Now, restart PostgreSQL to apply changes:

sudo systemctl restart postgresql

Pro Tip: If your replication is lagging, try increasing max_wal_senders to 10 or more.

Step 2: Set Up the Publisher (Main Server)

Now, let’s tell PostgreSQL which table(s) we want to replicate.

Connect to your main PostgreSQL server (Publisher):

CREATE PUBLICATION my_publication FOR TABLE employees;

This creates a publication named my_publication for the employees table.

Step 3: Allow the Subscriber to Connect

We need to grant permissions so that the subscriber server can connect.

Run this command on the Publisher:

ALTER ROLE replication_user WITH REPLICATION;

Then, modify pg_hba.conf (to allow remote replication connections):

host    replication     replication_user    192.168.1.100/24   md5

Restart PostgreSQL again:

sudo systemctl restart postgresql

That’s it! The publisher is now ready.

Step 4: Set Up the Subscriber (Replica Server)

On your replica server (subscriber), create the same table:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary NUMERIC
);

Now, connect it to the publisher:

CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=192.168.1.10 dbname=company user=replication_user password=secret'
PUBLICATION my_publication;

This tells PostgreSQL to pull changes from the main server.

Check if the subscription is active:

SELECT * FROM pg_subscription;

If you see an active status, congratulations—you’ve successfully set up logical replication! 🎯

3. Testing Logical Replication

Alright, let’s see if our setup actually works!

Go to the publisher and insert some test data:

INSERT INTO employees (name, salary) VALUES ('John Doe', 70000);

Now, check the subscriber:

SELECT * FROM employees;

If John Doe appears on the subscriber, congratulations! You’ve successfully set up logical replication in PostgreSQL.

4. Monitoring and Troubleshooting

Sometimes, things don’t work as expected. Here’s how to check and fix common issues:

Checking Replication Status

Run this on the subscriber to see if it’s lagging:

SELECT * FROM pg_stat_subscription;

If lag is high, increase max_wal_senders on the publisher.

Checking Replication Slots

On the publisher, see active replication slots:

SELECT * FROM pg_replication_slots;

If a slot is inactive, restart the subscriber.

Handling Conflicts

If you inserted data manually in the subscriber, replication might stop. You’ll need to:

DELETE FROM employees WHERE id = 5;

Then restart the subscriber.

5. What’s Next? Advanced Use Cases!

Now that you’ve got logical replication working, here are some cool things you can do next:

  • Replicate multiple tables→ Just add them to the publication.
  • Stream data to Apache Kafka → Combine PostgreSQL replication with Kafka for real-time analytics.
  • Sync with Cloud Databases → AWS RDS, Google Cloud SQL, or Azure Database for PostgreSQL.

Conclusion: Master PostgreSQL Logical Replication with Expert Guidance

Logical replication is a must-know skill for any DBA, data engineer, or developer working with PostgreSQL. Whether you’re handling real-time analytics, seamless migrations, or zero-downtime upgrades, mastering this feature will make you stand out in the industry.

At Learnomate Technologies, we provide the best hands-on training on PostgreSQL, covering logical replication, performance tuning, high availability, and more. Our expert-led courses ensure that you not only understand the concepts but can apply them in real-world scenarios.

Want to dive deeper? Check out our detailed PostgreSQL training program 👉 Click here

Prefer video explanations? Subscribe to our YouTube channel for insightful tutorials 👉 www.youtube.com/@learnomate

Let’s connect! Follow me on LinkedIn for more valuable insights 👉 Ankush Thavali

Want to explore more tech topics? Read our latest blogs 👉 Learnomate Blog Page

Keep learning, keep growing! Let’s take your PostgreSQL skills to the next level. Drop your thoughts in the comments, I’d love to hear from you! 😊

Happy Reading🙌!

ANKUSH😎

Monitoring PostgreSQL Performance: Tools and Techniques
Top 10 Performance Tuning Tips for Oracle RAC DBAs