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 (
...
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).
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
