Long Transaction Check in the Source Database

Description

If the source database has transactions that have been there for a long time without being submitted, the replication slot fails to be created. As a result, the incremental synchronization task fails.

Handling Suggestion

1. Check whether the source DB instance has long transactions. The following uses 5 minutes as an example.

select datname, pid, xact_start, state, query from pg_stat_activity where xact_start < current_timestamp - interval '300 seconds';

2. If there are long transactions, wait until the long transactions stop or kill the long transactions and try again.

SELECT pg_terminate backend(281223408887296);

Kill the backend process. 281223408887296 is the PID queried in the pg_stat_activity view.

3. If no long transaction is found, check whether there are prepared transactions. If yes, submit the transactions.

SELECT * FROM pg_prepared_xacts;