--- title: "Patroni and logical replication fo PostgreSQL: Preventing data loss during failovers" id: "4871" type: "post" slug: "patroni-postgresql-logical-replication-failover" published_at: "2026-07-17T10:40:51+00:00" modified_at: "2026-07-17T10:49:10+00:00" url: "https://palark.com/blog/patroni-postgresql-logical-replication-failover/" markdown_url: "https://palark.com/blog/patroni-postgresql-logical-replication-failover.md" excerpt: "If you’re using logical replication in Patroni on top of a physical one, there’s a risk of data loss during a failover. Learn how Patroni handles logical replication slots, what can go wrong, and which solutions PostgreSQL 17 and Patroni..." taxonomy_post_tag: - "databases" - "Patroni" - "PostgreSQL" taxonomy_language: - "English" taxonomy_mailchimp: - "created_newsletter" taxonomy_author: - "andrey.babushkin" --- # Patroni and logical replication fo PostgreSQL: Preventing data loss during failovers 17 July 2026 By Andrey Babushkin, software engineer Everyone knows that asynchronous replication failovers — whether performed by Patroni or another tool — can result in lost data. This occurs in situations where certain transactions committed on the leader haven’t managed to reach the replica before a failover. However, even if the replica is fully in sync with the leader, data loss during a failover may still occur if you’re using logical replication on top of a physical one. You may encounter this problem in a typical PostgreSQL replication setup, such as the one below: PostgreSQL cluster with physical and logical replicationThis diagram illustrates a two-node PostgreSQL cluster (`psql-db-1` and `psql-db-2`) with physical replication configured between them. This cluster is managed by Patroni. On top of those two nodes is a standalone node (`psql-db-3`), which receives updates for one single table via logical replication. To make sure the `psql-db-3` node connects to the Patroni leader, we route it through the HAProxy load balancer. HAProxy determines the current leader by sending an HTTP request to the Patroni API. What could go wrong? ## How Patroni handles logical replication slots Starting with PostgreSQL 11, Patroni can handle logical replication slots via a feature called *permanent replication slots*, as described in [Dynamic Configuration Settings](https://patroni.readthedocs.io/en/latest/dynamic_configuration.html) and in the [original PR](https://github.com/patroni/patroni/pull/1820) . To tell Patroni to automatically create a logical replication slot with the correct LSN marks on the leader node, just add the following settings to the Patroni configuration: ``` slots: permanent_logical_slot_name: type: logical database: db_name plugin: pgoutput ``` Once this block is added, Patroni will automatically create a logical replication slot named `permanent_logical_slot_name` on the leader node via `pg_create_logical_replication_slot`. Beyond that, it also creates the same replication slot on any replica tagged with `nofailover: false` in the Patroni config. To achieve this, Patroni copies the slot state from the leader and reads the state file located in the `pg_replslot` directory via `pg_read_binary_file`. Then, it writes identical files onto the replicas and restarts PostgreSQL on them. Once the slots are set up, Patroni advances the `confirmed_flush_lsn` on the replicas every `loop_wait` seconds (which defaults to 10 seconds and defines the interval for health checks and Distributed Configuration Store syncing). It adjusts the value based on DCS data using the `pg_replication_slot_advance` function. ## Potential issues with this approach At first glance, you might think that during a Patroni failover, the worst-case scenario for a logical replication slot would simply be resending some already-transmitted data to the logical replica (due to the `confirmed_flush_lsn` position lagging in the DCS). But you’d be mistaken. The thing is, in production environments, when choosing between availability, data integrity, and performance, **availability is often what ends up being sacrificed**. That is, in order to boost performance, replication is set to asynchronous, meaning an auto-failover could actually cause data loss. So, automatic failover is disabled, and in the event of a database cluster leader failure, a user decides if a leader switchover shall be required. But since automatic failover is turned off (replicas have the `nofailover: true` tag), Patroni won’t automatically pre-create the logical slots defined in the slots config on those replicas. So, what will happen in the event of such a managed failover if the previous leader is unavailable, and the logical replica has been lagging at the moment of the failover? Let’s find out. ## Experimenting with a lagged logical replica Here is the initial state of our Patroni-managed cluster, with `psql-db-1` being the leader: ``` patronictl list + Cluster: main (...) --------+---------+-----------+----+-----------+------------------+ | Member | Host | Role | State | TL | Lag in MB | Tags | +-----------+-----------------+---------+-----------+----+-----------+------------------+ | psql-db-1 | 192.168.100.101 | Leader | running | 38 | | clonefrom: true | +-----------+-----------------+---------+-----------+----+-----------+------------------+ | psql-db-2 | 192.168.100.102 | Replica | streaming | 38 | 0 | clonefrom: true | | | | | | | | nofailover: true | +-----------+-----------------+---------+-----------+----+-----------+------------------+ ``` Now, spin up a test database called `paydata` there and create a `payment` table in it, then insert a test record into it, and publish this table for logical replication: ``` CREATE DATABASE paydata; \c paydata CREATE TABLE payment ( id INTEGER PRIMARY KEY, customer INTEGER NOT NULL, merchant INTEGER NOT NULL, sum INTEGER NOT NULL ); INSERT INTO payment (id, customer, merchant, sum) VALUES (1, 0, 1, 1000); SELECT * FROM payment; id | customer | merchant | sum ----+----------+----------+------ 1 | 0 | 1 | 1000 CREATE PUBLICATION payment_pub FOR TABLE payment; ``` Now, on the `psql-db-3` server, create a database named `test` and a `payment` table with an analogous structure: ``` CREATE DATABASE test; \c test CREATE TABLE payment ( id INTEGER PRIMARY KEY, customer INTEGER NOT NULL, merchant INTEGER NOT NULL, sum INTEGER NOT NULL ); ``` Next, create a subscription connecting to the leader via HAProxy: ``` test=# CREATE SUBSCRIPTION payment_sub CONNECTION 'dbname=paydata host=192.168.100.10 user=REPLICA_USER password=REPLICA_PASS' PUBLICATION payment_pub; NOTICE: created replication slot "payment_sub" on publisher ``` Add the created replication slot to the Patroni DCS: ``` slots: payment_sub: database: paydata plugin: pgoutput type: logical ``` Check the contents of the `payment` table: ``` test=# SELECT * FROM payment; id | customer | merchant | sum ----+----------+----------+------ 1 | 0 | 1 | 1000 (1 row) ``` As expected, the data also successfully arrived on the `psql-db-2` replica via the physical replication slot. Now, let’s simulate the logical replica lagging behind. You can do so by temporarily stopping PostgreSQL on `psql-db-3`: ``` pg_ctlcluster 16 main stop ``` After that, add a new record to the test table on the `psql-db-1` leader: ``` INSERT INTO payment (id, customer, merchant, sum) VALUES (2, 0, 1, 2000); ``` Now, let’s mess some things up. Cut off network connections to both PostgreSQL and etcd on the `psql-db-1` leader: ``` # Block incoming connections to PostgreSQL iptables -A INPUT -p tcp --dport 5432 -j DROP # Block incoming connections to Patroni from HAProxy iptables -A INPUT -p tcp --dport 8008 -j DROP # Block outbound traffic from Patroni to etcd iptables -A OUTPUT -p tcp --dport 2379 -j REJECT ``` Well done, the cluster has lost its leader: ``` psql-db-2 ~ # patronictl list + Cluster: main (...) --------+---------+-----------+----+-----------+------------------+ | Member | Host | Role | State | TL | Lag in MB | Tags | +-----------+-----------------+---------+-----------+----+-----------+------------------+ | psql-db-2 | 192.168.199.102 | Replica | streaming | 40 | 0 | clonefrom: true | | | | | | | | nofailover: true | +-----------+-----------------+---------+-----------+----+-----------+------------------+ ``` Suppose an on-call engineer steps in at this point, assesses the damage, and realizes there’s no way to promptly bring the leader back online. To initiate the failover, the engineer removes the `nofailover` tag from the replica. Patroni successfully performs the failover: ``` psql-db-2 ~ # patronictl list + Cluster: main (...) --------+--------+---------+----+-----------+-----------------+ | Member | Host | Role | State | TL | Lag in MB | Tags | +-----------+-----------------+--------+---------+----+-----------+-----------------+ | psql-db-2 | 192.168.199.102 | Leader | running | 41 | | clonefrom: true | +-----------+-----------------+--------+---------+----+-----------+-----------------+ ``` … and automatically generates a replication slot on `psql-db-2`: ``` SELECT * FROM pg_replication_slots; slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase | conflicting -------------+----------+-----------+--------+------------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+-----------+------------- psql_db_1 | | physical | | | f | f | | | | D/F0030D45 | | reserved | | f | payment_sub | pgoutput | logical | 178410 | paydata | f | f | | | 157213 | D/F0031E56 | D/F0033421 | reserved | | f | f (2 rows) ``` Since the `psql-db-2` replica was perfectly in sync with the leader right before the crash, it got all the data. No data here was lost: ``` paydata=# SELECT * FROM payment; id | customer | merchant | sum ----+----------+----------+------ 1 | 0 | 1 | 1000 2 | 0 | 1 | 2000 (2 rows) ``` Now, let’s boot the logical replica on `psql-db-3` back up, so it catches up: ``` pg_ctlcluster 16 main start ``` Here we go: the **data is missing** due to the `psql-db-3` replica lagging at the time of the crash andnever made it to this replica: ``` test=# SELECT * FROM payment; id | customer | merchant | sum ----+----------+----------+------ 1 | 0 | 1 | 1000 (1 row) ``` Unfortunately, it *never* will! Let’s add another record to the test table on our new leader, `psql-db-2`: ``` INSERT INTO payment (id, customer, merchant, sum) VALUES (3, 0, 1, 3000); ``` Here’s what the table looks like on the leader: ``` paydata=# SELECT * FROM payment; id | customer | merchant | sum ----+----------+----------+------ 1 | 0 | 1 | 1000 2 | 0 | 1 | 2000 3 | 0 | 1 | 3000 (3 rows) ``` And here’s that same table on the logical replica: ``` test=# SELECT * FROM payment; id | customer | merchant | sum ----+----------+----------+------ 1 | 0 | 1 | 1000 3 | 0 | 1 | 3000 (2 rows) ``` This clearly demonstrates how a **manual leader switchover in a Patroni cluster can result in data loss on a lagging logical replica**, even though no data is lost in the main cluster. If the old leader had still been reachable, the slot would have copied over perfectly. But in our case, Patroni just spun up a new slot on the new leader using the current LSN. ## The solution One potential way to fix this is by switching to automatic failover managed by Patroni. But if you’re using fully asynchronous replication, relying on automated failovers brings about the risk of *uncontrolled* data loss across the cluster. Fortunately, starting with version 17, PostgreSQL offers a native approach to managing logical replication slots on replicas: [replication slot synchronization](https://www.postgresql.org/docs/17/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS-SYNCHRONIZATION) . To enable this feature, you’ll have to meet the following requirements: 1. You’ll need to have the following parameters enabled on the physical PostgreSQL replica: ``` sync_replication_slots = on  hot_standby_feedback = on ``` Once you turn on slot synchronization, PostgreSQL will spin up a dedicated `slotsync worker` process. This process takes care of synchronizing any slots with the `failover` label. 2. The logical replication slot must be created on the leader with the `failover` parameter. Here’s an example of how to create a subscription on the logical replica, which, in turn, tells the leader to create a slot with the correct parameter: ``` test=# CREATE SUBSCRIPTION payment_sub CONNECTION 'dbname=paydata host=192.168.100.10 user=REPLICA_USER password=REPLICA_PASS' PUBLICATION payment_pub WITH (failover = true); ``` 3. To prevent a scenario where the logical replica gets *ahead* of the physical replica, you must add the list of synchronized slots to the PostgreSQL configuration: ``` synchronized_standby_slots = 'payment_pub, one_more_pub, ...' ``` When this parameter is set, the `wal sender` will wait until the physical replicas confirm they’ve received the corresponding WAL *before* sending data to the logical replica. If this order is violated, it might no longer be possible to resume logical replication from a new leader. On top of tweaking the PostgreSQL configuration, you have to **prepare Patroni for failover slots**. Since the built-in replication slot synchronization mechanism at the DBMS level is being used, you no longer need Patroni to manage logical replication slots for you. Add all logical replication slots to `ignore_slots` in the Patroni config as follows: ``` ignore_slots: - name: payment_sub type: logical database: paydata plugin: pgoutput ``` Otherwise, Patroni may delete slots that aren’t defined in its configuration. An even better option is to upgrade Patroni to version 4.1.0 or higher. The thing is, the Patroni developers [merged a PR](https://github.com/patroni/patroni/pull/3245) into the 4.1.0 release (dated September 23, 2025) that prevents Patroni from interacting with any slots with the `failover` option set. As a result, by pairing PostgreSQL 17+ with Patroni 4.1+, database administrators can now set up **fault-tolerant logical replication using only native PostgreSQL features**, without having to tweak the Patroni configuration. ## Conclusion If you’re using logical replication alongside Patroni, control how your logical replication slots behave during a manual failover. An even better option yet is to migrate to PostgreSQL 17+ and Patroni 4.1+ to fix this issue once and for all using the new slot synchronization feature. ## Related articles 28 January 2025 ### [Patroni-managed PostgreSQL cluster switchover: A tricky case that ended well](https://palark.com/blog/patroni-postgresql-cluster-switchover/) 13 January 2022 ### [Migrating a PostgreSQL database cluster managed by Patroni](https://palark.com/blog/migrating-a-postgresql-cluster-managed-by-patroni/) 24 October 2025 ### [PG Back Web overview: GUI to manage PostgreSQL backups](https://palark.com/blog/ufo-backup-pg-back-web-overview/)