Setting Up Logical Replication in PostgreSQL: A Step-by-Step Guide
March 26, 2025
Introduction
Imagine managing a high-traffic PostgreSQL database and suddenly needing to migrate data, perform a version upgrade, or set up a real-time reporting system. However, downtime is not an option.
This is where logical replication becomes a lifesaver. Unlike traditional replication, which blindly copies everything, logical replication offers 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 guide, we will walk through every step, from enabling replication to monitoring and troubleshooting. 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 be able to set it up like a pro. Let’s dive in and unlock the full potential of PostgreSQL’s logical replication.
1. What is Logical Replication in PostgreSQL?
Understanding 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 operates at the table level. This allows you to choose exactly what data to replicate, making it efficient and flexible.
Use Cases of Logical Replication
Logical replication is widely used in production environments. Some real-world applications include:
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 to Use Logical Replication
Use logical replication if:
You need to sync specific tables instead of the whole database.
You want to upgrade PostgreSQL without downtime.
You need real-time analytics from production data.
You want to migrate data without locking tables.
When NOT to Use Logical Replication
Avoid logical replication if:
You need full database backups → Use physical replication instead.
You have very high write workloads → Logical replication may lag.
You expect schema changes (DDL statements) → These are not replicated.
Now that you have a solid understanding, let’s set up logical replication!
2. Step-by-Step: Setting Up Logical Replication in PostgreSQL
Step 1: Enable Logical Replication on Your Server
Before setting up replication, configure your PostgreSQL server.
Open the postgresql.conf file:
sudo nano /etc/postgresql/15/main/postgresql.confModify (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 connectionsRestart PostgreSQL to apply changes:
sudo systemctl restart postgresqlPro Tip: If replication lags, increase max_wal_senders to 10 or more.
Step 2: Set Up the Publisher (Main Server)
Define the tables to replicate. Connect to your PostgreSQL Publisher server and execute:
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
Grant permissions so that the subscriber can connect.
Run this command on the Publisher:
ALTER ROLE replication_user WITH REPLICATION;Modify pg_hba.conf to allow remote replication connections:
host replication replication_user 192.168.1.100/24 md5Restart PostgreSQL:
sudo systemctl restart postgresqlNow, the publisher is ready.
Step 4: Set Up the Subscriber (Replica Server)
On the Subscriber server, create the same table:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
salary NUMERIC
);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;Check if the subscription is active:
SELECT * FROM pg_subscription;If the status is active, logical replication is successfully set up! 🎯
3. Testing Logical Replication
To verify replication:
Insert test data on the Publisher:
INSERT INTO employees (name, salary) VALUES ('John Doe', 70000);Check the Subscriber:
SELECT * FROM employees;
If John Doe appears in the subscriber, replication is working!
4. Monitoring and Troubleshooting
Checking Replication Status
Run this on the Subscriber to check if replication is lagging:
SELECT * FROM pg_stat_subscription;If lag is high, increase max_wal_senders on the Publisher.
Checking Replication Slots
On the Publisher, view active replication slots:
SELECT * FROM pg_replication_slots;If a slot is inactive, restart the Subscriber.
Handling Conflicts
If data was inserted manually into the Subscriber, replication may stop. Fix it by:
DELETE FROM employees WHERE id = 5;Then restart the Subscriber.
5. What’s Next? Advanced Use Cases
Once logical replication is working, you can:
Replicate multiple tables → 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
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 enhance your PostgreSQL expertise and make you stand out in the industry.
By following this guide, you now have the knowledge to set up and manage logical replication like a pro! 🚀
