Migration/Upgrade Problems

On Fri, Apr 17, 2009 at 09:19:31AM -0700, stevefoss wrote:

I am trying to upgrade my postgresql data from 7.0.3 to 8.3.7 with
marginal success. I tried pg_dumpall and got the tables but lost
most of the data. I also tried pg_dump but keep getting "FATAL 1:
Database "whatever" does not exist in the system catalog" errors. I
also tried using the latest versions of pg_dump and pg_dumpall but
they just will not work. They are looking for some shared library
file that does not exist. Any suggestions? Do I need to upgrade to
an intermediate version before jumping to the latest version?

As you've intuited, for a version that ancient, you'll probably need
to do this in stages, so

* Get PostgreSQL 7.4.25
* Use its pg_dumpall on the running 7.0.3 database
* Load that into the 7.4.25 database
* Run contrib/adddepend on each of the databases in it
* Use 8.3.7's pg_dumpall to dump the now-fixed 7.4.25 database
* Load that into 8.3.7, and, most importantly,

* Build in and enforce upgrades as part of your maintenance cycle.
You never want to have to do *anything* like this again.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Fri, 17 Apr 2009 09:19:31 -0700 (PDT), stevefoss
<sfossum@autotraninc.com> wrote:

I am trying to upgrade my postgresql data from 7.0.3 to 8.3.7 with

marginal

success. I tried pg_dumpall and got the tables but lost most of the

data.

I also tried pg_dump but keep getting "FATAL 1: Database "whatever" does
not
exist in the system catalog" errors. I also tried using the latest
versions
of pg_dump and pg_dumpall but they just will not work. They are looking
for
some shared library file that does not exist. Any suggestions? Do I

need

to upgrade to an intermediate version before jumping to the latest

version?

I would first make sure that you have run a database wide vacuum on each
database.

Your library problems are likely because you just copied the binary? I
assume you didn't actually install postgresql. As far as running older
versions of pg_dumpall, I am not even sure 8.3 will work on 7.0.
I think you might have to double or triple upgrade. I would try using 7.4
pg_dumpall to get a clean dump. Restore that dump into a 7.4 cluster. Then
use 8.3 to pull from the 7.4.

Note that your application is pretty much guaranteed not to work. You will
have to rewrite a bunch of queries.

Joshua D. Drake

Thanks
--
View this message in context:
http://www.nabble.com/Migration-Upgrade-Problems-tp23100921p23100921.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

stevefoss wrote:

I am trying to upgrade my postgresql data from 7.0.3 to 8.3.7 with marginal
success. I tried pg_dumpall and got the tables but lost most of the data.
I also tried pg_dump but keep getting "FATAL 1: Database "whatever" does not
exist in the system catalog" errors.

No intermediate version upgrade should be necessary if you're up for
some query rewriting (which you'll need anyway I think). However, the
most serious problem appears to be that you've run into Xid wraparound,
and I don't think it's an easy problem to solve in 7.0.

I also tried using the latest versions
of pg_dump and pg_dumpall but they just will not work. They are looking for
some shared library file that does not exist.

How did you install them? Obviously copying binaries from another
system will not work, but if you were to compile them on the same system
they should work.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

"Joshua D. Drake" <jd@commandprompt.com> writes:

Your library problems are likely because you just copied the binary? I
assume you didn't actually install postgresql.

That's what it looked like to me --- you can't just copy an executable
that's been built on a far newer platform. However, you could run
pg_dumpall on the newer machine and have it connect to the old server
across the network.

As far as running older
versions of pg_dumpall, I am not even sure 8.3 will work on 7.0.

It's supposed to, and I do test it on occasion ;-). The main gotcha I'd
watch out for is that you should probably use --column-inserts instead
of relying on COPY mode for a server that old.

regards, tom lane