Thread: pg_restore question
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 (
...
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).
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
> --- 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
On 09/19/2016 01:06 PM, kbrannen@pwhome.com wrote: >> --- 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 . 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@aklaver.com > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 09/19/2016 01:06 PM, kbrannen@pwhome.com wrote: >> --- 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 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: --if-exists 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. > > 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 > > > -- Adrian Klaver adrian.klaver@aklaver.com
>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: >>>> >>>> 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 ( >>> >>> >>> 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 > >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: > >--if-exists > > Use conditional commands (i.e. add an IF EXISTS clause) when >cleaning database objects. This option is not valid unless --clean is >also specified. By doing it 1 command at a time with lots of compares, I think I'm starting to understand what's going on. Documenting this for those who are curious and to help others in the future... Back to the part where I have the data spread over 2 schemas... Note that I'm trying to backup only 1 at a time. The reason for doing this is because the important data is in the public schema, while the other schema is for logging data and will be 100's of times bigger (we do back this up but not as often because losing a little is not fatal). Anyway, when I change the public schema to a new name, the tables in the logging schema that use types from public automatically change, e.g. from public.call_types to savepublic.call_types. That also means that if I were do "drop schema public cascade", then the logging table definitions gets changed as the column is dropped. Yikes! (Sadly, I should have realized this before but didn't.) But that's why I'm getting the errors, because when I try to restore the public schema, our custom types do already exist. To your suggestion of added "--if-exists --clean", that does remove 4 errors, leaving me with 14 that are true errors. My take away from this is that if we have schemas that are dependent on each other, we can't backup/restore just 1. That means we must break the dependency or else back them both up all the time (not desirable to do this frequently because of size). Sigh... I'll go back and look at what it would take to break the dependencies, but any other suggestions would be welcome. Thanks for the advice and pointers Adrian! Kevin
On 09/19/2016 01:55 PM, kbrannen@pwhome.com wrote:
>> 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:
>>>>>
>>>>> 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 (
>>>>
>>>>
>>>> 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
>>
>> 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:
>>
>> --if-exists
>>
>> Use conditional commands (i.e. add an IF EXISTS clause) when
>> cleaning database objects. This option is not valid unless --clean is
>> also specified.
>
> By doing it 1 command at a time with lots of compares, I think I'm starting to
> understand what's going on.
>
> Documenting this for those who are curious and to help others in the future...
>
> Back to the part where I have the data spread over 2 schemas... Note that I'm
> trying to backup only 1 at a time. The reason for doing this is because the
> important data is in the public schema, while the other schema is for logging
> data and will be 100's of times bigger (we do back this up but not as often
> because losing a little is not fatal).
>
> Anyway, when I change the public schema to a new name, the tables in the
> logging schema that use types from public automatically change, e.g. from
> public.call_types to savepublic.call_types. That also means that if I were do
> "drop schema public cascade", then the logging table definitions gets changed as the
> column is dropped. Yikes! (Sadly, I should have realized this before but didn't.)
>
> But that's why I'm getting the errors, because when I try to restore the public
> schema, our custom types do already exist.
>
> To your suggestion of added "--if-exists --clean", that does remove 4 errors,
> leaving me with 14 that are true errors.
>
> My take away from this is that if we have schemas that are dependent on each
> other, we can't backup/restore just 1. That means we must break the dependency
> or else back them both up all the time (not desirable to do this frequently
> because of size). Sigh...
>
> I'll go back and look at what it would take to break the dependencies, but any
> other suggestions would be welcome.
One possible solution is to:
1) Do the pg_dump in custom format.
2) Do pg_restore with --schema-only to a file. Open file and change the
search_path to include the logging schema.
3) Pass the schema only plain text file into psql to get the schema
objects created.
4) pg_restore using:
-a
--data-only
Restore only the data, not the schema (data definitions). Table
data, large objects, and sequence values are restored, if present in the
archive.
This option is similar to, but for historical reasons not identical
to, specifying --section=data.
>
> Thanks for the advice and pointers Adrian!
>
> Kevin
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com