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: (view raw or whole 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:
>> 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-2015 The PostgreSQL Global Development Group