fivetran replication from PostgreSQL (connector Broken with WAL log error)

Question

PostgreSQL RDS instance having physical replication to multi-AZ, same instance also enabled for Fivetran replication copying data to BigQuery using WAL logical test-decoding.

Multiple issues here:

1. replication Lag, though not actually happening

2. Querying

   a) select * from pg_stat_replication; shows only physical RDS replication details.

   b) select * from pg_replication_slots; gives information about replication slot created with value "Active" is "False" and "active_pid" is "NULL", which indicates replication is not active.

   However, verifying from Fivetran shows the sync is successful and the restart_lsn and flush_lsn values getting updated.

3. encountered another issue with one of the databases, Fivetran connector disconnected and broken with this error:

   "status" : "FAILURE_WITH_TASK", "reason" : "java.lang.RuntimeException: fivetran_replication has last tracked LSN of PostgresLsn{6065/C8000028} which is ahead of PostgresLsn{6065/B009A038}", "taskType" : "reconnect"

   Tried reconnecting, but the same error pops up. Is there an way to skip the WAL logs?

Answer 1

2. b) confirmed from fivetran support, this is an expected behavior and this value is true only when active replication is happening.

3. waiting for the required WAL log which was already flushed at postgres side. postgresLSN (fivetran side) can be manually updated by fivetran support with the latest available restart_lsn value(data might be missing) or can resync the complete data to resolve the issue.