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

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