Error: Error With Logical Replication

Issue

Setup tests fail or syncs stop. One of the following errors appears:

• Error with logical replication. This error occurs when the replication slot conflicted with recovery (and so is now invalidated)
• Error with logical replication. This error occurs when the replication slot conflicted with recovery (and so is now invalidated). The WAL file required for replication appears to have been removed.
• Error with logical replication. This error occurs when the replication slot conflicted with recovery (and so is now invalidated). The rows required for replication appear to have been removed.
• Error with logical replication. This error occurs when the replication slot conflicted with recovery (and so is now invalidated). The primary database server doesn't have a WAL level sufficient to perform replication.

Environment

• Connector: PostgreSQL
• Update method: Logical replication

Resolution

Resolve the logical replication error

1. Drop the invalid replication slot from your PostgreSQL database:

   SELECT pg_drop_replication_slot('your_slot_name');
   

2. Create a new replication slot using the pgoutput plugin. You must use pgoutput. Using test_decoding or any other plugin cuases a separate error.

   SELECT pg_create_logical_replication_slot('your_slot_name', 'pgoutput');
   

   For more information, see step 10 in the Logical replication section of our Configure incremental sync method documentation.

3. Verify that the new slot was created with the correct plugin:

   SELECT slot_name, plugin FROM pg_replication_slots WHERE slot_name = 'your_slot_name';
   

   The plugin column must return pgoutput. If it shows anything else, drop the slot and repeat step 2.

4. Update the connection in Fivetran with the new slot details.

   i. In Fivetran, go to your PostgreSQL connection page.

   ii. Select the Setup tab.

   iii. Click Edit connection.

   iv. Update the Replication Slot and Publication Names fields with the new information.

   v. Click Save & Test.

5. Trigger a historical sync to re-sync your data. A historical sync is required because the new replication slot starts tracking changes from the moment it is created. It does not continue from where the old slot left off.

Prevent the issue from recurring

If Fivetran replicates from a PostgreSQL standby or replica instance, set the hot_standby_feedback parameter to on on the replica. This helps prevent the replication slot from being invalidated by future recovery events

1. Set hot_standby_feedback to on:

   • Standard PostgreSQL: Add or update hot_standby_feedback = on in postgresql.conf, or run ALTER SYSTEM SET hot_standby_feedback = on;.
   • Amazon Aurora PostgreSQL: Set the parameter in your DB cluster parameter group in the AWS RDS console. The AWS CLI equivalent is modify-db-cluster-parameter-group. For more information, see our Aurora PostgreSQL setup guide and the AWS RDS CLI documentation.

2. Restart your PostgreSQL instance or reload the configuration for the change to take effect. For more information on hot_standby_feedback, see the PostgreSQL Hot Standby documentation.

Cause

This issue occurs when the logical replication slot configured for your PostgreSQL connection is invalidated during a recovery event on the database. The slot can be invalidated for the following reasons:

• Recovery conflict: PostgreSQL performed a recovery process that conflicted with the slot.
• WAL file removed: The WAL segment required to resume replication from the slot's position was removed before we could consume it.
• Rows removed: Rows required for replication were vacuumed away before we could consume them.
• WAL level insufficient: The primary database server's wal_level setting is not set to logical, which is required for logical replication.

When we replicate from a standby or replica instance, PostgreSQL may vacuum data that the replication slot still needs if hot_standby_feedback is set to off, resulting in the above error. Enabling hot_standby_feedback on the replica prevents this error, but it does not prevent slot invalidation caused by other recovery events.