From: | Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com> |
---|---|
To: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Logical Replication Speed |
Date: | 2021-09-29 14:26:41 |
Message-ID: | CAFpL5VxM9rBPAwnCUk_TmrkLYsfGAYOSVDrUcY-JPCdfzpN52Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi Team,
We have a 10 TB database most of which is occupied by a single table. This
table is around 9.5TB. Bloat is 3.5TB for this table. So, the total table
size without bloat is 6TB.
We are using native logical replication to remove the bloat for this table
to another instance but it takes more than a day to copy 1.5TB of data.
With this speed it will take 4-5 days for the initial sync itself. During
this initial sync, the replication slot holds WAL and creates disk usage
problems on the primary, so we had to stop the replication. We cannot
remove the PK since it is the Replica identity.Other indexes and FK have
been removed from the destination(secondary).
Other changes we have made in the destination(secondary) instance but still
no luck
- fsync=off,synchronous_commit=off, archive_mode=off.
Since it is a single table, we cannot increase worker_processes. Source
database is highly transactional and we do not need any downtime.
Is there any other option to increase the speed of initial data copy in
logical replication?
Postgres Version - 11.7
OS - OEL7
Thanks and Regards,
Nikhil
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Kerber | 2021-09-29 14:32:31 | Re: Logical Replication Speed |
Previous Message | Axel Rau | 2021-09-29 13:37:22 | Re: [FIXED] Can't drop logical replication slot |