Message-ID: <481A1FF1.6080700@serioustechnology.com>
Date: Thu, 01 May 2008 15:54:25 -0400
From: Geoffrey <lists@serioustechnology.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080226)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
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>
	<15880.1209660291@sss.pgh.pa.us>
In-Reply-To: <15880.1209660291@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> Geoffrey <lists@serioustechnology.com> writes:
>> 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:
> 
>> 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.
> 
> Well, the insert has already happened: the error is actually coming out
> when the dump script tries to do ALTER TABLE ADD CONSTRAINT FOREIGN KEY.
> (The message is perhaps a bit misleading if you don't notice which
> command caused it.)
> 
> What this looks like to me is a misordering of the dump operations such
> that we're trying to do the ALTER on aaccess after loading its data,
> but before loading arates's data.  In theory 8.3's pg_dump should
> avoid such mistakes, but it's possible you've got a case that fools it.
> 
> Anyway, the first thing you should do to investigate is to check exactly
> what's the order of operations in the script and which command is
> causing the failure.  If the data involved is too large to make it
> convenient to eyeball the dump script with an editor, you might try
> running the dump script with log_statement = all so you can see just the
> SQL commands in the postmaster log.

It's manageable, so I'll take an eye at it.  As always, thanks for your 
insights Tom.

-- 
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
  - Benjamin Franklin