pg_restore fails

From: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: pg_restore fails
Date: 2016-03-12 23:09:19
Message-ID: 20160312230919.GA11080@hermes.hilbert.loc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 ?

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Karsten Hilbert 2016-03-12 23:15:01 Re: pg_restore fails
Previous Message Tom Lane 2016-03-12 22:58:11 Re: [GENERAL] OS X 10.11.3, psql, bus error 10, 9.5.1