Thread: Importing Postgres 7.1.1 DBs into Postgres 7.3.2

A few weeks ago, I posted regarding some odd errors in postgresql.
Since then, a few things have been happening, which my coworker and I
have been discussing.

The data in the tables themselves are becoming corrupt.  Right now, I
have happy database. Last night, at midnight, information moved from
some tables and went into another table. Somewhere in there, the
destination tables (info from 3 or so tables is taken and put into the
final table), something became corrupt, and I could no longer access all
parts of my destination table.  I couldn't do a 'SELECT * FROM
my_table", nor could I pg_dump it, which means that my backups will fail
(since I don't back up the db files, but dump files).

We've had other issues with this before, but not like this.  My coworker
says, "Postgres doesn't have 'bad data'. It is either acceptable or
unacceptable. If it is unacceptable, you will see it".

Granted, I say, but then, how do you explain what is happening?

Anyway, on with the show.  So now I'm trying to import my databases into
the 'new' version of postgres I have running, but I'm having problems.
Going from 7.1.1 to 7.3.2 isn't easy, I guess, because I'm getting lots
of errors:
ERROR:  Column "date_entered" is of type date but default expression is
of type text

And lots of

invalid command \N
invalid command \N


In the end, I have nearly all my tables, but none of my tables have data
in them. Is there a conversion I must do beforehand?  I assumed
(incorrectly, evidently) that dumpfiles are all the same...

Any help would be swell.

Adam



--

Adam Bultman
Email: adam.bultman@iconideas.com

Phone: 616-990-4941
http://www.iconideas.com

Adam Bultman <adamb@iconideas.com> writes:
> A few weeks ago, I posted regarding some odd errors in postgresql.
> Since then, a few things have been happening, which my coworker and I
> have been discussing.

Have you run hardware diagnostics lately?  More and more often, when
we see reports like this, bad RAM or bad disk cables or something like
that proves to be the cause.  PG itself is not prone to gradually
corrupt data.

Once you've stabilized your hardware, a PG update would be a good thing
to think about; but it's not going to solve hardware problems.

As for identifying schema compatibility problems, two suggestions:
1. Make a schema-only dump first, and find out what you have to correct
by loading that.  Transfer the data separately.
2. Use 7.3's pg_dump against the older server to make the dump file.
It can take care of some of the simpler compatibility issues for you.

            regards, tom lane

>
>
>>
>>
>
>Have you run hardware diagnostics lately?  More and more often, when
>we see reports like this, bad RAM or bad disk cables or something like
>that proves to be the cause.  PG itself is not prone to gradually
>corrupt data.
>

The box doesn't seem to be having any obvious issues, really.  It
doesn't crash, doesn't have runaway processes, and as of monday, had no
failed RAID drives. DMESG doesn't show any disk errors, either.

>
>Once you've stabilized your hardware, a PG update would be a good thing
>to think about; but it's not going to solve hardware problems.
>
>
Done.  I've got a version of pg7.3.2 running along side the old one now,
and I've been tinkering.


>As for identifying schema compatibility problems, two suggestions:
>1. Make a schema-only dump first, and find out what you have to correct
>by loading that.  Transfer the data separately.
>
Don't know how, but I managed to 'fix' things enough to get my data in
correctly (no errors).


>2. Use 7.3's pg_dump against the older server to make the dump file.
>It can take care of some of the simpler compatibility issues for
>
This actually helped a lot, believe it or not.

However, I guess one question remains.
Will there be a speed increase, or decrease, with regards to selects,
blah blah blah?  Things seem to take a while longer on the new system;
maybe it's a result of running two versions of PG on the same system,
and that some of my programs are pretty hard-hitting.  But I'm worried
that some of these programs will take too long.

Adam

>
>

Adam Bultman <adam.bultman@iconideas.com> writes:
> Will there be a speed increase, or decrease, with regards to selects,
> blah blah blah?  Things seem to take a while longer on the new system;

I'd expect 7.3 to be quicker on average than 7.1.  Perhaps you missed
some tuning steps?  (VACUUM ANALYZE, getting shared_buffers and other
config parameters up to where they are for the 7.1 system, etc)

            regards, tom lane