Re: pg_restore --clean failing due to dependancies

On 11/15/2016 07:11 AM, Arnaud L. wrote:
> Hi all
>
> Postgresql 9.3.14 on Windows.
> 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
>
> (I also omit "public" because we use postgis, so it's cleaner for me to
> dump without the public schema, then start from an empty database and do
> a "create extension postgis" before restoring)
>
> I can successfully restore this dump without any error in an empty
> database.
>
> 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]".

So where are the above objects, eg what schema?

>
> My command is :
> pg_restore -U postgres -h pgsql1 --clean -d db1 "D:\db.dump"
>
> I thought that pg_restore was supposed to reorder the objects to honor
> dependencies ? How can I get this right ?

To start:

pg_restore  -l  "D:\db.dump"

to see what pg_restore thinks is the order of restore.


>
> Thanks for your help !
> Cheers
>
> --
> Arnaud
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com