From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-admin@postgresql.org
Subject: Re: Migrating from 8.3 to 8.4 on the same server
In-Reply-To: <4BC7167E.2080504@setonhome.org>
Date: Thu, 15 Apr 2010 14:53:11 -0000
Message-ID: <fd6efc8fa55523e1c61fe42f2dae5e28@biglumber.com>


-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


>> Keep in mind that if you have tables and/or databases that don't
>> change frequently, you can dump those beforehand to minimize
>> the final downtime.

> That's true, I had thought about that also.  I guess I would save the 
> contents of the biggest tables to flat files using the COPY command, 
> then delete the copied rows, before doing the dump.  Is what what you 
> had in mind?

No need to delete the copied rows: just use the handy -t and -T flags 
for pg_dump to include/exclude specific tables. You also don't need 
to save to a flat file first unless you need/want a separate backup. 
For large tables foo and bar, assuming 8.3 on port 5432 and 8.4 on port 
5840, you could do:

* pg_dump -t foo -t bar -p 5432 | psql -p 5840 -f -

Then your final migration steps would be: 

* Stop the app
* pg_dump -T foo -T bar -p 5432 | psql -p 5840 -f -
* Turn fsync back on and change the port to 5432 for 8.4
* Stop the 8.3 database
* Restart 8.4
* Start the app

All off the top of my head, requires heavy testing by QA first, 
assumes foo and bar are not used, etc. /disclaimer

- -- 
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201004151050
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkvHKDkACgkQvJuQZxSWSsjasACgmepXTrkz/rsoHsm/qKWxsyB9
YlYAoJHHeyd768IJC5RqNn2aXPD/80fp
=6yRN
-----END PGP SIGNATURE-----