Complete Setup Guide for PostgreSQL 18 Logical Replication Failover

Updated on Sep 24, 2026
Mila H
9 MINS READ
Table of Contents
PostgreSQL 18 Logical Replication Failover

If your primary PostgreSQL server crashes, your logical replication normally breaks. The subscriber loses its slot, and you have to rebuild everything from scratch. PostgreSQL 18 fixes this problem with synchronized failover slots. In this guide, you will learn how to set up a complete PostgreSQL 18 logical replication failover.

What Is PostgreSQL 18 Logical Replication Failover

Logical replication lets you copy specific tables or databases to another server using a publication and a subscription. Before PostgreSQL 17, there was a big problem. If the primary server died, its logical replication slot died too. The subscriber had no way to know where to pick up on a new server.

PostgreSQL 17 introduced failover-ready logical slots. PostgreSQL 18 makes this feature more solid and reliable. With this failover mechanism, the primary copies its logical slots to a physical standby in the background. If the primary goes down and the standby gets promoted, the subscriber just switches to the new primary and keeps going. No need to re-copy data, and no broken pipeline.

This matters if you run production databases, where downtime means lost money. Also, if you cover high availability topics, you can check out the pgBackRest backups and recovery tutorial too, which covers backups and point-in-time recovery. This guide covers live failover for a running replication pipeline.

Lab Architecture: Primary, Standby, and Subscriber

To test PostgreSQL 18 logical replication failover properly, you need three separate servers or virtual machines:

  • Node A (Primary): IP 10.0.0.1, runs the main database and the publication.
  • Node B (Standby): IP 10.0.0.2, a physical standby of Node A, using streaming replication, and it will hold the synced failover slots.
  • Node C (Subscriber): IP 10.0.0.3, a separate PostgreSQL 18 server that subscribes to the publication on Node A.

Note: All three nodes must run PostgreSQL 18, the latest stable release at the time of this guide. Mixing versions between primary and standby is not supported for physical replication.

You can use three PerLod VPS or dedicated server hosting for this lab. Keeping the nodes on separate machines is closer to a real production setup than testing everything on one laptop.

Step 1: Install PostgreSQL 18 on All Three Nodes

You must install PostgreSQL 18 on Node A, Node B, and Node C. This guide assumes you are using Ubuntu:

Bash
sudo apt updatesudo apt install postgresql-common -ysudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.shsudo apt install postgresql-18 postgresql-client-18 -y

Check the version after installation:

Bash
psql --version

You should see PostgreSQL 18.x. Make sure all three nodes report the same version before continuing.

Step 2: Configure the Primary Server (Node A)

You must open the main config file on Node A with your desired text editor:

Bash
sudo nano /etc/postgresql/18/main/postgresql.conf

Then, set the following values. These are required for both physical streaming replication and for synchronized failover slots to work:

Bash
listen_addresses = '*'wal_level = logicalmax_wal_senders = 10max_replication_slots = 10synchronized_standby_slots = 'standby_1'

The wal_level = logical setting is required for logical decoding. Without it, publications cannot exist. The synchronized_standby_slots setting tells the primary to wait until the named physical slot confirms it received the WAL before sending changes to logical subscribers. This is the setting that guarantees the standby never falls behind the subscriber.

Once you are done, open the access file to allow replication connections:

Bash
sudo nano /etc/postgresql/18/main/pg_hba.conf

Add these lines and replace IPs with your real node addresses:

Bash
host replication repl_user 10.0.0.2/32 scram-sha-256host all repl_user 10.0.0.2/32 scram-sha-256host all subscriber_user 10.0.0.3/32 scram-sha-256

Save and close the file. Restart PostgreSQL so the settings apply:

Bash
sudo systemctl restart postgresql

Next, you must create a replication role and a role for the subscriber connection. Connect to the primary server as the postgres user:

Bash
sudo -u postgres psql

Run these CREATE ROLE commands inside the psql prompt:

SQL
CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'ReplPass123!';CREATE ROLE subscriber_user WITH LOGIN PASSWORD 'SubPass123!';GRANT pg_read_all_data TO subscriber_user;

Step 3: Build the Physical Standby (Node B)

The standby must exist before we can sync failover slots to it. First, create a physical replication slot on the primary node for the standby to use:

SQL
SELECT pg_create_physical_replication_slot('standby_1');

This name, standby_1, matches the synchronized_standby_slots value we set earlier in Step 2.

Now on Node B, you must stop PostgreSQL and clear the data directory, then take a base backup from the primary:

Bash
sudo systemctl stop postgresqlsudo rm -rf /var/lib/postgresql/18/main/*sudo -u postgres pg_basebackup -h 10.0.0.1 -D /var/lib/postgresql/18/main -U repl_user -P -R -X stream -S standby_1

The -S standby_1 flag tells pg_basebackup to use the existing physical slot, and -R writes the standby connection settings automatically into postgresql.auto.conf.

Then, edit the standby's config to enable logical failover slot sync. This is the key part for PostgreSQL 18 logical replication failover:

Bash
sudo nano /etc/postgresql/18/main/postgresql.conf
Bash
hot_standby = onhot_standby_feedback = onsync_replication_slots = on

hot_standby_feedback must be on; otherwise the primary may remove rows the standby still needs. sync_replication_slots = on starts a background worker on the standby that pulls the logical failover slots from the primary automatically.

Check that primary_conninfo and primary_slot_name were set correctly by pg_basebackup:

Bash
sudo -u postgres cat /var/lib/postgresql/18/main/postgresql.auto.conf

You should see primary_slot_name = 'standby_1' in the output. If it is missing, you must add it manually:

Bash
primary_slot_name = 'standby_1'

Start the standby:

Bash
sudo systemctl start postgresql

Confirm the standby is streaming:

SQL
SELECT status, sync_state FROM pg_stat_replication;

Step 4: Create the Publication on the Primary Node

Back on Node A, use the command below to create a test table and a publication:

SQL
CREATE TABLE orders (  id serial PRIMARY KEY,  customer text,  amount numeric,  created_at timestamptz DEFAULT now()); CREATE PUBLICATION orders_pub FOR TABLE orders;

Step 5: Create the Subscription with Failover Enabled (Node C)

This is the step that turns a normal logical replication setup into a true failover-ready setup. On Node C, create the matching table first:

SQL
CREATE TABLE orders (  id serial PRIMARY KEY,  customer text,  amount numeric,  created_at timestamptz DEFAULT now());

Now create the subscription and set failover = true:

SQL
CREATE SUBSCRIPTION orders_sub  CONNECTION 'host=10.0.0.1 port=5432 dbname=postgres user=subscriber_user password=SubPass123!'  PUBLICATION orders_pub  WITH (failover = true);

The failover = true option marks the logical slot on the primary as eligible for syncing to the physical standby. Without this flag, the slot remains local to the primary and will not survive a promotion.

Check that the subscription is active and the initial table copy has finished:

SQL
SELECT subname, subenabled, subfailover FROM pg_subscription;SELECT srsubstate FROM pg_subscription_rel;

subfailover should show t, and srsubstate should show r, meaning the table is fully synced and streaming.

Step 6: Verify the Failover Slots Are Synced to the Standby

This validation step is the most important part of PostgreSQL 18 logical replication failover. A slot that looks synced but is not truly ready will break your failover.

First, on the subscriber node (Node C), find which slots must exist on the standby:

SQL
SELECT array_agg(quote_literal(s.subslotname)) AS slotsFROM pg_subscription sWHERE s.subfailover AND s.subslotname IS NOT NULL;

Next, on the primary (Node A), you can get the full list of every failover-ready slot at once:

SQL
SELECT array_agg(quote_literal(r.slot_name)) AS slotsFROM pg_replication_slots rWHERE r.failover AND NOT r.temporary;

Finally, on the standby (Node B), confirm those slots exist and are ready:

SQL
SELECT slot_name, (synced AND NOT temporary AND invalidation_reason IS NULL) AS failover_readyFROM pg_replication_slotsWHERE slot_name IN ('orders_sub');

If failover_ready shows t, the slot has been fully synced to the standby and is safe to use after promotion. If it shows f or the row is missing, wait a few seconds and run the query again; the slotsync worker syncs slots periodically, not instantly.

Also, you can force an immediate manual sync instead of waiting for the background worker by running this on the standby:

SQL
SELECT pg_sync_replication_slots();

Step 7: Simulate the Failover

Now we want to test the real scenario. If the primary goes down, the subscriber must keep working from the new primary. This is the actual proof that PostgreSQL 18 logical replication failover works.

First, you must insert a row on the primary before the failover to confirm normal replication:

SQL
INSERT INTO orders (customer, amount) VALUES ('Test Customer 1', 100);

Check it arrived on the subscriber with:

SQL
SELECT * FROM orders;

Now stop the primary to simulate a crash:

Bash
sudo systemctl stop postgresql

Promote the standby (Node B) to become the new primary:

Bash
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/18/main

Or, you can do it from inside psql on the standby:

SQL
SELECT pg_promote();

Wait a few seconds, then confirm Node B is no longer in recovery:

SQL
SELECT pg_is_in_recovery();

It should return f, meaning Node B is now a normal read-write primary.

Step 8: Point the Subscriber to the New Primary

The subscriber's connection string still points at the old primary's IP address. You must update it to point at Node B, which is now the primary:

SQL
ALTER SUBSCRIPTION orders_sub  CONNECTION 'host=10.0.0.2 port=5432 dbname=postgres user=subscriber_user password=SubPass123!';

There is no need to drop and recreate the subscription, and no need to create a new slot. Because the slot was already synced by sync_replication_slots in Step 3, it exists on Node B with the correct position already recorded.

Insert a new row on the new primary (Node B) to confirm replication continues:

SQL
INSERT INTO orders (customer, amount) VALUES ('Test Customer 2', 200);

Check the subscriber again:

SQL
SELECT * FROM orders;

If you see both rows, "Test Customer 1" and "Test Customer 2", the PostgreSQL 18 logical replication failover worked correctly. The subscription resumed from exactly where it left off, using the slot that was synchronized before the crash.

Step 9: Clean Up and Re-Point the Standby Chain

After a real failover, Node A is gone or needs to be rebuilt as a new standby of Node B. You must create a fresh physical replication slot for it, and set synchronized_standby_slots on Node B to use that slot's name. 

This keeps your failover protection working for the next incident, instead of leaving Node B as a single point of failure.

Common Mistakes That Break Failover Slots

A few small mistakes cause most failures in these failover setups:

  • Forgetting failover = true on the subscription, which leaves the slot local to the primary only.
  • Forgetting hot_standby_feedback = on on the standby, which can cause the primary to remove data the standby still needs.
  • Not setting primary_slot_name on the standby, which stops slot sync from working at all.
  • Checking slot readiness once and assuming it stays ready, instead of re-checking failover_ready right before the actual failover.
  • Mixing PostgreSQL versions between primary and standby, which is not supported.

Conclusion

PostgreSQL 18 logical replication failover solves the biggest problem in logical replication, which is losing your subscription when the primary fails. Turn on failover = true on the subscription, sync_replication_slots on the standby, and synchronized_standby_slots on the primary. Your logical slots now move to the standby automatically. When you promote the standby, the subscriber just needs a new connection string; no rebuild needed. Test this on real separate servers first, and always check failover_ready before you pull the plug on the primary.

We hope you enjoy this guide. For more detailed information, you can check the PostgreSQL Logical Replication Failover Docs.

Prev PostHow to Create Ubuntu 26.04 Cloud-Init Templates in Proxmox VE 9.2
Category:Server Setup & Administration
Tag:

We use cookies to run PerLod and to understand how the site is used. You can accept everything, or open Manage to turn individual categories off. Cookie policy.