On 09/19/2016 01:06 PM, kbran...@pwhome.com wrote:
--- adrian.kla...@aklaver.com wrote:

From: Adrian Klaver <adrian.kla...@aklaver.com>
To: kbran...@pwhome.com, pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_restore question
Date: Mon, 19 Sep 2016 12:46:24 -0700

On 09/19/2016 11:46 AM, kbran...@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 
    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



     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

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 .

It still shows all the stuff below (from the original email) and a lot more 
ending with:

    WARNING: errors ignored on restore: 18

Meant to add to previous post. If any of the errors are of the 'objects does not exist' variety you can get rid of then using:


Use conditional commands (i.e. add an IF EXISTS clause) when cleaning database objects. This option is not valid unless --clean is also specified.

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.



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,
    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;
     "$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.



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

Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:

Reply via email to