Re: Logical Replication speed-up initial data

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Vijaykumar,

Thanks for the details.
In this method you are saying the pg_basebackup will make the initial load faster ? 
We intend to bring only a few tables. Using pg_basebackup will clone an entire instance. 

Thanks,
Nikhil



On Thu, Aug 5, 2021 at 7:57 PM Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> wrote:
On Thu, 5 Aug 2021 at 10:27, Nikhil Shetty <nikhil.dba04@xxxxxxxxx> wrote:
Hi,

Thank you for the suggestion.

We tried by dropping indexes and it worked faster compared to what we saw earlier. We wanted to know if anybody has done any other changes that helps speed-up initial data load without dropping indexes.


PS: i have not tested this in production level loads, it was just some exp i did on my laptop.

one option would be to use pglogical extension (this was shared by Dharmendra in one the previous mails, sharing the same),
and then use pglogical_create_subscriber cli to create the initial copy via pgbasebackup and then carry on from there.
I ran the test case similar to one below in my local env, and it seems to work fine. of course i do not have TB worth of load to test, but it looks promising,
especially since they introduced it to the core.
Once you attain some reasonable sync state, you can drop the pglogical extension, and check if things continue fine.
I have done something similar when upgrading from 9.6 to 11 using pglogical and then dropping the extension and it was smooth,
maybe you need to try this out and share if things works fine.
and 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux