No load bar appears for multiple syncs in a row, but you know there are changes in the Postgres source:
PostgreSQL Connector using logical (Write Ahead Log - a.k.a WAL) replication
This is often due to improper WAL settings.
- Ensure your WAL settings are tuned correctly. Your DBA can follow this external blog post as a general guide to this performance tuning.
- Our general recommendation is to set the following:
- checkpoint_timeout = 30 minutes
- max_wal_size = 30 GB
- max_wal_senders = 140
- max_logical_replication_workers = 55
- max_worker_process = 15
- You may need to drop and recreate the replication slots after changing these values in your database
1. Pause the connector.
2. Delete the replication slot in the source.
3. Allow the database to checkpoint.
4. Create a new replication slot.
5. Click the Setup tab.
6. Click the Edit connection details link.
7. Click Re-sync All Historical Data on the setup page.
8. Unpause the connector.
If the WAL checkpoint values are not set correctly for your databases' needs, Postgres will checkpoint constantly. This causes a performance bottleneck when external processes like Fivetran try to read from the WAL.
Often you can see this in action when your DBA tries to run this query:
select * from pg_logical_slot_peek_changes('fivetran_replication_slot_1', NULL, 20);
The above query will often hang forever instead of retuning 20 entries from the replication slot. Just replace 'fivetran_replication_slot_1' with your replication slot name as seen in the connector settings.