Обсуждение: Fwd: pg_restore option --clean
Hi,
The --clean option of pg_restore allows you to replace an object before being imported. However, dependencies such as foreign keys or views prevent the deletion of the object. Is there a way to add the cascade option to force the deletion?
Thanks for helping
Fabrice
Look around for
ALTER TABLE TABLE-NAME
ADD constraint fk-name foreign key col-name refers to tab-name ( col-name )
on UPDATE cascase
on DELETE CASCADE
;
Good luck,
Sarwar
From: Fabrice Chapuis <fabrice636861@gmail.com>
Sent: Wednesday, February 21, 2024 4:17 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Fwd: pg_restore option --clean
Sent: Wednesday, February 21, 2024 4:17 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Fwd: pg_restore option --clean
Hi,
The --clean option of pg_restore allows you to replace an object before being imported. However, dependencies such as foreign keys or views prevent the deletion of the object. Is there a way to add the cascade option to force the deletion?
Thanks for helping
Fabrice
But it does not work for the structure
# CONSTRAINT test FOREIGN KEY (id_tab_key) REFERENCES tab(id) ON DELETE cascade ON UPDATE CASCADE
ERROR: cannot drop table tab because other objects depend on it
Regards,
Fabrice
On Wed, Feb 21, 2024 at 12:47 PM M Sarwar <sarwarmd02@outlook.com> wrote:
Look around forALTER TABLE TABLE-NAMEADD constraint fk-name foreign key col-name refers to tab-name ( col-name )on UPDATE cascaseon DELETE CASCADE;Good luck,SarwarFrom: Fabrice Chapuis <fabrice636861@gmail.com>
Sent: Wednesday, February 21, 2024 4:17 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Fwd: pg_restore option --cleanHi,The --clean option of pg_restore allows you to replace an object before being imported. However, dependencies such as foreign keys or views prevent the deletion of the object. Is there a way to add the cascade option to force the deletion?Thanks for helpingFabrice
Hi,
Le mer. 21 févr. 2024 à 15:01, Fabrice Chapuis <fabrice636861@gmail.com> a écrit :
But it does not work for the structure# CONSTRAINT test FOREIGN KEY (id_tab_key) REFERENCES tab(id) ON DELETE cascade ON UPDATE CASCADEERROR: cannot drop table tab because other objects depend on it
Yeah, ON DELETE and ON CASCADE are not the answer to your question.
pg_restore won't drop objects in cascade. There's no option for that. I'd guess the reason is that --clean only cleans the object it will restore. If other objects depend on it, pg_restore has no way to know how to recreate them, and you would end up with a not completely restored database.
Regards.
Regards,FabriceOn Wed, Feb 21, 2024 at 12:47 PM M Sarwar <sarwarmd02@outlook.com> wrote:Look around forALTER TABLE TABLE-NAMEADD constraint fk-name foreign key col-name refers to tab-name ( col-name )on UPDATE cascaseon DELETE CASCADE;Good luck,SarwarFrom: Fabrice Chapuis <fabrice636861@gmail.com>
Sent: Wednesday, February 21, 2024 4:17 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Fwd: pg_restore option --cleanHi,The --clean option of pg_restore allows you to replace an object before being imported. However, dependencies such as foreign keys or views prevent the deletion of the object. Is there a way to add the cascade option to force the deletion?Thanks for helpingFabrice
--
Guillaume.
Guillaume Lelarge <guillaume@lelarge.info> writes:
> pg_restore won't drop objects in cascade. There's no option for that. I'd
> guess the reason is that --clean only cleans the object it will restore. If
> other objects depend on it, pg_restore has no way to know how to recreate
> them, and you would end up with a not completely restored database.
Yeah. The expectation is that --clean will issue the DROP commands
in reverse dependency order, so that no step would require CASCADE.
If one did, it'd imply that pg_dump failed to catalog all the
dependencies in the database, which would be a bug we'd want to know
about.
Now, this theory does fail in at least two practical cases:
* You're trying to use --clean with a selective restore.
* You're trying to restore into a database that has more or
different objects than the source DB did.
But in both cases, blindly using CASCADE seems like a bad idea.
You'd end up with a database that's missing some objects, and
you won't know which ones or how to put them back.
regards, tom lane
Effectivly, Tom, we are in the second case, we are carrying out a partial restore, certain tables remain in place and are not replaced, currently I have to do manually a DROP... CASCADE and recreate the dependencies between the objects which are imported and those which are in place.No choice to continue with this approach.
Regarde, Fabrice
On Wed, Feb 21, 2024 at 4:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Guillaume Lelarge <guillaume@lelarge.info> writes:
> pg_restore won't drop objects in cascade. There's no option for that. I'd
> guess the reason is that --clean only cleans the object it will restore. If
> other objects depend on it, pg_restore has no way to know how to recreate
> them, and you would end up with a not completely restored database.
Yeah. The expectation is that --clean will issue the DROP commands
in reverse dependency order, so that no step would require CASCADE.
If one did, it'd imply that pg_dump failed to catalog all the
dependencies in the database, which would be a bug we'd want to know
about.
Now, this theory does fail in at least two practical cases:
* You're trying to use --clean with a selective restore.
* You're trying to restore into a database that has more or
different objects than the source DB did.
But in both cases, blindly using CASCADE seems like a bad idea.
You'd end up with a database that's missing some objects, and
you won't know which ones or how to put them back.
regards, tom lane