Re: pg_restore fails

On Sat, Mar 12, 2016 at 4:32 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.