Data migration problems with Upgrade from Version 6.5.2 to 7.1.2 - Mailing list pgsql-general
From | Tom.Bakken |
---|---|
Subject | Data migration problems with Upgrade from Version 6.5.2 to 7.1.2 |
Date | |
Msg-id | 3B3A0F10.95B08C81@tx.usda.gov Whole thread Raw |
Responses |
Re: Data migration problems with Upgrade from Version
6.5.2 to 7.1.2
(Alex Pilosov <alex@pilosoft.com>)
|
List | 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.bakken@tx.usda.gov
pgsql-general by date: