Re: pg_restore question

> --- adrian(dot)klaver(at)aklaver(dot)com wrote:
>
> From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> To: kbrannen(at)pwhome(dot)com, pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] pg_restore question
> Date: Mon, 19 Sep 2016 12:46:24 -0700
>
> On 09/19/2016 11:46 AM, kbrannen(at)pwhome(dot)com wrote:
> > I think I'm going to need some help in understanding a couple of restore issues.
> > This is for Pg 9.5.1.
> >
> > It seems that if I create a dump using
> > pg_dump --clean --create --format=p --dbname=nms --schema=public > dump.sql
> > then the restore (after "drop schema public cascade") with "psql nms < dump.sql"
> > will create the schema and it loads correctly.
> >
> > But if I dump using:
> > pg_dump --format=d -j4 --file=/tmp/exp # (an empty dir)
> > then the restore with with the schema still there and relying on --clean to help:
> > pg_restore --dbname=nms --clean --create --schema=public .
> > will fail with:
> >
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 2398; 1247 147632 TYPE app_kinds nmsroot
> > pg_restore: [archiver (db)] could not execute query: ERROR: type "app_kinds" already exists
> > Command was: CREATE TYPE app_kinds AS ENUM (
>
> First the --create is a no-op as it only applies to the database as a
> whole:
>
> https://www.postgresql.org/docs/9.5/static/app-pgrestore.html
>
> --create
>
> Create the database before restoring into it. If --clean is also
> specified, drop and recreate the target database before connecting to it.

OK, we'll chalk that one up to "reading comprehension failure" on my part. :)
I'll drop that option especially because it's easy to work around.

>
> Second, did it actually fail or did it just throw the error and keep on
> going?

So changes my process to:

# create backup just in case
echo "alter schema public rename to save; create schema public;" | psql
pg_restore --dbname=nms --schema=public -j3 .

It still shows all the stuff below (from the original email) and a lot more ending with:

WARNING: errors ignored on restore: 18

I'm sure you can see how that might alarm me. :)

The more I read about search_path and schemas, the more I'm thinking the issue is related to that.
I just haven't figured out how yet nor what to do about it.

Kevin

> > ...
> >
> > But if I drop the schema first AND create a blank schema (leaving of the create
> > gives me yet a 3rd set of errors), then I get a 2nd set of errors:
> >
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 266; 1259 148562 VIEW busy_log_view nmsroot
> > pg_restore: [archiver (db)] could not execute query: ERROR: column busy_log.call_type does not exist
> > LINE 12: WHEN (busy_log.call_type = 'U'::call_types) THEN...
> > ^
> > Command was: CREATE VIEW busy_log_view AS
> > SELECT busy_log.busy_log_pk,
> > busy_log.time_sent,
> > busy_log.source_id,
> > busy_log.targ...
> > pg_restore: [archiver (db)] could not execute query: ERROR: relation "busy_log_view" does not exist
> > Command was: ALTER TABLE busy_log_view OWNER TO nmsroot;
> > ...
> >
> > Here, it seems like the view is getting created too early, and that's with me
> > leaving the -j flag off, which I want to add.
> >
> > What parts of the docs am I not understanding or what flags am I missing?
> >
> > The 2nd attempt and 2nd set of errors is the closest to working and I'm starting
> > to think that this is a "search_path" issue. There is a 2nd schema (called
> > "logging") which has log tables while the call types are in public (and the
> > type is used in both schemas). This works normally because the search_path
> > includes both schemas. Before the dump I see:
> >
> > nms=# show search_path;
> > search_path
> > --------------------------
> > "$user", public, logging
> > (1 row)
> >
> > But in the "format=p" file, I see:
> >
> > SET search_path = public, pg_catalog;
> >
> > Is it possible the database's search_path isn't being used during the restore
> > but the incorrect one in the dump file is?
> > Note, the database was never dropped (just the schema), so its search path was
> > (should be) correct.
> >
> > I did find a discussion about backup/restore and search_path from back in 2006
> > that makes me suspect the search_path even more, but if that's it, I don't
> > understand why the backup would put an invalid search_path in the backup file
> > nor what I might be able to do about that.
> >
> > Thanks,
> > Kevin
> >
> > ---
> >
> > Don't think this matters, but to be complete, this is on Centos 6.7. Pg was
> > compiled from source since the default Centos package would be version 8.4.20 (very old).
> >
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com