Обсуждение: Migration/Upgrade Problems
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? 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.
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