Re: pg_restore question

Поиск
Список
Период
Сортировка
От
Тема Re: pg_restore question
Дата
Msg-id 20160919130654.7601E1F7@m0087797.ppops.net
обсуждение исходный текст
Ответ на pg_restore question  ("" <kbrannen@pwhome.com>)
Ответы Re: pg_restore question  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: pg_restore question  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
> --- adrian.klaver@aklaver.com wrote:
>
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> To: kbrannen@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, 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.

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 .

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.klaver@aklaver.com



В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: pg_restore question
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: pg_restore question