Thread: trouble migrating from 6.3.2 (IRIX) to 7.0.2 (Linux)
I'm trying to migrate from PostgreSQL 6.3.2 under IRIX to version 7.0.2 under RedHat 7.0. I've dumped my old database with "pg_dumpall -o > db.backup", but I'm having problems restoring this data to the new machine. I'm using "psql -d template1 < db.backup" to restore the data, but I'm getting parse errors from psql: > You are now connected to database template1. > SELECT > DELETE 0 > DROP > You are now connected to database template1 as user postgres. > CREATE DATABASE > You are now connected to database armhold as user postgres. > CREATE > DROP > You are now connected as new user armhold. > psql:/home/armhold/db.backup:13: ERROR: parser: parse error at or near "-" > psql:/home/armhold/db.backup:14: ERROR: Relation 'pfam' does not exist > psql:/home/armhold/db.backup:20: invalid command \. I get similar results if I dump the individual databases one by one. I'm running the stock Postgres install that comes with Redhat 7. I've applied all the available errata patches for Redhat. Should I expect this to work, moving from IRIX to Linux, and from Postgres 6.3.2 to 7.0.2 at the same time? Thanks for any help. -- George Armhold Rutgers University Bioinformatics Initiative
George Armhold <armhold@cs.rutgers.edu> writes:
>> psql:/home/armhold/db.backup:13: ERROR: parser: parse error at or near "-"
>> psql:/home/armhold/db.backup:14: ERROR: Relation 'pfam' does not exist
>> psql:/home/armhold/db.backup:20: invalid command \.
So, how about showing us the first 20 or so lines of db.backup? You
can't really expect us to guess much from this amount of info ...
regards, tom lane
Tom Lane wrote: > So, how about showing us the first 20 or so lines of db.backup? You > can't really expect us to guess much from this amount of info ... Fair enough question. select datdba into table tmp_pg_shadow from pg_database where datname = 't emplate1'; delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba; drop table tmp_pg_shadow; copy pg_shadow from stdin; nobody 60001 f t f t \N \N seabee 10481 t t f t \N \N yueyue 5535 t t f t \N \N hagerty 27458 t t f t \N \N armhold 29124 t t t t mysql \N seredin 60002 f t f t \N aynur 5869 t t f t \N \N \. \connect template1 postgres create database armhold; \connect armhold postgres CREATE TABLE pgdump_oid (dummy int4); COPY pgdump_oid WITH OIDS FROM stdin; 691497 0 \. DROP TABLE pgdump_oid; CREATE TABLE pfam (swiss_id varchar(16) NOT NULL, pfam_family varchar(32), comme nt varchar(80), sequence_data varchar(-5)); COPY pfam WITH OIDS FROM stdin; 536960 SWISS1 pfam_family1 comment data.... ABCDEFGHJ 536962 SWISS2 pfam_family1 comment data.... ABCDEFGHJ 536963 SWISS3 pfam_family1 comment data.... ABCDEFGHJ 536964 SWISS4 pfam_family1 comment data.... ABCDEFGHJ 536966 SWISS5 pfam_family2 comment data.... ABCDEFGHJ \. CREATE UNIQUE INDEX pfam_pkey on pfam using btree ( swiss_id varchar_ops ); \connect template1 armhold create database yueyue_test; \connect yueyue_test armhold CREATE TABLE pgdump_oid (dummy int4); COPY pgdump_oid WITH OIDS FROM stdin; 691529 0 \. Thanks. -- George Armhold Rutgers University Bioinformatics Initiative
George Armhold <armhold@cs.rutgers.edu> writes:
>> So, how about showing us the first 20 or so lines of db.backup? You
>> can't really expect us to guess much from this amount of info ...
> Fair enough question.
> CREATE TABLE pfam (swiss_id varchar(16) NOT NULL, pfam_family
> varchar(32), comment varchar(80), sequence_data varchar(-5));
Hrm. varchar(-5) is pretty obviously broken :-(. I don't want to
guess what sort of aberration prompted that output from pg_dump
--- 6.3.2 is before my time with Postgres. Try editing the dump
file to have a more reasonable value for the varchar max width,
and see how it goes...
regards, tom lane
Tom Lane wrote: > > CREATE TABLE pfam (swiss_id varchar(16) NOT NULL, pfam_family > > varchar(32), comment varchar(80), sequence_data varchar(-5)); > > Hrm. varchar(-5) is pretty obviously broken :-(. I don't want to > guess what sort of aberration prompted that output from pg_dump > --- 6.3.2 is before my time with Postgres. Try editing the dump > file to have a more reasonable value for the varchar max width, > and see how it goes... Hmm, I seem to be getting negative values for tables that were created without a maximum char length. Is (was) it legal postgres syntax to say something like create table foo (mytext varchar); without a maximum length constraint? psql (or perhaps the Perl interface) let me create such tables under 6.5.3 and they've worked fine, up until this pg_dump. Would it be safe to simply edit the dumped file, change the negative values to something reasonable and reload the DB? Thank you for your assistance. -- George Armhold Rutgers University Bioinformatics Initiative
George Armhold <armhold@cs.rutgers.edu> writes:
> Hmm, I seem to be getting negative values for tables that were created
> without a maximum char length. Is (was) it legal postgres syntax to
> say something like
> create table foo (mytext varchar);
> without a maximum length constraint?
It was and is, but it looks like 6.3.2's pg_dump did the wrong thing
with 'em. If you know that's how these fields were created, I'd say
that manually removing the (-5) from the table declarations is the
way to go.
regards, tom lane
Earlier in the week I was having problems moving from PG 6.3.2 under IRIX to version 7.0.2 under Linux. I noticed that pg_dump was creating bogus (negative value) entries for varchar fields that were initiallly created with no maximum length constraint. Tom Lane suggested the following: > It was and is, but it looks like 6.3.2's pg_dump did the wrong thing > with 'em. If you know that's how these fields were created, I'd say > that manually removing the (-5) from the table declarations is the > way to go. This fixed most of the problems. The one other thing I had to do was rename a field "offset" to "my_offset" throughout all my tables. Apparently this is a reserved word in the current version? I still have some more testing to do, but I seem to have things working now. Thanks to the group, and Tom Lane in particular.
"George Armhold" <armhold@cs.rutgers.edu> writes:
> The one other thing I had to do was rename
> a field "offset" to "my_offset" throughout all my tables. Apparently this
> is a reserved word in the current version?
Yes, see LIMIT/OFFSET options in SELECT. You could still use it with
double-quotes around the name, but changing the column name is probably
less annoying in the long run...
regards, tom lane