Re: pg_restore fails - Mailing list 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