Data migration problems with Upgrade from Version 6.5.2 to 7.1.2

From: "Tom(dot)Bakken" <Tom(dot)Bakken(at)tx(dot)usda(dot)gov>
To: Postgres general <pgsql-general(at)postgresql(dot)org>
Subject: Data migration problems with Upgrade from Version 6.5.2 to 7.1.2
Date: 2001-06-27 16:51:28
Message-ID: 3B3A0F10.95B08C81@tx.usda.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

This is my first posting to the list, so I hope this is the right place
for this question.

I'm currently running postgres 6.5.2 on a Red Hat LINUX 6.2 web server.
I've installed postgres 7.1.2 on a Red Hat LINUX 7.0 platform. Reading
the README.rpm-dist has been confusing to say the least. I have quite a
50 or 60 tables, some containing several thousand records on the older
system. I would like to import the data into the newer machine without
too much trouble. Here follows actions I have taken to move my data to
the new platform:

I created a dump of all data on the 6.5.2 machine: pg_dumpall > db.out
and copied it over to the new system.

I then attempted to import the data: psql -d template1 -f
/home/postgreSQL/infosystems/dump/db.out

The result was a few tables came through okay but a lot of tables with
no records. I redirected the standard error and will include a few
snippets of the complaints the psql utility encountered:

You are now connected to database template1.
SELECT
DELETE 14
DROP
You are now connected to database template1 as user luser.
CREATE DATABASE
You are now connected to database infosystems as user luser.
You are now connected as new user s4900tjb.
CREATE
CREATE
CREATE
nextval
---------
1100
(1 row)
.
.
.

Quite a few row like this appear. Then comes the following:

CHANGE
CHANGE
CHANGE
CHANGE
psql:/home/postgreSQL/infosystems/dump/db.out:499: ERROR: Attribute
'extra_pos'
is of type 'bool' but default expression is of type 'int4'
You will need to rewrite or cast the expression
psql:/home/postgreSQL/infosystems/dump/db.out:500: ERROR: Relation
'positions'
does not exist
psql:/home/postgreSQL/infosystems/dump/db.out:501: ERROR: Relation
'positions'
does not exist

My question here is, how to resolve the BOOLEAN/INTEGER conflict. It
doesn't appear to be a problem with the earlier version of postgres. I
believe that's what causes most of the error messages that follow:

psql:/home/postgreSQL/infosystems/dump/db.out:24715: invalid command \N
psql:/home/postgreSQL/infosystems/dump/db.out:24716: invalid command \N
psql:/home/postgreSQL/infosystems/dump/db.out:24717: invalid command \N
psql:/home/postgreSQL/infosystems/dump/db.out:25332: invalid command \.
psql:/home/postgreSQL/infosystems/dump/db.out:25333: ERROR: parser:
parse error
at or near "dan"
psql:/home/postgreSQL/infosystems/dump/db.out:25386: invalid command \.
psql:/home/postgreSQL/infosystems/dump/db.out:25387: ERROR: parser:
parse error
at or near "0"
psql:/home/postgreSQL/infosystems/dump/db.out:25708: invalid command \.
psql:/home/postgreSQL/infosystems/dump/db.out:25709: ERROR: parser:
parse error
at or near "2192"

How should I specify my boolean fields? I have them set up as follows:

column BOOLEAN DEFAULT 0

This setup works well with Access97/odbc boolean fields such as check
boxes etc.

This format is acceptable to postgres 6.5.2 and data copies in and out
without errors.

If anybody's already been down this road, I'd appreciate the benefit of
your experiences.
--
Tom Bakken
Information Resource Manager
USDA, Rural Development
101 South Main, Suite 102
Temple, TX 76501

Phone: 254-742-9726 Fax: 254-742-9709
Email: tom(dot)bakken(at)tx(dot)usda(dot)gov

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2001-06-27 16:53:19 Re: DBD::Pg - BYTEA - fails for range outside chr(0)-chr(127)
Previous Message Thalis A. Kalfigopoulos 2001-06-27 16:47:44 SHMMAX value