From: Geoffrey <lists@serioustechnology.com>
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
Date: Thu, 01 May 2008 15:54:25 -0400
Message-ID: <481A1FF1.6080700@serioustechnology.com> (raw)
In-Reply-To: <15880.1209660291@sss.pgh.pa.us>
References: <4819EDF9.2080000@serioustechnology.com>
<15880.1209660291@sss.pgh.pa.us>
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
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, tgl@sss.pgh.pa.us
Subject: Re: COPY errors when trying to convert from 7.4.19 to 8.3.1
In-Reply-To: <481A1FF1.6080700@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