A bug in PostgreSQL's logical decoding facility can cause data in logical replication (pglogical, BDR, in-core logical replication) to be sent multiple times. Symptoms include oversized pg_replslot directory contents, ERRORs on the subscriber/downstream, spurious CONFLICT reports, and replication that appears busy but fails to progress.
If a logical decoding provider worker (walsender) exits due to a timeout, it fails to clean up buffered transactions in pg_replslot from the logical decoding session. When restarted, the worker appends to the leftover buffers, duplicating changes in the transaction.
This often snowballs, as the larger transaction and slower apply due to conflict handling may cause further timeouts, resulting in further appends to the reorder buffer, slower apply, and more timeouts.
This problem may also give rise to provider/subscriber row count mismatches and tables that are out of sync.
A workaround has been shipped in:
- pglogical 2.2.0
- BDR 1.0.5
- BDR 2.0.5
to prevent this from affecting users.
Fixed in (unreleased at time of writing):
- 10.4
- 9.6.9
- 9.5.13
- 9.4.18
If you suspect you are affected by this problem, contact support.
You can clear the excess reorder buffers with a PostgreSQL restart. It's not necessary to clean anything from pg_replslot manually, nor should you do so. Instead adjust the walsender timeout; see the linked related article on walsender timeouts.
See commit, mailing list and commitfest.