PostgreSQL Logical Replication Sync

You've built a perfect PostgreSQL schema, your indexes are tuned, and your queries fly — but now the business asks for a real-time analytics copy of just three tables from your production database, not the whole thing. You could dump and restore nightly, but that's stale by morning. Streaming the entire WAL to a standby would work, but it replicates everything, including that 2TB log table you don't need on the analytics box. This is where logical replication shines: it lets you pick exactly which tables — even which rows — get synced, with minimal impact on your primary. In this lesson, you'll learn how to use logical replication for selective sync, from the core concepts to a working hands-on setup, and how to avoid the gotchas that trip up even experienced DBAs.

The problem this lesson solves

Traditional PostgreSQL replication is physical: a standby replays the write-ahead log (WAL) byte-for-byte, producing an exact copy of the entire cluster. That's great for failover, but it's a blunt instrument for data distribution. You can't say, "replicate only the orders and customers tables to the reporting instance," or "only rows where region = 'EU'."

Shipping full dumps over the network is slow, and they're point-in-time snapshots — by the time they load, they're already outdated. This forces teams into a choice between stale analytics, massive bandwidth bills, or complex ETL pipelines that poll for changes.

Logical replication solves this by decoupling the what from the how. It uses a publish-subscribe model where a publication on the source defines which tables (and optionally which rows) to expose, and a subscription on the target pulls those changes in real time. You get selective, incremental sync without touching your application code or locking your tables.

Why now? Modern data pipelines demand near-real-time sync for dashboards, search indexes, or event-driven microservices. Logical replication is the native, low-maintenance answer built right into PostgreSQL — no extra tools required (for most use cases).

Core concept / mental model

Think of logical replication as a newsletter service for your database.

• The publisher (primary) runs a publication — like a mailing list — that says, "I'll send updates about these tables (and maybe only certain rows) to anyone who subscribes."
• The subscriber (standby or another database) runs a subscription — like signing up for that newsletter — and applies each change as it arrives.
• The wire protocol carries the changes as logical rows, not raw WAL bytes. That's why the subscriber can have a different schema (as long as columns match), a different PostgreSQL version, and even run on different hardware.

This is fundamentally different from physical streaming replication:

Key definitions you'll encounter:

• Publication: A named object on the source that specifies which tables to publish. It can include all tables in a schema, specific tables, or filtered rows.
• Subscription: A named object on the target that connects to a publication and applies changes.
• Replication slot: On the publisher, it tracks the subscriber's progress in the WAL, ensuring changes aren't discarded until the subscriber confirms them.

How it works step by step

Setting up logical replication involves several moving parts. Here's the logical flow:

1. Configure the publisher — set wal_level = logical in postgresql.conf (or via ALTER SYSTEM), which adds the information needed for logical decoding.
2. Create a publication on the source, specifying which tables to publish. You can filter rows using a WHERE clause.
3. Create a replication slot (optional — it's created automatically when you create a subscription, but you can also create one manually to manage it).
4. On the target server, create the same tables (or compatible ones) that you want to receive.
5. Create a subscription on the target, pointing to the connection string and publication name.
6. The initial sync happens automatically: the subscription copies the current snapshot of the published tables, then starts streaming incremental changes.
7. Monitor and manage — check pg_stat_subscription and pg_subscription_rel to see sync status.

Hands-on walkthrough

Let's walk through a complete example. Assume you have a primary database appdb with a table orders that you want to replicate selectively to a reporting database reports — only rows with status = 'paid'.

1. On the publisher (source)

First, set wal_level to logical. You can do this dynamically (since PostgreSQL 9.6) with:

ALTER SYSTEM SET wal_level = logical;
SELECT pg_reload_conf();

Pro tip: You don't need to restart the whole server — pg_reload_conf() applies that change immediately. But if you're on an older version, you may need a restart.

Now create a publication for the orders table with a row filter:

-- On the publisher
CREATE TABLE orders (
    id serial PRIMARY KEY,
    customer_id int,
    total numeric(10,2),
    status text
);

INSERT INTO orders (customer_id, total, status) VALUES
(1, 99.99, 'paid'),
(2, 45.50, 'pending');

CREATE PUBLICATION orders_pub FOR TABLE orders WHERE (status = 'paid');

You can verify the publication:

SELECT * FROM pg_publication_tables;
--  pubname     | schemaname | tablename 
-- -------------+------------+-----------
--  orders_pub  | public     | orders

2. On the subscriber (target)

First, create the target table. It must have the same columns, but it can have different indexes or constraints. Include a primary key or a REPLICA IDENTITY if you want updates/deletes to propagate correctly.

-- On the subscriber
CREATE TABLE orders (
    id integer PRIMARY KEY,
    customer_id int,
    total numeric(10,2),
    status text
);

Now create a subscription that connects to the publication. Use a connection string that points to the publisher:

CREATE SUBSCRIPTION orders_sub
CONNECTION 'host=primary_host dbname=appdb user=replication_user password=secret'
PUBLICATION orders_pub;

PostgreSQL will automatically copy the current data (only rows where status='paid') and then start streaming changes.

3. Verify the sync

On the subscriber, check that data arrived:

SELECT * FROM orders;
--  id | customer_id | total | status 
-- ----+-------------+-------+--------
--   1 |           1 | 99.99 | paid
-- (1 row)

Only the paid order was replicated. Now, insert a new paid order on the publisher:

-- On publisher
INSERT INTO orders (customer_id, total, status) VALUES (3, 120.00, 'paid');

Wait a moment, then check again on the subscriber:

SELECT * FROM orders;
--  id | customer_id | total | status 
-- ----+-------------+-------+--------
--   1 |           1 | 99.99 | paid
--   3 |           3 | 120.00| paid
-- (2 rows)

The new row appeared automatically — no manual intervention.

4. Wrapping up later

If you want to drop the subscription, you should disable it first to avoid orphaned replication slots:

ALTER SUBSCRIPTION orders_sub DISABLE;
ALTER SUBSCRIPTION orders_sub DROP;

Compare options / when to choose what

Logical replication is not the only way to sync data. Let's compare it with alternatives:

When to choose logical replication:

• You need only a subset of tables or rows.
• You want near-real-time updates without polling.
• You are migrating between PostgreSQL versions.
• You can tolerate the target having a slightly different schema (e.g., extra indexes).

When NOT to choose it:

• You need DDL changes (like ALTER TABLE) to replicate — they won't.
• You need synchronous replication for failover (use physical for that).
• You have very high write throughput — logical decoding adds overhead.

Troubleshooting & edge cases

Even with a clean setup, things can go wrong. Here are common issues and fixes.

1. Subscription is stuck in "initializing"

If the subscription stays in that state, check:

SELECT * FROM pg_stat_subscription;

• Cause: The initial snapshot copy is happening, or there's a connection error.
• Fix: Check the publisher log for authentication issues. Ensure the replication user has REPLICATION privilege:

CREATE USER replication_user WITH REPLICATION LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE appdb TO replication_user;
GRANT SELECT ON orders TO replication_user;

Also, make sure pg_hba.conf allows replication connections.

2. Updated rows disappear or don't update on the subscriber

This happens when the table has no primary key or replica identity. Logical replication uses the primary key to match rows for updates/deletes. Without one, updates become inserts or fail.

• Fix: Add a PRIMARY KEY or at least REPLICA IDENTITY USING INDEX on both tables. On the publisher, you can set:

ALTER TABLE orders REPLICA IDENTITY FULL;

This tells PostgreSQL to use all columns to identify rows — but it increases WAL size.

3. Subscription fails with "publication does not exist"

This usually means the subscription points to a publication name that doesn't exist on the publisher. Double-check names and case sensitivity.

4. Data type mismatches between publisher and subscriber

If the column types aren't compatible, apply errors will occur. Use the same types, or make sure PostgreSQL can cast between them.

5. Replication slot growing unboundedly

If the subscriber is offline for a long time, the replication slot on the publisher can grow, consuming disk. Monitor with:

SELECT * FROM pg_replication_slots;

If you don't need the slot anymore, drop it, or consider setting a max_slot_wal_keep_size.

What you learned & what's next

By now, you understand the core idea behind using logical replication for selective sync: a publication on the source defines what to share, and a subscription on the target pulls only those changes — table by table, row by row. You can explain the mental model (newsletter), set up a real publication with row filters, create a subscription, verify initial sync and live streaming, and troubleshoot common pitfalls like missing REPLICA IDENTITY or stuck subscriptions.

You've also completed a practical exercise — replicating only paid orders between two databases — which is a pattern you'll reuse for real-world scenarios like analytics replicas, microservice data sharing, or zero-downtime upgrades.

What's next? In the next lesson, you'll explore how to monitor and scale this replication, including handling schema changes, using multiple publications, and even upgrading to newer PostgreSQL versions with minimal downtime. Master selective sync, and you'll have a powerful tool for building responsive, distributed data systems.