The speed is going to be governed by the available hardware, so improve the hardware to improve the speed of the initial data copy. You should also be able to add space to the file system for your wal logs without too much difficulty in RHEL7.
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
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'