Re: pg_restore question

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