From: Geoffrey <lists@serioustechnology.com>
To: Shane Ambler <pgsql@Sheeky.Biz>
Cc: pgsql-admin@postgresql.org
Subject: Re: COPY errors when trying to convert from 7.4.19 to 8.3.1
Date: Thu, 01 May 2008 15:57:45 -0400
Message-ID: <481A20B9.3000403@serioustechnology.com> (raw)
In-Reply-To: <481A09B8.2070604@Sheeky.Biz>
References: <4819EDF9.2080000@serioustechnology.com>
<481A09B8.2070604@Sheeky.Biz>
Shane Ambler wrote:
> Geoffrey wrote:
>> We are trying to test our process for upgrading our database from
>> 7.4.19 to 8.3.1. We are following the instructions for dumping the
>> 7.4.19 database from:
>>
>> http://www.postgresql.org/docs/8.3/static/install-upgrading.html
>>
>> ie:
>>
>> /usr/local/pg83/bin/pg_dumpall -h master -p 5434 |gzip > dump.gz
>>
>> Create a new 8.3.1 cluster as follows:
>>
>> /usr/local/pg83/bin/initdb -D /data/pgsql/master83
>>
>> Load the data into the new cluster:
>>
>> gunzip < dump.gz |/usr/local/pg83/bin/psql -d postgres
>>
>> We know we have some old data in the database that does not meet
>> current criteria, thus we are seeing errors such as:
>
> Are you modifying the dump before the import with the new criteria
> adjustments? Or is this criteria from changes between 7.4 and 8.3?
We aren't making any changes to the dump data. It's likely caused by
data that was quite old in the 7.4 database and new constraints were
added since that data was created.
>
>> ERROR: insert or update on table "aaccess" violates foreign key
>> constraint "$1"
>> DETAIL: Key (code)=(CFSAVAUS000) is not present in table "arates".
>>
>> What baffles us is, that although we get these errors, the data is
>> still being inserted in to the table, in this case, the aaccess table.
>>
>
>>
>>
>> What are we missing?
>>
>
> I am guessing here - I believe that the message is misleading.
>
> If you look at the dumpall output you may notice the order in which some
> things are done.
Yeah, as noted in my response to Tom, that is what I'm going to do.
>
> ...
> ...
> create table....
> copy from stdin....
> alter table add constraint....
> create index....
> ...
> ...
>
> My guess is that the error is coming from the add constraint which is
> invoked after the data has been inserted.
>
> Check your table defs in 8.3 and see if the fk constraints are in place.
Will do.
>
> You would have two choices - clean up the data in 7.4 before the export
> or clean up after the import and before the fk constraints are added.
The latter is likely the plan. The primary concern is that we don't
lose any data.
--
Until later, Geoffrey
Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: lists@serioustechnology.com, pgsql@Sheeky.Biz
Subject: Re: COPY errors when trying to convert from 7.4.19 to 8.3.1
In-Reply-To: <481A20B9.3000403@serioustechnology.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox