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.