On 09/19/2016 01:06 PM, kbrannen@xxxxxxxxxx wrote:
--- adrian.klaver@xxxxxxxxxxx wrote:
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
To: kbrannen@xxxxxxxxxx, pgsql-general@xxxxxxxxxxxxxx
Subject: Re: pg_restore question
Date: Mon, 19 Sep 2016 12:46:24 -0700
On 09/19/2016 11:46 AM, kbrannen@xxxxxxxxxx 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.klaver@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
