Thread: pglogical performance for copying large table
Hi,
I am working on migration of Aurora PostgreSQL to Ec2 with community postgresql.
I am using postgresql 12 version. Both the machines has 128 GB of ram and 32 core cpu.
I have set the parallel processes for logical replication as 12.
I am able to copy data at speed of around 8 MB/sec using pglogical.
I have dropped all indexes/triggers/constraints on destination except primary key. The table size is 2 TB. Is there any way I can improve the performance ?
Thanks
Srinivas
The best performance for moving postgres data is to use parallel pg_dump and pg_restore with no compression, in your situation I would use parallel level of 8.
Order of events:
Move the structure of the database only to the destination server from a pg_dump. Use the disable trigger option and make sure triggers are remain disabled on destination.
Take another pg_dump in parallel and only get the data.
Create 3 script to capture all primary key indexes, domain indexes and foreign key indexes
Order of events:
Move the structure of the database only to the destination server from a pg_dump. Use the disable trigger option and make sure triggers are remain disabled on destination.
Take another pg_dump in parallel and only get the data.
Create 3 script to capture all primary key indexes, domain indexes and foreign key indexes
Create 3 scripts to drop the 3 index types mentioned above.
Create 3 scripts to create all index types mentioned above.
Change your memory to 256 GB and set work_mem=4GB. Each session has lots of memory for sorting to build indexes
Drop all indexes, constraints and triggers
Do the pg_restore of the data
Verify the counts
Run the rebuild index scripts.
This takes some up front scripting but 2 Terabytes should be done in a few hours, depending on network. Remember no compression on the pg_dump and pg_restore file sets.
Do the pg_restore of the data
Verify the counts
Run the rebuild index scripts.
This takes some up front scripting but 2 Terabytes should be done in a few hours, depending on network. Remember no compression on the pg_dump and pg_restore file sets.
On Mon, Feb 13, 2023 at 13:30 srinivas oguri <srinivasoguri7@gmail.com> wrote:
Hi,I am working on migration of Aurora PostgreSQL to Ec2 with community postgresql.I am using postgresql 12 version. Both the machines has 128 GB of ram and 32 core cpu.I have set the parallel processes for logical replication as 12.I am able to copy data at speed of around 8 MB/sec using pglogical.I have dropped all indexes/triggers/constraints on destination except primary key. The table size is 2 TB. Is there any way I can improve the performance ?ThanksSrinivas
On Mon, Feb 13, 2023 at 1:30 PM srinivas oguri <srinivasoguri7@gmail.com> wrote:
I have set the parallel processes for logical replication as 12.
What does this mean in terms of parameters? Are all of them being used?
Cheers,
Jeff
>> What does this mean in terms of parameters? Are all of them being used?
No, actually it is restricted to only one process which is running copy command.
>> Pg_dump and pg_restore
Basically this is one of the database we have largest database which is of 20 TB. We would like to configure replication by which we will be able to switch with less downtime.
Is it possible to configure the logical replication with pg_dump for initial data copy ? Can you please help me with detailed steps.
On Tue, Feb 14, 2023, 4:07 AM Jeff Janes <jeff.janes@gmail.com> wrote:
On Mon, Feb 13, 2023 at 1:30 PM srinivas oguri <srinivasoguri7@gmail.com> wrote:I have set the parallel processes for logical replication as 12.What does this mean in terms of parameters? Are all of them being used?Cheers,Jeff