pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore with -j > 1 breaks the "clean" phase by not removing dependencies in order
3+ messages / 3 participants
[nested] [flat]
* pg_restore with -j > 1 breaks the "clean" phase by not removing dependencies in order
@ 2024-03-18 09:34 Erwin de Haan <erwin.de.haan@calcasa.nl>
0 siblings, 2 replies; 3+ messages in thread
From: Erwin de Haan @ 2024-03-18 09:34 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_restore with -j > 1 breaks the "clean" phase by not removing dependencies in order
@ 2024-03-18 13:59 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Erwin de Haan <erwin.de.haan@calcasa.nl>
1 sibling, 0 replies; 3+ messages in thread
From: Ron Johnson @ 2024-03-18 13:59 UTC (permalink / raw)
To: Erwin de Haan <erwin.de.haan@calcasa.nl>; +Cc: pgsql-admin
On Mon, Mar 18, 2024 at 9:49 AM Erwin de Haan <erwin.de.haan@calcasa.nl>
wrote:
> 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
>
>
Try "--dbname=template1" instead of "--dbname=$name".
This *always* works for me:
pg_restore -v --jobs=$Threads --clean --create -Fd --dbname=postgres
$BackupRoot/$DbName
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_restore with -j > 1 breaks the "clean" phase by not removing dependencies in order
@ 2024-03-18 14:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Erwin de Haan <erwin.de.haan@calcasa.nl>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2024-03-18 14:40 UTC (permalink / raw)
To: Erwin de Haan <erwin.de.haan@calcasa.nl>; +Cc: pgsql-admin
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 it respect 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-03-18 14:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-18 09:34 pg_restore with -j > 1 breaks the "clean" phase by not removing dependencies in order Erwin de Haan <erwin.de.haan@calcasa.nl>
2024-03-18 13:59 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-03-18 14:40 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox