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