Обсуждение: pg_restore --clean failing due to dependancies
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]". 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 ? Thanks for your help ! Cheers -- Arnaud
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
"Arnaud L." <arnaud.listes@codata.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
Le 15/11/2016 à 16:21, Adrian Klaver a écrit : >> 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? They are in a schema that is included in the dump (more precisely, not excluded from). It happens in all schemas, not a specific one. > pg_restore -l "D:\db.dump" > to see what pg_restore thinks is the order of restore. OK I did that. The output is pretty big. The second error is a consequence of the first one (they were not foreign keys but views, sorry for the mistake). So the very first error from pg_restore, the "cannot drop rule _return..." is TOC 11330. It is on the line 4948 in the pg_restore -l output (does this order matter, or is it the TOC ?). The view is number 1467 in the TOC, and is on line 2353 of the pg_restore -l output. -- Arnaud
Le 15/11/2016 à 16:44, Tom Lane a écrit : > 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. The target databased was restored from the dump in an empty database. So what I do is : dropdb -U postgres -h localhost db1 createdb -U postgres -h localhost db1 psql -U postgres -h localhost -c "CREATE EXTENSION postgis" db1 pg_dump -h pgsql1 -U postgres -b -Fc -E UTF8 -N public -N osm -N osm2 -f "D:\temp.dump" db1 pg_restore -U postgres -h localhost -j 2 -d db1 "D:\temp.dump" That works, no error, no warning. Then pg_restore -U postgres -h localhost --clean -d db1 "D:\temp.dump" fails. So can we exclude additional objects in the target database in this case ? > 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). Can I trust what pgadmin says about objects dependent on a schema ? It says that public schema's dependent objects are only it's own operators, functions, etc. (i.e., what's in the postgis extension), and the same for the other two schemas. They don't show any dependent objects outside themselves. > If you think neither of those cases apply, please provide a self-contained > test case. That's not going to be easy. I'll try to trim down a pg_dump -s to see how I can reproduce this. -- Arnaud
"Arnaud L." <arnaud.listes@codata.eu> writes:
> The target databased was restored from the dump in an empty database.
> So what I do is :
> dropdb -U postgres -h localhost db1
> createdb -U postgres -h localhost db1
> psql -U postgres -h localhost -c "CREATE EXTENSION postgis" db1
> pg_dump -h pgsql1 -U postgres -b -Fc -E UTF8 -N public -N osm -N osm2 -f
> "D:\temp.dump" db1
> pg_restore -U postgres -h localhost -j 2 -d db1 "D:\temp.dump"
> That works, no error, no warning.
> Then
> pg_restore -U postgres -h localhost --clean -d db1 "D:\temp.dump"
> fails.
[ squint... ] That does look like it ought to work. I wonder if postgis
is doing something weird? But again, it's impossible to debug this at
this level of detail. If you can make a test case I'd be happy to look
into it.
regards, tom lane
On 11/15/2016 07:55 AM, Arnaud L. wrote: > Le 15/11/2016 à 16:21, Adrian Klaver a écrit : >>> 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? > > They are in a schema that is included in the dump (more precisely, not > excluded from). > It happens in all schemas, not a specific one. > >> pg_restore -l "D:\db.dump" >> to see what pg_restore thinks is the order of restore. > > OK I did that. > The output is pretty big. > The second error is a consequence of the first one (they were not > foreign keys but views, sorry for the mistake). > So the very first error from pg_restore, the "cannot drop rule > _return..." is TOC 11330. > It is on the line 4948 in the pg_restore -l output (does this order > matter, or is it the TOC ?). The line order: https://www.postgresql.org/docs/9.5/static/app-pgrestore.htm "The listing file consists of a header and one line for each item, e.g.: .... Semicolons start a comment, and the numbers at the start of lines refer to the internal archive ID assigned to each item. Lines in the file can be commented out, deleted, and reordered. For example: ...... " > The view is number 1467 in the TOC, and is on line 2353 of the > pg_restore -l output. > > > -- > Arnaud > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/15/2016 08:09 AM, Arnaud L. wrote: > Le 15/11/2016 à 16:44, Tom Lane a écrit : >> 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. > > The target databased was restored from the dump in an empty database. > So what I do is : > dropdb -U postgres -h localhost db1 > createdb -U postgres -h localhost db1 > psql -U postgres -h localhost -c "CREATE EXTENSION postgis" db1 > pg_dump -h pgsql1 -U postgres -b -Fc -E UTF8 -N public -N osm -N osm2 -f > "D:\temp.dump" db1 > pg_restore -U postgres -h localhost -j 2 -d db1 "D:\temp.dump" > > That works, no error, no warning. > > Then > pg_restore -U postgres -h localhost --clean -d db1 "D:\temp.dump" > fails. > > So can we exclude additional objects in the target database in this case ? > > >> 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). > > Can I trust what pgadmin says about objects dependent on a schema ? Believe that only shows objects that have are declared for that schema. It does not show internal relationships of the objects to other objects outside their schema. In other words a function that is public.some_fnc but inside the function body operates on objects in another schema. Or a table in one schema that has a FK to a table in another schema and so on. > It says that public schema's dependent objects are only it's own > operators, functions, etc. (i.e., what's in the postgis extension), and > the same for the other two schemas. > They don't show any dependent objects outside themselves. > > >> If you think neither of those cases apply, please provide a >> self-contained >> test case. > > That's not going to be easy. I'll try to trim down a pg_dump -s to see > how I can reproduce this. > > -- > Arnaud > > > > -- Adrian Klaver adrian.klaver@aklaver.com
Arnaud Lesauvage <arnaud.lesauvage@codata.eu> writes:
> [ dump from problematic database ]
OK, thanks for the test case. The problem here is that pg_dump is setting
up a circular dependency that it doesn't know how to break correctly.
You've got a couple of views that are implicitly dependent on the primary
keys of their underlying tables, because they use a GROUP BY the primary
key without also grouping by other columns they use post-grouping. That
means that pg_dump has to dump the view definition after the creation of
the primary key, but it also needs to put the view out sooner than that
for other reasons. It manages to deal with that okay in the default mode,
but when you have --clean in there, it ends up generating an illegal DROP
RULE command.
This is something we ought to fix, but it's not exactly trivial to do.
In the meantime I'd suggest changing the view definitions to not assume
that the underlying tables have primary keys. It looks like in
view_temp_export_geo_recherche_extra_sites_projets you need to add
c.official_language_id to the GROUP BY, and similarly in
view_temp_export_geo_recherche_offtrad_sites.
regards, tom lane
Le 16/11/2016 à 20:05, Tom Lane a écrit : > Arnaud Lesauvage <arnaud.lesauvage@codata.eu> writes: >> [ dump from problematic database ] > > OK, thanks for the test case. The problem here is that pg_dump is setting > up a circular dependency that it doesn't know how to break correctly. > You've got a couple of views that are implicitly dependent on the primary > keys of their underlying tables, because they use a GROUP BY the primary > key without also grouping by other columns they use post-grouping. That > means that pg_dump has to dump the view definition after the creation of > the primary key, but it also needs to put the view out sooner than that > for other reasons. It manages to deal with that okay in the default mode, > but when you have --clean in there, it ends up generating an illegal DROP > RULE command. All right, at least I'm glad that I did not miss something obvious. > This is something we ought to fix, but it's not exactly trivial to do. > In the meantime I'd suggest changing the view definitions to not assume > that the underlying tables have primary keys. It looks like in > view_temp_export_geo_recherche_extra_sites_projets you need to add > c.official_language_id to the GROUP BY, and similarly in > view_temp_export_geo_recherche_offtrad_sites. Thanks for the tip ! I'll try this ASAP. I never "GROUP BY" primary keys only, so I can consider this as an error that needs fixing. I did not even know that this was valid SQL to be honest. Thanks a lot for your help ! Regards -- Arnaud