From: Geoffrey <lists@serioustechnology.com>
To: pgsql-admin@postgresql.org
Subject: COPY errors when trying to convert from 7.4.19 to 8.3.1
Date: Thu, 01 May 2008 12:21:13 -0400
Message-ID: <4819EDF9.2080000@serioustechnology.com> (raw)
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
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
Subject: Re: COPY errors when trying to convert from 7.4.19 to 8.3.1
In-Reply-To: <4819EDF9.2080000@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