Re: pg_restore question

On 09/19/2016 11:46 AM, kbrannen(at)pwhome(dot)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(dot)klaver(at)aklaver(dot)com