Skip site navigation (1) Skip section navigation (2)

Re: COPY errors when trying to convert from 7.4.19 to 8.3.1

From: Geoffrey <lists(at)serioustechnology(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: COPY errors when trying to convert from 7.4.19 to 8.3.1
Date: 2008-05-01 19:54:25
Message-ID: 481A1FF1.6080700@serioustechnology.com (view raw or flat)
Thread:
Lists: pgsql-admin
Tom Lane wrote:
> Geoffrey <lists(at)serioustechnology(dot)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

In response to

pgsql-admin by date

Next:From: GeoffreyDate: 2008-05-01 19:57:45
Subject: Re: COPY errors when trying to convert from 7.4.19 to 8.3.1
Previous:From: Shane AmblerDate: 2008-05-01 18:21:25
Subject: Re: How to configure Postgre 8.2 to put the data base in a especific directory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group