Re: pg_restore fails

On Saturday, March 12, 2016, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
wrote:

> On Sat, Mar 12, 2016 at 04:53:20PM -0700, David G. Johnston wrote:
>
> > 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
>
> The thing is, even when defaulting --create to template0 it
> would contain a copy of the PUBLIC schema from template0,
> which is then attempted to be restored from the dump, if
> included.
>
> As Adrian pointed out, that's not a problem as the restore
> continues anyway (which I was able to confirm).
>
> However, pg_restore.c seems to suggest
>
> 420 /* done, print a summary of ignored errors */
> 421 if (AH->n_errors)
> 422 fprintf(stderr, _("WARNING: errors ignored on restore: %d\n"),
> 423 AH->n_errors);
> 424
> 425 /* AH may be freed in CloseArchive? */
> 426 exit_code = AH->n_errors ? 1 : 0;
> 427
> 428 CloseArchive(AH);
>
> that the exit code is set to 1 if any errors ensued (but were
> ignored). Thusly the restore may have succeeded semantically
> but is still flagged as (technically) failed. That wouldn't
> be a problem if the condition
>
> really-fully-failed
>
> could be differentiated from
>
> technical-failure-but-ignored-and-semantically-succeeded
>
> at the exit code level since the latter outcome can be
> expected to happen under the circumstances described above.
>
> Am I thinking the wrong way ?
>
> The reason being, of course, that I want to check the exit
> code in a pg_restore wrapper script.
>
>
I mistakenly thought public only came from template1...I wouldn't be
opposed to that change. This all seems awfully familiar too...

You probably should just drop the existing database and use --create by
itself.

You can even use the dropdb command to avoid SQL in your script.

David J,