Message-ID: <481A09B8.2070604@Sheeky.Biz>
Date: Fri, 02 May 2008 03:49:36 +0930
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.12 (Macintosh/20080213)
MIME-Version: 1.0
To: Geoffrey <lists@serioustechnology.com>
CC: pgsql-admin@postgresql.org
Subject: Re: COPY errors when trying to convert from 7.4.19 to 8.3.1
References: <4819EDF9.2080000@serioustechnology.com>
In-Reply-To: <4819EDF9.2080000@serioustechnology.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?

> 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.

...
...
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.

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.


-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz