Re: pg_restore --clean failing due to dependancies

"Arnaud L." <arnaud(dot)listes(at)codata(dot)eu> writes:
> Hi am making daily backups of a database and omitting two schemas from
> the dump (and the public schema). Those schemas are read-only and have
> no dependancy with the rest of the database.
> My command is :
> pg_dump -h pgsql1 -U postgres -b -Fc -E UTF8 -N public -N osm -N osm2 -f
> "D:\db.dump" db1

> Now, I would like to restore this dump in a database where the
> aforementioned schemas have been loaded. So i would like to do a
> pg_restore --clean, in order two preserve those two schemas (and the
> public one), and to restore everything else.
> The restore fails on a lot of statements, complaining about dependencies.
> For instance, "cannot drop rule _RETURN on view myview1 because view
> myview1requires it". Or "cannot drop constraint mypkey on table my table
> because other objects depend on it [list of foreign keys]".

You'd have to provide a lot more detail before anyone could tell if there
was a fixable bug here, but I rather doubt it. There are at least two
ways this scenario might lose:

1. There are additional objects in the target database that have
dependencies on ones that are in the dump. In that case there is
no ordering of dropping the objects in the dump that will succeed.

2. There are dependency chains passing through objects that weren't
dumped (ie, if A depends on B which depends on C, and you omit B
from the dump, it might still be the case that A can't be restored
before C).

If you think neither of those cases apply, please provide a self-contained
test case.

regards, tom lane