Обсуждение: pg_restore with -j > 1 breaks the "clean" phase by not removing dependencies in order

Hi,

I'm having some trouble getting parallel restores to work. When running pg_restore like this:

pg_restore --no-password --host=$MAIN_CLUSTER-pg-cluster --port=5432 --dbname=$name --jobs=4 --format=d --no-owner
--clean--if-exists --disable-triggers --role=${name}_owner "/temp_data/data-$name-$UNIQUE_SUFFIX"
 

the restore errors on foreign key constraints while removing tables, is there anyway to keep this parallel but have it
respectthe tree of dependencies.
 

Errors like this:
pg_restore: error: could not execute query: ERROR:  cannot drop table public.organisations because other objects depend
onit
 
DETAIL:  constraint fk_groups_organisations_organisation_id on table public.groups depends on table
public.organisations
constraint fk_organisation_role_organisations_organisation_id on table public.organisation_role depends on table
public.organisations
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Command was: DROP TABLE IF EXISTS public.organisations;

When executed with --jobs=1 this command works.

So I tried the following: (to restore data and make indices with more than one thread but do the initial table creating
andcleaning without)    
 

pg_restore --no-password --host=$MAIN_CLUSTER-pg-cluster --port=5432 --dbname=$name --jobs=1 --section=pre-data
--format=d--no-owner --clean --if-exists --disable-triggers --role=${name}_owner
"/temp_data/data-$name-$UNIQUE_SUFFIX"
pg_restore --no-password --host=$MAIN_CLUSTER-pg-cluster --port=5432 --dbname=$name --jobs=8 --section=data --format=d
--no-owner--clean --if-exists --disable-triggers --role=${name}_owner "/temp_data/data-$name-$UNIQUE_SUFFIX"
 
pg_restore --no-password --host=$MAIN_CLUSTER-pg-cluster --port=5432 --dbname=$name --jobs=4 --section=post-data
--format=d--no-owner --clean --if-exists --disable-triggers --role=${name}_owner
"/temp_data/data-$name-$UNIQUE_SUFFIX"

But now pre-data does not remove the foreign key constraints at all so all tables can't be removed by --clean, breaking
thewhole thing.
 

Is there some way to make it do this internally? And this doesn't always reproduce either, seems like a bit of a race
conditionbecause one thread is just a bit too fast dropping tables.
 

We really want the indices to be created after the data gets put in because it is much much faster, than doing a
schema-onlyand data-only restore.
 

Kind regards,

Erwin de Haan

Erwin de Haan <erwin.de.haan@calcasa.nl> writes:
> I'm having some trouble getting parallel restores to work. When running pg_restore like this:

> pg_restore --no-password --host=$MAIN_CLUSTER-pg-cluster --port=5432 --dbname=$name --jobs=4 --format=d --no-owner
--clean--if-exists --disable-triggers --role=${name}_owner "/temp_data/data-$name-$UNIQUE_SUFFIX" 

> the restore errors on foreign key constraints while removing tables, is there anyway to keep this parallel but have
itrespect the tree of dependencies. 

pg_restore does not parallelize the DROP stage of the process, so
I can't help thinking you've misidentified the source of the problem.

Can you provide a concrete example that misbehaves?
Also, what PG version is this?

            regards, tom lane