Re: pg_restore question - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: pg_restore question |
Date | |
Msg-id | f2665912-e1d9-5a6a-514b-91ca05d42247@aklaver.com Whole thread Raw |
In response to | pg_restore question ("" <kbrannen@pwhome.com>) |
List | pgsql-general |
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. Second, did it actually fail or did it just throw the error and keep on going? > ... > > 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
pgsql-general by date: