Re: pg_restore fails

On Saturday, March 12, 2016, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)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.