Re: pg_restore question - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: pg_restore question |
Date | |
Msg-id | 853df282-cdae-2280-fd5f-47d9a641bc15@aklaver.com Whole thread Raw |
In response to | Re: pg_restore question ("" <kbrannen@pwhome.com>) |
List | pgsql-general |
On 09/19/2016 01:06 PM, kbrannen@pwhome.com wrote: >> --- adrian.klaver@aklaver.com wrote: >> >> From: Adrian Klaver <adrian.klaver@aklaver.com> >> To: kbrannen@pwhome.com, pgsql-general@postgresql.org >> Subject: Re: [GENERAL] pg_restore question >> Date: Mon, 19 Sep 2016 12:46:24 -0700 >> >> On 09/19/2016 11:46 AM, kbrannen@pwhome.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 Meant to add to previous post. If any of the errors are of the 'objects does not exist' variety you can get rid of then using: --if-exists Use conditional commands (i.e. add an IF EXISTS clause) when cleaning database objects. This option is not valid unless --clean is also specified. > > 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@aklaver.com > > > -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: