Issue

Sync takes longer than normal or eventually fails.

Warning in logs: "Extract schema.table failed. We will now attempt automatic mitigation."

Environment

PostgreSQL connector with XMIN replication to a PostgreSQL read replica

Resolution

Increase the max_standby_archive_delay and max_standby_streaming_delay parameters to keep the source db from killing long-running queries.
 
Here are links that explain these settings in more detail:
max_standby_archive_delay - The maximum total time allowed to apply any one WAL segment's data.
max_standby_streaming_delay - The maximum total time allowed to apply WAL data once it has been received from the primary server.
 
Fivetran can support up to 6 hours for these settings (Fivetran support must enable the 6 hour replica delay), but a good starting place is to set them a few minutes above the average query return time of the Fivetran connector database user. This is best measured by the database administrator.

Setting to the average sync frequency as seen in the connector dashboard is also a good starting point. 

Cause

This is caused when the extraction queries we make to the replica exceed the configured max_standby_archive_delay and max_standby_streaming_delay parameters on the source database.

These settings essentially set the amount of time the replica can be out of sync with the master database so balancing acceptable replica lag with enough headroom for larger extract queries is the goal.