Spurious error messages from pg_restore

From: Evan Martin <postgresql(at)realityexists(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Spurious error messages from pg_restore
Date: 2013-09-04 14:02:52
Message-ID: 52273D8C.7040105@realityexists.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 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 couldalso 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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2013-09-04 14:20:58 Re: Spurious error messages from pg_restore
Previous Message Trigve Siver 2013-09-04 13:06:53 FETCH FORWARD 0 and "cursor can only scan forward" error