Re: pg_restore fails

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(dot)klaver(at)aklaver(dot)com