pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore option --clean
7+ messages / 4 participants
[nested] [flat]
* pg_restore option --clean
@ 2024-02-20 07:47 Fabrice Chapuis <fabrice636861@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Fabrice Chapuis @ 2024-02-20 07:47 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Fwd: pg_restore option --clean
@ 2024-02-21 09:17 Fabrice Chapuis <fabrice636861@gmail.com>
parent: Fabrice Chapuis <fabrice636861@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Fabrice Chapuis @ 2024-02-21 09:17 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore option --clean
@ 2024-02-21 11:47 M Sarwar <sarwarmd02@outlook.com>
parent: Fabrice Chapuis <fabrice636861@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: M Sarwar @ 2024-02-21 11:47 UTC (permalink / raw)
To: Fabrice Chapuis <fabrice636861@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore option --clean
@ 2024-02-21 14:00 Fabrice Chapuis <fabrice636861@gmail.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 7+ messages in thread
From: Fabrice Chapuis @ 2024-02-21 14:00 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore option --clean
@ 2024-02-21 14:35 Guillaume Lelarge <guillaume@lelarge.info>
parent: Fabrice Chapuis <fabrice636861@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Guillaume Lelarge @ 2024-02-21 14:35 UTC (permalink / raw)
To: Fabrice Chapuis <fabrice636861@gmail.com>; +Cc: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore option --clean
@ 2024-02-21 15:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2024-02-21 15:31 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Fabrice Chapuis <fabrice636861@gmail.com>; M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore option --clean
@ 2024-02-22 08:05 Fabrice Chapuis <fabrice636861@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Fabrice Chapuis @ 2024-02-22 08:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-02-22 08:05 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-20 07:47 pg_restore option --clean Fabrice Chapuis <fabrice636861@gmail.com>
2024-02-21 09:17 ` Fabrice Chapuis <fabrice636861@gmail.com>
2024-02-21 11:47 ` M Sarwar <sarwarmd02@outlook.com>
2024-02-21 14:00 ` Fabrice Chapuis <fabrice636861@gmail.com>
2024-02-21 14:35 ` Guillaume Lelarge <guillaume@lelarge.info>
2024-02-21 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-22 08:05 ` Fabrice Chapuis <fabrice636861@gmail.com>
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