Replication fails during Patroni switchover (ShareUpdateExclusiveLock & Per-DB worker self-check failure)

Environment

• PostgreSQL: 17.6
• pgactive: 2.1.6
• Patroni: 4.1.0
• Architecture: 2-Node Active-Active setup managed by Patroni.

Description

We are experiencing critical failures in pgactive replication slots during a standard Patroni switchover (or failover). When a standby node is promoted to become the new primary, pgactive workers fail to resume replication automatically, leaving slots in active: false state.

We have identified two distinct but related crash scenarios during this process:

Scenario 1: Race Condition on Output Plugin Startup

The pgactive apply worker attempts to start replication from the remote node (the new primary) while the new primary is still in the final stages of recovery/promotion. This triggers a fatal lock error on the server side because the Output Plugin tries to acquire a lock before the database is writable.

Log (on the new primary/walsender side):

FATAL:  could not send replication command "START_REPLICATION SLOT ..."
ERROR:  cannot acquire lock mode ShareUpdateExclusiveLock on database objects while recovery is in progress
HINT:  Only RowExclusiveLock or less can be acquired on database objects during recovery.
CONTEXT:  slot "...", output plugin "pgactive", in the startup callback

Scenario 2: Per-DB Worker "Self-Check" Failure
​Even if the locking issue is avoided, the Per-DB Supervisor worker on the new primary often crashes immediately after promotion. It appears that the worker tries to verify its own connectivity (check_local_node_connectability) and fails.
​Instead of retrying for the configured duration (e.g., pgactive.connectability_check_duration), it seems to fail on the logical check (verifying pg_stat_activity) or a transient connection reset, leading to an immediate unregister/exit.
​Log (on the new primary):

LOG:  unregistering per-db worker on node node1 (...) due to failure when connecting to ourself
...
LOG:  background worker "pgactive per-db worker" (PID 463055) exited with exit code 1

Once the Per-DB worker unregisters, it does not restart automatically, and the Apply workers are never launched. This requires a manual restart of the PostgreSQL service to fix.
​Steps to Reproduce
​Setup a 2-node Patroni cluster with pgactive enabled.
​Perform patronictl switchover.
​Observe the logs on the new primary node.
​Check pg_replication_slots (slots remain inactive).
​Expected Behavior
​Apply Worker / Output Plugin: Should gracefully wait/retry if the remote server is in recovery or not yet ready to grant ShareUpdateExclusiveLock, rather than crashing with a FATAL error.
​Per-DB Worker: The check_local_node_connectability logic should be more robust. It should retry (with backoff) if the connection to itself fails or if the pg_stat_activity check returns an unexpected result during the volatile switchover period, instead of immediately unregistering.