Re: What is the best plan to upgrade PostgreSQL from an ancient version?

On Mon, Jan 26, 2009 at 06:45:55PM -0800, Steve Atkins wrote:
> On Jan 26, 2009, at 6:33 PM, Tom Lane wrote:
>> "Dann Corbit" <DCorbit(at)connx(dot)com> writes:
>>> My notion is to do a character mode database dump as SQL
>>> statements and then load into the new version by execution of
>>> psql against the sql STATEMENTS. What are the "gotchas" we can
>>> expect with this approach? When I say 'ancient' I mean v7.1.3 and
>>> the target is v8.3.5.
>>
>> Yoi, that is a long way. As already noted, you should use the 8.3
>> version of pg_dump to pull the data from the old server; this
>> should smooth some of the bumps, but there will be more.
>
> ISTR there being some hard problems moving from something that old
> to 8.2, and that doing it via an intermediate 7.4 installation ( use
> pg_dump 7.4 against the 7.1 installation, load it into a 7.4
> installation, then use the 8.3 pg_dump against that) avoided some
> problems. It's been a while, and I don't recall what the problems
> were, so ICBW.

I think you may be thinking of the situation where foreign keys were
implemented as visible triggers, and the contrib/adddepend script,
which was removed (IIRC) in 8.2.

You can still find that code here:

http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/adddepends/adddepends/

>> Also, experiment with using the -d or -D options to pg_dump (ie
>> dump data via INSERT not COPY) if you have problems. I forget
>> exactly when we got rid of the last risk factors for COPY-style
>> dumps, but it might've been after 7.1. This'll be slower though.
>>
>> I don't have too much else to add to what was already said, except
>> to reinforce the advice to test your applications before you do the
>> live migration. You're almost certain to hit some compatibility
>> issues.
>
> +1

+1 from here, too.

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

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