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.git...@gmail.com> wrote: > On Thu, 5 Aug 2021 at 10:27, Nikhil Shetty <nikhil.db...@gmail.com> 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. > pglogical/010_pglogical_create_subscriber.pl at REL2_x_STABLE · > 2ndQuadrant/pglogical (github.com) > <https://github.com/2ndQuadrant/pglogical/blob/REL2_x_STABLE/t/010_pglogical_create_subscriber.pl> > 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 > The 1-2-3 for PostgreSQL Logical Replication Using an RDS Snapshot - > Percona Database Performance Blog > <https://www.percona.com/blog/postgresql-logical-replication-using-an-rds-snapshot/> > >
