> --- [email protected] wrote:
>
> From: Adrian Klaver <[email protected]>
> To: [email protected], [email protected]
> Subject: Re: [GENERAL] pg_restore question
> Date: Mon, 19 Sep 2016 12:46:24 -0700
>
> On 09/19/2016 11:46 AM, [email protected] 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
> [email protected]
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
