Logical Replication in PostgreSQL: What It Is and How It Works

Logical replication in PostgreSQL is a method to copy data changes from one database to another in real time by sending the actual data changes (inserts, updates, deletes). It allows selective replication of tables and supports replication between different PostgreSQL versions.

How It Works

Logical replication works like a live messenger that watches changes in your database and sends only the actual data updates to another database. Imagine you have a notebook where you write daily notes, and you want a friend to have the same notes. Instead of copying the whole notebook every time, you just tell your friend what new notes you wrote or what you erased.

In PostgreSQL, this is done by creating a publication on the source database, which defines what tables to share, and a subscription on the target database, which listens and applies those changes. This way, only the changed rows are sent, not the entire database, making it efficient and flexible.

Example

This example shows how to set up logical replication between two PostgreSQL databases on the same server for a table named products.

sql

CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price NUMERIC);

-- On the publisher (source) database:
CREATE PUBLICATION my_pub FOR TABLE products;

-- On the subscriber (target) database:
CREATE SUBSCRIPTION my_sub CONNECTION 'host=localhost dbname=source_db user=replicator password=secret' PUBLICATION my_pub;

Output

CREATE TABLE
CREATE PUBLICATION
CREATE SUBSCRIPTION

When to Use

Use logical replication when you want to keep specific tables in sync between databases, especially if they are on different PostgreSQL versions or if you need to replicate only part of the data. It is great for:

• Scaling reads by replicating data to multiple read-only servers.
• Upgrading PostgreSQL versions with minimal downtime.
• Distributing data selectively to different locations or applications.
• Maintaining audit or reporting databases that need fresh data.

Key Points

• Logical replication sends data changes (inserts, updates, deletes) instead of whole database files.
• It uses publications and subscriptions to control what data is replicated.
• Supports selective table replication and cross-version replication.
• Requires some setup but offers flexibility and real-time data sync.

Key Takeaways

Logical replication copies data changes in real time between PostgreSQL databases.

It uses publications on the source and subscriptions on the target to control replication.

Ideal for selective table replication, version upgrades, and scaling read workloads.

Only changed data is sent, making it efficient and flexible.

Setup requires creating tables, publications, and subscriptions with proper permissions.