Thread: Data migration problems with Upgrade from Version 6.5.2 to 7.1.2

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

"Tom.Bakken" <Tom.Bakken@tx.usda.gov> writes:

> This is my first posting to the list, so I hope this is the right place
> for this question.
>
> 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:
> 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

> 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:
>
> How should I specify my boolean fields?  I have them set up as follows:
>
> column BOOLEAN DEFAULT 0

If you change "DEFAULT 0" to "DEFAULT 'f'" it should work
OK--certainly works in my 7.1.2 installation.  You can whip up a quick
Perl or sed script to do the job.

Good luck; I hope this is the only problem you hit in your
migration...

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

On Wed, 27 Jun 2001, Tom.Bakken wrote:

>
> How should I specify my boolean fields?  I have them set up as follows:
>
> column BOOLEAN DEFAULT 0
It must be DEFAULT 'f'.

>
> 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
Its not acceptable to postgres 7.x , as you found 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>
>

> > How should I specify my boolean fields?  I have them set up as follows:
> >
> > column BOOLEAN DEFAULT 0
> It must be DEFAULT 'f'.

Must be? DEFAULT False works fine for me.

Greg