0

I am attempting to move a large (4TB) Postgresql database from AWS RDS to Azure Database for Postgresql Flexible Server using logical replication (publication/subscription). I am performing the replication progressively by table and am syncing the larger tables one by one to reduce the WAL overhead on the primary. I am encountering hangs and long waits when syncing the bigger (>10GB) tables.

Starting with the subscription in sync and up to date, when I add a new table to the publication and update the subscription I see network traffic and disk usage spike immediately (write thoughput on the subscriber of 100MB/s). After around half an hour, the traffic almost stops (1MB/s or less). After some variable period from 1 to 12 hours, traffic suddenly kicks up again. This process repeats until the sync eventually completes and the table is up to date.

I've inspected the database logs on both sides and can't see any error messages or even info related to the replication process. What might be causing these long hangs? Are there further diagnostics I can do to see the cause?

New contributor
Jarrett Prosser is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • Typically, the cause are long-running transactions on the publisher. Fix those or, if you have a recent PostgreSQL version on the subscriber, try the streaming option on the subscription. Commented Jul 8 at 7:29

1 Answer 1

0

After looking into the source and destination database waits, this turned out the be the result of a proxy device used to expose the source database. The proxy VM was a t3.nano burstable instance. By increasing the size of the proxy VM this removed the bottleneck and allowed faster transfer of data without the observed slowdown.

New contributor
Jarrett Prosser is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.