Postgres logical replication: slots, limitations, setup, and logical vs physical replication

What is logical replication in PostgreSQL?

Logical replication copies data changes between PostgreSQL databases based on their identity, usually a primary key, rather than by copying disk blocks. The publisher decodes its write-ahead log back into row-level events, and the subscriber applies those events to its own copy of the table. Because the changes are decoded rather than raw, the two sides do not have to be byte-identical, do not have to run the same major version, and do not have to hold the same set of tables.

That flexibility is the whole point. A physical standby is an exact clone of an entire cluster and you cannot write to it. A logical subscriber is an ordinary database that happens to receive a stream of changes for some of its tables, so it can carry extra indexes, extra tables, and its own workload. This is what makes it the standard way to run a near-zero-downtime major version upgrade, and a reasonable way to build a reporting replica that analysts can hammer without anyone paging the on-call engineer.

Postgres logical replication vs physical replication

The two are solving different problems and the choice is usually obvious once you write down what you actually need. Physical replication is for keeping a spare copy of the whole database ready to take over. Logical replication is for getting specific data somewhere else that is going to do something different with it.

One practical consequence people miss: because logical replication crosses major versions, it is the mechanism behind most modern PostgreSQL upgrades. You stand up the new version empty, replicate into it while the old one keeps serving traffic, wait for the lag to reach zero, then switch the application over. The outage is however long it takes to repoint a connection string, not however long pg_upgrade takes on a large database.

Postgres logical replication setup, step by step

The basic setup is genuinely four steps, and the PostgreSQL documentation notes that everything except wal_level works on default values for a simple configuration.

1. Set wal_level on the publisher. In postgresql.conf:

wal_level = logical

This one needs a restart, so plan it. On Amazon RDS and Aurora the equivalent is the static parameter rds.logical_replication set to 1, followed by an instance reboot.

2. Allow the replication user in pg_hba.conf. The exact line depends on your network, but the shape is:

host    all    repuser    0.0.0.0/0    scram-sha-256

3. Create the publication on the source database.

CREATE PUBLICATION mypub FOR TABLE users, departments;

You can also publish every table with FOR ALL TABLES, or a whole schema, or add a WHERE clause to replicate only the rows that match a condition. Start narrow. A publication is easy to extend later and painful to trim once something downstream depends on it.

4. Create the subscription on the target database.

CREATE SUBSCRIPTION mysub
  CONNECTION 'dbname=foo host=bar user=repuser'
  PUBLICATION mypub;

That starts the process immediately. PostgreSQL first copies the existing contents of the published tables, then begins streaming incremental changes. The one prerequisite nobody mentions until it fails: the tables must already exist on the subscriber, with compatible column definitions, because the schema does not travel with the data.

Postgres logical replication limitations

These are documented restrictions rather than bugs, and PostgreSQL lists them plainly. Read this table before you commit to native replication, because two or three of these decide the answer for most teams.

Postgres logical replication sequences: the failover trap

This is the one worth reading twice. Sequence data is not replicated. If a table has a serial or identity column, the values in that column replicate perfectly well, because they are just column data. The sequence object behind the column does not. On the subscriber it still reports its original start value.

Nothing looks wrong while the subscriber is read-only. The damage arrives on the day you promote it. The table contains ids up to 4,812,900 and the sequence is sitting at 1, so the first insert tries to reuse an id that has existed for two years and hits a unique violation, and it keeps doing that for every insert after it. If a switchover or failover is anywhere in your plan, advancing sequences from the publisher belongs in the runbook as an explicit step, using setval driven from either the current sequence values or the maximum id in each table.

Postgres logical replication DDL and schema changes

DDL does not replicate, so the two sides drift the moment somebody runs a migration. The failure mode depends on which side changes first, and the ordering rule is simple enough to put in a deployment checklist.

Add a column to the publisher first and replication breaks: rows arrive carrying a column the subscriber has never heard of, and the apply worker errors out until you add it there too. Add it to the subscriber first and nothing breaks at all, because the extra column simply takes its default until data starts arriving for it. So the rule is that additive changes go to the subscriber first, then the publisher. Destructive changes go the other way round.

PostgreSQL is explicit that the schemas do not have to be identical on both sides, which is more useful than it sounds. The subscriber can carry extra columns, extra indexes, and extra tables that the publisher knows nothing about. What it cannot do is be missing something the publisher sends. Before you drop a column that is being published, it pays to know what downstream actually reads it, which is the kind of question a column-level lineage map answers in seconds and a code search answers badly.

What is a Postgres logical replication slot?

A replication slot is a named bookmark on the publisher that records how far a particular consumer has read. Its purpose is to guarantee that PostgreSQL does not recycle write-ahead log files the consumer still needs, even if that consumer disconnects for a while. Each subscription creates and holds one automatically.

That guarantee is also the trap. If nothing is consuming the slot, the publisher keeps retaining WAL on its behalf, and by default there is no ceiling on how much. Pause a subscription over a long weekend, or drop a subscriber without dropping its slot, and pg_wal grows until the volume is full and the database stops accepting writes. Set max_slot_wal_keep_size to a real limit before going live, and alert on slots whose lag is growing. The same mechanism, used by external consumers rather than a subscription, is what every CDC product is built on, and it is covered in detail in our guide to Postgres change data capture.

How do I check logical replication status in Postgres?

Three views tell you almost everything. On the publisher, pg_replication_slots shows every slot, whether it is active, and how much WAL it is retaining. pg_stat_replication shows the live streaming connections and their lag. On the subscriber, pg_stat_subscription shows each subscription worker and the last message it applied.

The query worth putting on a dashboard is the retained-WAL one, because it is the one that ends in an outage:

SELECT slot_name,
       active,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

Any slot showing active as false with retained_wal climbing is a countdown to a full disk. Either fix the consumer or drop the slot, and do it before it becomes interesting.

Server settings for logical replication

Only wal_level requires attention in a basic setup. The rest matter once you have more than one subscription or a large initial copy. These were read from the PostgreSQL 18 configuration documentation on 16 August 2026.

When logical replication is the wrong tool

Logical replication is Postgres to Postgres. The moment the destination is Snowflake, BigQuery, Salesforce or a spreadsheet, it stops being an option and you are choosing a pipeline instead. It also does no transformation whatsoever: rows land exactly as they left, so if the target needs different column names, different types, currency conversion or filtering beyond a WHERE clause, something else has to do that work.

The honest test is whether both ends are PostgreSQL and you want an identical copy of some tables. If yes, use logical replication and buy nothing, because it is free, native and already installed. If either end is something else, or the data has to change shape on the way, compare the options on Postgres ETL tools, which lines up the native paths against the managed platforms and covers the same restrictions from the buying side.

For the specific lanes out of Postgres, the detailed pages are Postgres to Snowflake and Postgres to BigQuery, and for the reverse direction, Snowflake to Postgres. If you are still deciding where the transformation should happen, ETL versus ELT covers that trade directly.