Why Is My PostgreSQL Connector Using Teleport or XMIN Update Method Not Syncing?

Question

Why is my connector using the Teleport or XMIN update method not syncing data?

Environment

Connector: PostgreSQL

Answer

If you are using Teleport or XMIN as your incremental sync method and the source database has a transaction that is taking a long time to commit, the connector stalls and waits for the transaction to complete before syncing the updates. This may appear as multiple syncs that extract no data, followed by a sync that extracts a lot of data.

You can use the following queries to troubleshoot the source database and look for ongoing transactions:

SELECT pg_xact_changes(<STALLED_TXID>);
SELECT * FROM pg_replication_slots;
SELECT * FROM pg_stat_activity;
SELECT * FROM pg_prepared_xacts;
SELECT * FROM pg_prepared_statements;

IMPORTANT: To see all transactions, execute these queries on your primary source, and not on your replica.

This can help identify the oldest running transactions in your environment. Once these transactions complete or are terminated, the XMIN horizon can advance, which allows incremental syncs to continue.