pg_restore fails

On Sun, Mar 13, 2016 at 12:09:19AM +0100, Karsten Hilbert wrote:

In case it is needed:

pg_restore: erstelle SCHEMA „public“

creating SCHEMA "public"

pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:

Error in Phase ...

pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8; 2615 2200 SCHEMA public postgres

Error in TOC entry 8 ...

pg_restore: [Archivierer (DB)] could not execute query: FEHLER: Schema „public“ existiert bereits

ERROR: Schema "public" already exists

Die Anweisung war: CREATE SCHEMA public;

The command was: CREATE ...

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/12/2016 03:09 PM, Karsten Hilbert wrote:

Hi,

Debian Stretch
PG 9.5.1

I am trying to pg_restore from a directory dump.

However, despite using

--clean
--create
--if-exists

I am getting an error because schema PUBLIC already exists.

That schema is, indeed, included in the dump to be restored
and also cannot be omitted from either the dump or the
restore because it still contains a few relevant things which
I haven't yet moved to their own app specific schema.

I am assuming (wrongly ?) that pg_restore uses template1 to
re-create the target database. I had to re-create template1
today from template0 (as is suggested) because I erroneously
added a few tables to template1 earlier. So, the newly
created target DB will, indeed, contain a schema PUBLIC
initially.

That should not (?) matter however, because of the above
options which I would have expected to drop the schema before
(re)creating it (--clean).

Here is the log:

sudo -u postgres pg_restore --verbose --create --clean --if-exists --exit-on-error --disable-triggers --dbname=template1 -p 5432 /tmp/gnumed/gm-restore_2016-03-12_23-58-05/backup-gnumed_v20-GNUmed_Team-hermes-2016-03-07-21-15-06.dir/
pg_restore: verbinde mit der Datenbank zur Wiederherstellung
pg_restore: entferne DATABASE gnumed_v20
pg_restore: erstelle DATABASE „gnumed_v20“
pg_restore: verbinde mit neuer Datenbank „gnumed_v20“
pg_restore: verbinde mit Datenbank „gnumed_v20“ als Benutzer „postgres“
pg_restore: erstelle SCHEMA „au“
pg_restore: erstelle SCHEMA „audit“
pg_restore: erstelle SCHEMA „bill“
pg_restore: erstelle COMMENT „SCHEMA bill“
pg_restore: erstelle SCHEMA „blobs“
pg_restore: erstelle SCHEMA „cfg“
pg_restore: erstelle COMMENT „SCHEMA cfg“
pg_restore: erstelle SCHEMA „clin“
pg_restore: erstelle SCHEMA „de_de“
pg_restore: erstelle SCHEMA „dem“
pg_restore: erstelle SCHEMA „gm“
pg_restore: erstelle SCHEMA „i18n“
pg_restore: erstelle SCHEMA „pgtrgm“
pg_restore: erstelle SCHEMA „public“
pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8; 2615 2200 SCHEMA public postgres
pg_restore: [Archivierer (DB)] could not execute query: FEHLER: Schema „public“ existiert bereits
Die Anweisung war: CREATE SCHEMA public;

I am sure I am doing something wrong, but what ?

Did it actually fail or did it just throw an error?
In other words did the restore continue past the error?

Thanks,
Karsten

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Mar 12, 2016 at 03:32:15PM -0800, Adrian Klaver wrote:

pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8; 2615 2200 SCHEMA public postgres
pg_restore: [Archivierer (DB)] could not execute query: FEHLER: Schema „public“ existiert bereits
Die Anweisung war: CREATE SCHEMA public;

I am sure I am doing something wrong, but what ?

Did it actually fail or did it just throw an error?
In other words did the restore continue past the error?

Good question. I'll remove the --exit-on-error and retry :-)

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Saturday, March 12, 2016, Karsten Hilbert <Karsten.Hilbert@gmx.net>
wrote:

Hi,

Debian Stretch
PG 9.5.1

I am trying to pg_restore from a directory dump.

However, despite using

--clean
--create
--if-exists

I am getting an error because schema PUBLIC already exists.

That schema is, indeed, included in the dump to be restored
and also cannot be omitted from either the dump or the
restore because it still contains a few relevant things which
I haven't yet moved to their own app specific schema.

I am assuming (wrongly ?) that pg_restore uses template1 to
re-create the target database. I had to re-create template1
today from template0 (as is suggested) because I erroneously
added a few tables to template1 earlier. So, the newly
created target DB will, indeed, contain a schema PUBLIC
initially.

That should not (?) matter however, because of the above
options which I would have expected to drop the schema before
(re)creating it (--clean).

Here is the log:

sudo -u postgres pg_restore --verbose --create --clean --if-exists
--exit-on-error --disable-triggers --dbname=template1 -p 5432
/tmp/gnumed/gm-restore_2016-03-12_23-58-05/backup-gnumed_v20-GNUmed_Team-hermes-2016-03-07-21-15-06.dir/
pg_restore: verbinde mit der Datenbank zur Wiederherstellung
pg_restore: entferne DATABASE gnumed_v20
pg_restore: erstelle DATABASE „gnumed_v20“
pg_restore: verbinde mit neuer Datenbank „gnumed_v20“
pg_restore: verbinde mit Datenbank „gnumed_v20“ als Benutzer
„postgres“
pg_restore: erstelle SCHEMA „au“
pg_restore: erstelle SCHEMA „audit“
pg_restore: erstelle SCHEMA „bill“
pg_restore: erstelle COMMENT „SCHEMA bill“
pg_restore: erstelle SCHEMA „blobs“
pg_restore: erstelle SCHEMA „cfg“
pg_restore: erstelle COMMENT „SCHEMA cfg“
pg_restore: erstelle SCHEMA „clin“
pg_restore: erstelle SCHEMA „de_de“
pg_restore: erstelle SCHEMA „dem“
pg_restore: erstelle SCHEMA „gm“
pg_restore: erstelle SCHEMA „i18n“
pg_restore: erstelle SCHEMA „pgtrgm“
pg_restore: erstelle SCHEMA „public“
pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag
8; 2615 2200 SCHEMA public postgres
pg_restore: [Archivierer (DB)] could not execute query: FEHLER:
Schema „public“ existiert bereits
Die Anweisung war: CREATE SCHEMA public;

I am sure I am doing something wrong, but what ?

The docs could probably use improvement here - though I am inferring
behavior from description and not code.

The create option tells restore that it is pointless to use conditions or
actively drop objects since the newly created database is expected to be
empty. The --clean option will cause pg_restore to drop the database if it
exists but only the database. The --if-exists option would seem to be
extraneous.

The clean option with create seems to be misleading since the advice later
in the document is to ensure the created database is empty by using
template0 - which you cannot specify directly within pg_restore and so
createdb or an equivalent command should be used to stage up the empty
database before performing a simple (no create or clean) restore.

I'm not certain why the create database command constructed when specifying
--create isn't just defaulted to template0...and for completeness a
--template option added for user template specification

David J.

On Sat, Mar 12, 2016 at 4:32 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 03/12/2016 03:09 PM, Karsten Hilbert wrote:

Hi,

Debian Stretch
PG 9.5.1

I am trying to pg_restore from a directory dump.

However, despite using

--clean
--create
--if-exists

I am getting an error because schema PUBLIC already exists.

That schema is, indeed, included in the dump to be restored
and also cannot be omitted from either the dump or the
restore because it still contains a few relevant things which
I haven't yet moved to their own app specific schema.

I am assuming (wrongly ?) that pg_restore uses template1 to
re-create the target database. I had to re-create template1
today from template0 (as is suggested) because I erroneously
added a few tables to template1 earlier. So, the newly
created target DB will, indeed, contain a schema PUBLIC
initially.

That should not (?) matter however, because of the above
options which I would have expected to drop the schema before
(re)creating it (--clean).

Here is the log:

sudo -u postgres pg_restore --verbose --create --clean
--if-exists --exit-on-error --disable-triggers --dbname=template1 -p 5432
/tmp/gnumed/gm-restore_2016-03-12_23-58-05/backup-gnumed_v20-GNUmed_Team-hermes-2016-03-07-21-15-06.dir/
pg_restore: verbinde mit der Datenbank zur Wiederherstellung
pg_restore: entferne DATABASE gnumed_v20
pg_restore: erstelle DATABASE „gnumed_v20“
pg_restore: verbinde mit neuer Datenbank „gnumed_v20“
pg_restore: verbinde mit Datenbank „gnumed_v20“ als Benutzer
„postgres“
pg_restore: erstelle SCHEMA „au“
pg_restore: erstelle SCHEMA „audit“
pg_restore: erstelle SCHEMA „bill“
pg_restore: erstelle COMMENT „SCHEMA bill“
pg_restore: erstelle SCHEMA „blobs“
pg_restore: erstelle SCHEMA „cfg“
pg_restore: erstelle COMMENT „SCHEMA cfg“
pg_restore: erstelle SCHEMA „clin“
pg_restore: erstelle SCHEMA „de_de“
pg_restore: erstelle SCHEMA „dem“
pg_restore: erstelle SCHEMA „gm“
pg_restore: erstelle SCHEMA „i18n“
pg_restore: erstelle SCHEMA „pgtrgm“
pg_restore: erstelle SCHEMA „public“
pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
pg_restore: [Archivierer (DB)] Fehler in
Inhaltsverzeichniseintrag 8; 2615 2200 SCHEMA public postgres
pg_restore: [Archivierer (DB)] could not execute query: FEHLER:
Schema „public“ existiert bereits
Die Anweisung war: CREATE SCHEMA public;

I am sure I am doing something wrong, but what ?

Did it actually fail or did it just throw an error?
In other words did the restore continue past the error?

My other post is more detailed in why (and how) this should (could) be
improved. As to this point it doesn't "actually fail" insofar as there is
no actual harm done as the schema cloned from template1 is empty and so the
failure during the attempt to create it - by definition in an empty state -
is insubstantial when the concern is whether the source and result
databases have the same schema. But it is substantial insofar as it
reports an error that doesn't have to happen and that is "a failure" if one
chooses, quite rightly, to "exit-on-error"

David J.

On Sun, Mar 13, 2016 at 12:37:02AM +0100, Karsten Hilbert wrote:

pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8; 2615 2200 SCHEMA public postgres
pg_restore: [Archivierer (DB)] could not execute query: FEHLER: Schema „public“ existiert bereits
Die Anweisung war: CREATE SCHEMA public;

I am sure I am doing something wrong, but what ?

Did it actually fail or did it just throw an error?
In other words did the restore continue past the error?

Good question. I'll remove the --exit-on-error and retry :-)

It actually went through with the last line saying

WARNING: 1 error during restor was ignored

pg_restore does not return exit code 0 anymore, however, and
offhand I can't find documentation as to whether pg_restore
returns different error codes between success and
success-with-ignored-errors. It does not seem to return 0
when it "ignores" errors.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Mar 12, 2016 at 04:53:20PM -0700, David G. Johnston wrote:

The docs could probably use improvement here - though I am inferring
behavior from description and not code.

The create option tells restore that it is pointless to use conditions or
actively drop objects since the newly created database is expected to be
empty. The --clean option will cause pg_restore to drop the database if it
exists but only the database. The --if-exists option would seem to be
extraneous.

The clean option with create seems to be misleading since the advice later
in the document is to ensure the created database is empty by using
template0 - which you cannot specify directly within pg_restore and so
createdb or an equivalent command should be used to stage up the empty
database before performing a simple (no create or clean) restore.

I'm not certain why the create database command constructed when specifying
--create isn't just defaulted to template0...and for completeness a
--template option added for user template specification

The thing is, even when defaulting --create to template0 it
would contain a copy of the PUBLIC schema from template0,
which is then attempted to be restored from the dump, if
included.

As Adrian pointed out, that's not a problem as the restore
continues anyway (which I was able to confirm).

However, pg_restore.c seems to suggest

420 /* done, print a summary of ignored errors */
421 if (AH->n_errors)
422 fprintf(stderr, _("WARNING: errors ignored on restore: %d\n"),
423 AH->n_errors);
424
425 /* AH may be freed in CloseArchive? */
426 exit_code = AH->n_errors ? 1 : 0;
427
428 CloseArchive(AH);

that the exit code is set to 1 if any errors ensued (but were
ignored). Thusly the restore may have succeeded semantically
but is still flagged as (technically) failed. That wouldn't
be a problem if the condition

really-fully-failed

could be differentiated from

technical-failure-but-ignored-and-semantically-succeeded

at the exit code level since the latter outcome can be
expected to happen under the circumstances described above.

Am I thinking the wrong way ?

The reason being, of course, that I want to check the exit
code in a pg_restore wrapper script.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Saturday, March 12, 2016, Karsten Hilbert <Karsten.Hilbert@gmx.net>
wrote:

On Sat, Mar 12, 2016 at 04:53:20PM -0700, David G. Johnston wrote:

The docs could probably use improvement here - though I am inferring
behavior from description and not code.

The create option tells restore that it is pointless to use conditions or
actively drop objects since the newly created database is expected to be
empty. The --clean option will cause pg_restore to drop the database if

it

exists but only the database. The --if-exists option would seem to be
extraneous.

The clean option with create seems to be misleading since the advice

later

in the document is to ensure the created database is empty by using
template0 - which you cannot specify directly within pg_restore and so
createdb or an equivalent command should be used to stage up the empty
database before performing a simple (no create or clean) restore.

I'm not certain why the create database command constructed when

specifying

--create isn't just defaulted to template0...and for completeness a
--template option added for user template specification

The thing is, even when defaulting --create to template0 it
would contain a copy of the PUBLIC schema from template0,
which is then attempted to be restored from the dump, if
included.

As Adrian pointed out, that's not a problem as the restore
continues anyway (which I was able to confirm).

However, pg_restore.c seems to suggest

420 /* done, print a summary of ignored errors */
421 if (AH->n_errors)
422 fprintf(stderr, _("WARNING: errors ignored on restore: %d\n"),
423 AH->n_errors);
424
425 /* AH may be freed in CloseArchive? */
426 exit_code = AH->n_errors ? 1 : 0;
427
428 CloseArchive(AH);

that the exit code is set to 1 if any errors ensued (but were
ignored). Thusly the restore may have succeeded semantically
but is still flagged as (technically) failed. That wouldn't
be a problem if the condition

really-fully-failed

could be differentiated from

technical-failure-but-ignored-and-semantically-succeeded

at the exit code level since the latter outcome can be
expected to happen under the circumstances described above.

Am I thinking the wrong way ?

The reason being, of course, that I want to check the exit
code in a pg_restore wrapper script.

I mistakenly thought public only came from template1...I wouldn't be
opposed to that change. This all seems awfully familiar too...

You probably should just drop the existing database and use --create by
itself.

You can even use the dropdb command to avoid SQL in your script.

David J,

On Sat, Mar 12, 2016 at 05:31:38PM -0700, David G. Johnston wrote:

The reason being, of course, that I want to check the exit
code in a pg_restore wrapper script.

I mistakenly thought public only came from template1...I wouldn't be
opposed to that change. This all seems awfully familiar too...

You probably should just drop the existing database and use --create by
itself.

You can even use the dropdb command to avoid SQL in your script.

I already do something similar: the wrapper fails if the
target db exists before a restore is even attempted. The
restore itself now uses --create and works as expected. The
only thing left ATM is that I cannot distinguish
success-with-or-without-ignored-errors from real failure.

I _can_ partly work around that by attempting to connect to
the target and checking the md5 sum of the schema definition
against a known hash. That won't help with detecting whether
pg_restore thought that _data_ was successfully restored ...

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Mar 12, 2016 at 5:31 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

You probably should just drop the existing database and use --create by
itself.

You can even use the dropdb command to avoid SQL in your script.

​This seems like it is the main problem:

# dropdb postgres
# pg_restore --create <a dump with the postgres database>

No problems​

​ # pg_restore --clean --create <a dump with the postgres database>

​>public schema already exists

So both --clean and --create are attempting to create the database.

So in the example you can either use:

# pg_restore --clean -d postgres
or
# pg_restore --create -d template1

But with the later you have to "dropdb" first - if the target database
already exists

With the former you have to "createdb" first - if the target database
doesn't already exist.

I agree the that exit code situation should be enhanced as well.

David J.

On Sat, Mar 12, 2016 at 5:43 PM, Karsten Hilbert <Karsten.Hilbert@gmx.net>
wrote:

On Sat, Mar 12, 2016 at 05:31:38PM -0700, David G. Johnston wrote:

The reason being, of course, that I want to check the exit
code in a pg_restore wrapper script.

I mistakenly thought public only came from template1...I wouldn't be
opposed to that change. This all seems awfully familiar too...

You probably should just drop the existing database and use --create by
itself.

You can even use the dropdb command to avoid SQL in your script.

I already do something similar: the wrapper fails if the
target db exists before a restore is even attempted. The
restore itself now uses --create and works as expected. The
only thing left ATM is that I cannot distinguish
success-with-or-without-ignored-errors from real failure.

I _can_ partly work around that by attempting to connect to
the target and checking the md5 sum of the schema definition
against a known hash. That won't help with detecting whether
pg_restore thought that _data_ was successfully restored ...

​I'd operate under the premise that all warnings and errors are fatal
(i.e., keep --exit-on-error) until you cannot for some very specific
reason. I'd decide how to proceed at that point. For instance pg_restore
does provide an ignored error count at the end - you could scan the log for
expected errors, count them, and compare to that value and fail if the
count differs.
​ But this particular warning should be easy to work around and you
shouldn't expect any others that would be considered non-critical.

David J.

On Sat, Mar 12, 2016 at 05:49:56PM -0700, David G. Johnston wrote:

I'd operate under the premise that all warnings and errors are fatal
(i.e., keep --exit-on-error) until you cannot for some very specific
reason.

--exit-on-error will exit on _any_ perceived error,
regardless of whether it could be ignored and the restore
still succeed later on. Hence I cannot keep that option in
use in order to implement the below.

The unfortunate thing is that *any* restore will "fail"
because the schema PUBLIC is copied from the template and
that alone will produce an (ignorable) error...

I'd decide how to proceed at that point. For instance pg_restore
does provide an ignored error count at the end - you could scan the log for
expected errors, count them, and compare to that value and fail if the
count differs.

That is a good idea.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Saturday, March 12, 2016, Karsten Hilbert <Karsten.Hilbert@gmx.net>
wrote:

On Sat, Mar 12, 2016 at 05:49:56PM -0700, David G. Johnston wrote:

I'd operate under the premise that all warnings and errors are fatal
(i.e., keep --exit-on-error) until you cannot for some very specific
reason.

--exit-on-error will exit on _any_ perceived error,
regardless of whether it could be ignored and the restore
still succeed later on. Hence I cannot keep that option in
use in order to implement the below.

The unfortunate thing is that *any* restore will "fail"
because the schema PUBLIC is copied from the template and
that alone will produce an (ignorable) error...

So you make things so that error doesn't occur, the work-arounds are
reasonably simple.

Using either clean or create alone succeeded without the public schema
error. It is only when you use both will it fail. But both those
individual options have pre-reqs you need to ensure are met before calling
pg_restore.

David J.

Hi Karsten..

On Sun, Mar 13, 2016 at 12:09 AM, Karsten Hilbert
<Karsten.Hilbert@gmx.net> wrote:

I am trying to pg_restore from a directory dump.
However, despite using

--clean
--create
--if-exists

I am getting an error because schema PUBLIC already exists.

....snip, snip....

Have you tried the classic combo pg_restore -l > toc.dat,
your_favorite_editor toc.dat pg_restore -L toc.dat?

I've had great success with that in the past, even splitting the TOC
in several chunks to be able to make adjustements between them, but
I've never used the directory format for ( serious, I've tried all
when learning ) backups.

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general