Re: pg_restore option --clean

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Guillaume Lelarge <guillaume(at)lelarge(dot)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
>