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

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:

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 CASCADE

ERROR: 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,

Fabrice

On Wed, Feb 21, 2024 at 12:47 PM M Sarwar <sarwarmd02@outlook.com> wrote:

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

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

--
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: