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