COPY errors when trying to convert from 7.4.19 to 8.3.1

From: Geoffrey <lists(at)serioustechnology(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: COPY errors when trying to convert from 7.4.19 to 8.3.1
Date: 2008-05-01 16:21:13
Message-ID: 4819EDF9.2080000@serioustechnology.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

Our expectation is that when COPY encounters an error such as this, it
would stop and not continue to insert any more data into this table.
Researching this indicates it is not true. We have the same number of
records in the 7.4.19 table as we do in our 8.3.1 table.

We are basing our 'expectation' on the documentation of COPY which reads:

COPY stops operation at the first error. This should not lead to
problems in the event of a COPY TO, but the target table will already
have received earlier rows in a COPY FROM. These rows will not be
visible or accessible, but they still occupy disk space. This might
amount to a considerable amount of wasted disk space if the failure
happened well into a large copy operation. You might wish to invoke
VACUUM to recover the wasted space.

What are we missing?

--
Until later, Geoffrey

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2008-05-01 16:44:51 Re: COPY errors when trying to convert from 7.4.19 to 8.3.1
Previous Message Glyn Astill 2008-05-01 16:20:53 Re: Urgent :: PostgreSQL on Vista Home Premium