Обсуждение: Spurious error messages from pg_restore
When I use pg_restore with --clean to restore a PostgreSQL 9.2.4 database into a new, blank database it generates thousands of error messages like this:
pg_restore: [archiver (db)] could not execute query: ERROR: schema "myschema" does not exist
Command was: DROP INDEX myschema.some_index;
The documentation [http://www.postgresql.org/docs/9.2/static/app-pgrestore.html] does warn about this:
--clean
Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)
However, I don't agree that the error messages are "harmless". The harm is that I don't know whether everything was restored successfully or not. I think that's a serious problem. I cannot be sure that I haven't missed a "real" error among the thousands of "harmless" ones.
Could pg_restore be smarter about this and use DROP IF EXISTS wherever possible? That won't be enough in cases where the schema doesn't exist, either, but it could also query to see which schemas exist and avoid trying to drop anything in those that don't (this might even save a little time).
Also, even without --clean I get 3 errors:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6755; 2618 4417788 RULE geometry_columns_delete em
pg_restore: [archiver (db)] could not execute query: ERROR: rule "geometry_columns_delete" for relation "geometry_columns" already exists
Command was: CREATE RULE geometry_columns_delete AS ON DELETE TO geometry_columns DO INSTEAD NOTHING;
(and same for "geometry_columns_insert" and "geometry_columns_update")
This is probably because I have PostGIS installed as an extension (in both the source and target databases), which creates those rules. Could pg_restore use CREATE OR REPLACE whenever possible to avoid this?
pg_restore: [archiver (db)] could not execute query: ERROR: schema "myschema" does not exist
Command was: DROP INDEX myschema.some_index;
The documentation [http://www.postgresql.org/docs/9.2/static/app-pgrestore.html] does warn about this:
--clean
Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)
However, I don't agree that the error messages are "harmless". The harm is that I don't know whether everything was restored successfully or not. I think that's a serious problem. I cannot be sure that I haven't missed a "real" error among the thousands of "harmless" ones.
Could pg_restore be smarter about this and use DROP IF EXISTS wherever possible? That won't be enough in cases where the schema doesn't exist, either, but it could also query to see which schemas exist and avoid trying to drop anything in those that don't (this might even save a little time).
Also, even without --clean I get 3 errors:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6755; 2618 4417788 RULE geometry_columns_delete em
pg_restore: [archiver (db)] could not execute query: ERROR: rule "geometry_columns_delete" for relation "geometry_columns" already exists
Command was: CREATE RULE geometry_columns_delete AS ON DELETE TO geometry_columns DO INSTEAD NOTHING;
(and same for "geometry_columns_insert" and "geometry_columns_update")
This is probably because I have PostGIS installed as an extension (in both the source and target databases), which creates those rules. Could pg_restore use CREATE OR REPLACE whenever possible to avoid this?
On 09/04/2013 07:02 AM, Evan Martin wrote: > Also, even without --clean I get 3 errors: > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 6755; 2618 4417788 RULE > geometry_columns_delete em > pg_restore: [archiver (db)] could not execute query: ERROR: rule > "geometry_columns_delete" for relation "geometry_columns" already exists > Command was: CREATE RULE geometry_columns_delete AS ON DELETE TO > geometry_columns DO INSTEAD NOTHING; > > (and same for "geometry_columns_insert" and "geometry_columns_update") > > This is probably because I have PostGIS installed as an extension (in > both the source and target databases), which creates those rules. Could > pg_restore use CREATE OR REPLACE whenever possible to avoid this? I could see where CREATE OR REPLACE would lead to hidden corruption. It is in entirely possible for a RULE or FUNCTION to have the same name in different databases, but have different code under the name. Blindly REPLACEing would get you past the error messages, however it is possible that it leads to all sorts of headaches later. -- Adrian Klaver adrian.klaver@gmail.com
I'm not sure I see the problem. The rule name is only unique within the table, so it would not replace a rule on a different table (or view, as is the case here) than the one being restored. Functions names should be schema-qualified and if I'm restoring a function from a backup I would want any existing function with the same signature to be overwritten. On 04.09.2013 18:20, Adrian Klaver wrote: > On 09/04/2013 07:02 AM, Evan Martin wrote: > >> Also, even without --clean I get 3 errors: >> >> pg_restore: [archiver (db)] Error while PROCESSING TOC: >> pg_restore: [archiver (db)] Error from TOC entry 6755; 2618 4417788 RULE >> geometry_columns_delete em >> pg_restore: [archiver (db)] could not execute query: ERROR: rule >> "geometry_columns_delete" for relation "geometry_columns" already exists >> Command was: CREATE RULE geometry_columns_delete AS ON DELETE TO >> geometry_columns DO INSTEAD NOTHING; >> >> (and same for "geometry_columns_insert" and "geometry_columns_update") >> >> This is probably because I have PostGIS installed as an extension (in >> both the source and target databases), which creates those rules. Could >> pg_restore use CREATE OR REPLACE whenever possible to avoid this? > > I could see where CREATE OR REPLACE would lead to hidden corruption. > It is in entirely possible for a RULE or FUNCTION to have the same > name in different databases, but have different code under the name. > Blindly REPLACEing would get you past the error messages, however it > is possible that it leads to all sorts of headaches later. > >
Evan Martin wrote > When I use pg_restore with --clean to restore a PostgreSQL 9.2.4database > into a new, blank database it generates thousands of error messages like > this: > > pg_restore: [archiver (db)] could not execute query: ERROR: schema > "myschema" does not exist > Command was: DROP INDEX myschema.some_index; The "--clean" parameter is an "option" that you can enable if you feel it will provide value to your routine. If you are installing into a "new, blank database" the clean option has no value to you and you should not be using it. The resulting log, when using this option, needs to be processed using tools like "grep" so that you can: A) identify and scan over all those "does not exist" messages B) isolate and review any other message not containing "does not exist" There are lots on inter-related pieces involved in the whole dump/restore process. Your current use-case and example for the "--clean" option are not going to convince anyone that something requires fixing. That said bringing up stuff like this is always welcome. It helps people to learn and also provides insight to the developers as to what kinds of usage exists in the wild. Additional application of "IF EXISTS" likely would be welcome but the "--clean" option is rarely going to be noise-less. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Spurious-error-messages-from-pg-restore-tp5769545p5769625.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Evan Martin wrote > Also, even without --clean I get 3 errors: > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 6755; 2618 4417788 RULE > geometry_columns_delete em > pg_restore: [archiver (db)] could not execute query: ERROR: rule > "geometry_columns_delete" for relation "geometry_columns" already exists > Command was: CREATE RULE geometry_columns_delete AS ON DELETE TO > geometry_columns DO INSTEAD NOTHING; > > (and same for "geometry_columns_insert" and "geometry_columns_update") I should probably go read up on this but I thought the behavior of extensions was such that anything created from an extension is omitted from the traditional dump/restore process. Is this a within 9.2 dump/restore or are you crossing version boundaries somewhere? What was the original version of PostgreSQL when PostGIS was installed and how was it installed? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Spurious-error-messages-from-pg-restore-tp5769545p5769626.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
No version boundaries. I can reproduce this on 9.2.4 by backing up and immediately restoring a new DB with PostGIS: CREATE DATABASE test; CREATE EXTENSION postgis; -- Version 2.1 pg_dump --format custom --file test.bak test pg_restore --jobs 4 --dbname test test.bak On 04.09.2013 23:31, David Johnston wrote: > Evan Martin wrote >> Also, even without --clean I get 3 errors: >> >> pg_restore: [archiver (db)] Error while PROCESSING TOC: >> pg_restore: [archiver (db)] Error from TOC entry 6755; 2618 4417788 RULE >> geometry_columns_delete em >> pg_restore: [archiver (db)] could not execute query: ERROR: rule >> "geometry_columns_delete" for relation "geometry_columns" already exists >> Command was: CREATE RULE geometry_columns_delete AS ON DELETE TO >> geometry_columns DO INSTEAD NOTHING; >> >> (and same for "geometry_columns_insert" and "geometry_columns_update") > I should probably go read up on this but I thought the behavior of > extensions was such that anything created from an extension is omitted from > the traditional dump/restore process. > > Is this a within 9.2 dump/restore or are you crossing version boundaries > somewhere? > > What was the original version of PostgreSQL when PostGIS was installed and > how was it installed? > > David J. > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Spurious-error-messages-from-pg-restore-tp5769545p5769626.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > >
There may be implementation reasons for this, but try to see it from a user's point of view. An error, to a user, means "something went wrong; whatever you tried to do didn't work". If a message is harmless, it should be, at best, a warning. From a more practical point of view, it's not realistic review all the "does not exist" messages, as you suggest - there are literally thousands of them. So, as it is, I cannot be sure whether the restore succeeded or failed and that's bad.
Also, let's take a step back and consider what "restore" means to a user. It does not mean "drop some objects, create some objects, insert some rows, etc." Those are implementation details. To a user, restore means "make the database/schema/table exactly like it was when I did the backup". The current state of the object is irrelevant. So I can't even see any good reason for the --clean option to exist at all.
It may not be possible to achieve this in practice given the way pg_restore is implemented, I don't know, but I think it should be the aim. If something as simple as adding "IF EXISTS" and "OR REPLACE" can make a major difference then it would be a step in the right direction.
Basically, as a user, I find PG backups/restores to be a bit of a minefield. You can go through it, but you have to do it just right. The reason I originally added the --clean option to my script is that the restore failed without it (since I did have some objects in the database). So now at least I know that to restore the entire DB I need to drop it, re-create it, and run pg_restore without --clean (and ignore those 3 errors I mentioned). It took me a long time to figure this out, though, and such pain points are unnecessary. Backups and restores should "just work". They do in MSSQL and I think they can in Postgres, too.
Regards,
Evan
On 04.09.2013 23:26, David Johnston wrote:
Also, let's take a step back and consider what "restore" means to a user. It does not mean "drop some objects, create some objects, insert some rows, etc." Those are implementation details. To a user, restore means "make the database/schema/table exactly like it was when I did the backup". The current state of the object is irrelevant. So I can't even see any good reason for the --clean option to exist at all.
It may not be possible to achieve this in practice given the way pg_restore is implemented, I don't know, but I think it should be the aim. If something as simple as adding "IF EXISTS" and "OR REPLACE" can make a major difference then it would be a step in the right direction.
Basically, as a user, I find PG backups/restores to be a bit of a minefield. You can go through it, but you have to do it just right. The reason I originally added the --clean option to my script is that the restore failed without it (since I did have some objects in the database). So now at least I know that to restore the entire DB I need to drop it, re-create it, and run pg_restore without --clean (and ignore those 3 errors I mentioned). It took me a long time to figure this out, though, and such pain points are unnecessary. Backups and restores should "just work". They do in MSSQL and I think they can in Postgres, too.
Regards,
Evan
On 04.09.2013 23:26, David Johnston wrote:
Evan Martin wroteWhen I use pg_restore with --clean to restore a PostgreSQL 9.2.4database into a new, blank database it generates thousands of error messages like this: pg_restore: [archiver (db)] could not execute query: ERROR: schema "myschema" does not exist Command was: DROP INDEX myschema.some_index;The "--clean" parameter is an "option" that you can enable if you feel it will provide value to your routine. If you are installing into a "new, blank database" the clean option has no value to you and you should not be using it. The resulting log, when using this option, needs to be processed using tools like "grep" so that you can: A) identify and scan over all those "does not exist" messages B) isolate and review any other message not containing "does not exist" There are lots on inter-related pieces involved in the whole dump/restore process. Your current use-case and example for the "--clean" option are not going to convince anyone that something requires fixing. That said bringing up stuff like this is always welcome. It helps people to learn and also provides insight to the developers as to what kinds of usage exists in the wild. Additional application of "IF EXISTS" likely would be welcome but the "--clean" option is rarely going to be noise-less. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Spurious-error-messages-from-pg-restore-tp5769545p5769625.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.