Re: pg_restore question

On 09/19/2016 01:06 PM, kbrannen(at)pwhome(dot)com wrote:
>> --- adrian(dot)klaver(at)aklaver(dot)com wrote:
>>
>> From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
>> To: kbrannen(at)pwhome(dot)com, pgsql-general(at)postgresql(dot)org
>> Subject: Re: [GENERAL] pg_restore question
>> Date: Mon, 19 Sep 2016 12:46:24 -0700
>>
>> 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.
>
> 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 .

If you want to see what is going on you can change the above to:

pg_restore --schema=public --schema-only -f text_restore.sql

This will output the restore to plain text form in the file
text_restore.sql. I added the --schema-only to filter out the data and
make things a little easier to read. The -j option is a no-op when
outputting to a file so I left it off.

Then you can compare the contents of the file to your original text dump.

>
> It still shows all the stuff below (from the original email) and a lot more ending with:
>
> WARNING: errors ignored on restore: 18
>
> 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(dot)klaver(at)aklaver(dot)com
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com