From: | Erwin de Haan <erwin(dot)de(dot)haan(at)calcasa(dot)nl> |
---|---|
To: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | pg_restore with -j > 1 breaks the "clean" phase by not removing dependencies in order |
Date: | 2024-03-18 09:34:29 |
Message-ID: | DU2PR01MB862463D00FF021DB969FE61CC82D2@DU2PR01MB8624.eurprd01.prod.exchangelabs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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 respect the tree of dependencies.
Errors like this:
pg_restore: error: could not execute query: ERROR: cannot drop table public.organisations because other objects depend on it
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 and cleaning 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 the whole 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 condition because 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-only and data-only restore.
Kind regards,
Erwin de Haan
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2024-03-18 13:59:05 | Re: pg_restore with -j > 1 breaks the "clean" phase by not removing dependencies in order |
Previous Message | hector vass | 2024-03-13 22:06:08 | Re: Dynamically generate varying Number of Column Headers from the Row data |