PostgreSQL Logical Replication: A Live Standby Without Downtime

The short answer

Logical replication in PostgreSQL ships row-level changes from a "publisher" database to a "subscriber" through a publication and a subscription, instead of copying WAL bytes verbatim. You end up with a live second server that answers queries, can run a different major version from the source, and can receive only the tables you choose rather than the whole cluster.

Those properties make it the right tool for three jobs: upgrading between major versions (13 to 17, for example) with 5 to 30 seconds of downtime, moving a database to a new server while the application keeps running, and feeding a reporting database with specific tables without loading the primary. If your goal is an identical copy you can fail over to when the primary dies, physical streaming replication is simpler and more complete because it carries everything, schema changes and sequences included.

Setup comes down to three steps: set wal_level = logical on the publisher, create a publication listing the tables, then create a subscription on the second server pointing back at the first. An initial data copy runs, and after that changes stream across with lag usually measured in milliseconds between two servers in the same data center.

Physical or logical: the difference that decides it

Physical replication sends the WAL as is, so the standby becomes a byte-for-byte copy of the primary. That means the same major version and architecture, and the standby stays read-only. The upside is that you never think about it: every new table, index and sequence arrives on its own.

Logical replication decodes the WAL into logical operations (insert, update or delete on a specific row) and sends them to the subscriber, which applies them as ordinary transactions. The subscriber can therefore hold extra tables of its own, different indexes and a newer version. The price is that a few things do not travel, which we cover below.

Criterion	Physical	Logical
Unit of transfer	WAL blocks	Row changes
Different major version	Not possible	Possible
Subset of tables	No, whole cluster	Yes, table by table
Writes on the second server	Blocked	Allowed on other tables
Schema changes (DDL)	Replicated	Not replicated
Sequences	Replicated	Not replicated in common versions
Best fit	Failover	Upgrades, migrations, reporting

The working rule: pick physical for high availability and logical for moving between servers or versions. Many larger systems run both, physical for fast failover and logical to feed a reporting warehouse.

Step-by-step setup

On the publisher

In postgresql.conf set wal_level = logical and raise max_replication_slots and max_wal_senders to around 10, since each subscription consumes one slot and one sender and the initial copy temporarily takes more. Changing wal_level requires a full restart, so schedule it in a short maintenance window before anything else.

Create a dedicated replication user with the REPLICATION attribute and SELECT on the published tables, then add a pg_hba.conf line allowing it to connect only from the subscriber's IP, using scram-sha-256 rather than md5. Keep port 5432 closed to the world and let the firewall admit the subscriber alone.

Then create the publication: CREATE PUBLICATION erp_pub FOR TABLE orders, invoices, customers; or FOR ALL TABLES if you are moving everything. One condition matters a lot: every table that receives UPDATE or DELETE needs a primary key or an explicit REPLICA IDENTITY, otherwise the publisher itself will reject updates on that table, which breaks the application and not just replication. Audit tables without primary keys before you start.

On the subscriber

Logical replication does not carry the schema, so move it yourself with pg_dump --schema-only from the publisher and restore it on the subscriber. Then run CREATE SUBSCRIPTION erp_sub CONNECTION 'host=10.0.0.5 dbname=erp user=repl' PUBLICATION erp_pub; and it immediately begins an initial copy of each table before switching to streaming changes.

The initial copy of a 50GB database typically takes 20 to 60 minutes on SSD storage, depending on how many indexes the subscriber has to maintain while inserting. A useful trick is to drop heavy secondary indexes on the subscriber before subscribing and rebuild them once the copy finishes, which sometimes cuts the time in half.

Monitoring lag in bytes

On the publisher the key query is SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication; which gives you the byte distance between what the publisher last wrote and what the subscriber last applied. In normal operation it stays under 1MB. If it passes 100MB and keeps growing, the subscriber is falling behind, usually because of a slow disk, a huge transaction, or a conflict that stopped the subscription.

The real danger is an abandoned replication slot. A slot forces the publisher to keep every WAL segment the subscriber has not consumed, so if the subscriber stops for two days the publisher's disk fills and the primary database goes down with it. Watch pg_replication_slots and its active column, and set max_slot_wal_keep_size (available since version 13) to a cap such as 20GB so the publisher gives up on the slot before it chokes the disk.

On the subscriber, check pg_stat_subscription for the last received LSN and the time of the last message. Wire these numbers into your monitoring with alerts at 50MB of lag or a slot inactive for more than 10 minutes, since those two alerts save you from the worst outcome.

Limits to know before you rely on it

• Schema changes do not replicate: any ALTER TABLE or new table must be applied on the subscriber first and then on the publisher, or rows carrying an unknown column will fail to apply.
• Sequences do not replicate: row values arrive intact, but the subscriber's sequence counter stays at 1. Harmless during replication, it breaks the first insert after cutover unless you sync it by hand.
• Large objects are not replicated, so if your system stores files that way, logical replication alone is not enough.
• Conflicts stop the subscription: a duplicate row that violates a unique constraint on the subscriber halts apply until you step in, and from version 15 you can skip a specific transaction with ALTER SUBSCRIPTION ... SKIP.
• Huge transactions: updating 10 million rows in one transaction reaches the subscriber all at once after COMMIT, so lag jumps suddenly. The streaming = on option in recent versions softens this.

A major-version upgrade with seconds of downtime

The traditional upgrade is pg_dump and restore, which means hours of downtime for a 100GB database. In-place pg_upgrade is faster but still needs an outage and makes rollback awkward. Logical replication turns the upgrade into a simple cutover:

• Prepare a new server on the newer version and load the schema with pg_dump --schema-only.
• Create a publication for all tables on the old server and a subscription on the new one, then wait for the initial copy.
• Watch lag settle near zero and test the application against the new server with real reads.
• Stop writes on the old server using the application's maintenance mode and wait for lag_bytes to reach zero.
• Sync sequences by reading each value on the old server and applying it on the new one with setval, plus a margin of 1000.
• Point the application's connection at the new server, then drop the subscription.

The real outage is the gap between stopping writes and switching the connection, typically 5 to 30 seconds. Better still, the old server stays intact, so if something surfaces after cutover you can point the application back within a minute.

Sizing both servers on Cloud

Logical replication needs two servers of similar capacity, because the subscriber applies the same write volume the publisher receives. For an ERP with a database around 50GB and moderate load, the Business plan (4 vCPU, 8GB RAM, 40GB SSD, 1TB transfer) at 11,500 SAR a year with a +100GB SSD add-on at 500 SAR per server handles it comfortably. For systems under 15GB, the Growth plan (2 vCPU, 4GB RAM) at 6,900 SAR a year does the job.

Keeping both servers in the Riyadh data center puts internal network latency under a millisecond, so lag_bytes stays near zero even under write pressure. After the upgrade, keep the old server and turn it into a physical standby or a reporting node. Resource details are on the Cloud Server page, and the full comparison is on pricing and plans.

What to do next

• If all you need is failover, start with physical replication and keep it simple.
• If a version upgrade or server move is coming, audit tables without primary keys today, since they are the first blocker.
• Set max_slot_wal_keep_size before creating any slot, and add the lag alert before launch rather than after.
• Rehearse the whole process on a test copy and measure the initial copy time and the cutover time yourself before the production window.