Re: Move databases from Pg 7.2.1 to 8.3.x

Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> On Tue, 2009-04-14 at 09:58 -0700, Roderick A. Anderson wrote:
>>> The plan calls for a pg_dump using my workstation using CREATE the
>>> database and INSERTS (-C -D) .  Then use psql to run the script created
>>> above to build the database on the new system.
>
>> You want to install the new version of PostgreSQL and use the version of
>> pg_dump that comes with that machine.

Right.  I left that out.  I have a more recent copy of Pg (or maybe just
the utilities) on my workstation -- actually 8.3.4.  Pretty recent.

> Check.  In theory the other way should work, but usually the newer
> version of pg_dump is a better bet (particularly for such an old version
> --- seven more years of bug fixes, eh?)
>
>> Secondly there is no reasons to do -D (which is actually -d btw). Just
>> do a stock pg_dumpall.

Well there are other databases in the cluster for other applications
that I will not be moving so I'm doing those databases I need one at a time.

> It might actually be a good idea to use --column-inserts for this.
> I don't recall all the details right at the moment, but ISTR there were
> some funnies with respect to corner-case COPY data syntax back in the
> dim past, and it might be that that includes 7.2.  This was a
> server-side issue and so just using a newer pg_dump wouldn't fix it.

Right.  Therefore the "-D".

> In any case it'd be a really good idea to see if you can manage a dry
> run before doing it live.  You can expect some application compatibility
> issues across such a large version jump, even after you get past any
> difficulties in moving the data.  So it'd be smart to do an import into
> a test server and see how your apps work against it before you do it
> for real.

Oh yeah!  That is the plan.


\\||/
Rod
--