Question about 'DATA' copy performace

[samodadela]

Hi,

I tested pg-clone-schema on my database (localhost).

I have another way of 'cloning' the schema. I apply all the migrations to create a fresh schema, remove all FK constraints, copy the data, reinstall the FK constraints, copy sequence values over. (The removal of constraints is needed because I need to run this on GCP (managed) Cloud SQL and there is no way to deffer constraints)

So comparing those two, I noticed a big difference in speed.

• pg-clone-schema: 23 minutes
• method mentioned above: 2 minutes (of which 60 seconds are used for copying data)

I wonder how pg-clone-schema works? Is the data copied before the indexes and constraints are created, or after? If indexes and FK constraints are the cause of the slowdown - would it be possible to deffer FK constraint and index creation to the end?

[MichaelDBA]

@samodadela, How are you copying the data?

[MichaelDBA]

Copying large datasets would best be done using pg_dump/pg_restore in directory mode so that you could paralyze the process. Only really useful for small datasets with clone_schema, even if we did defer the key/index creations since it is done sequentially, one table at a time.

[MichaelDBA]

See limitations in readme for clarification