Best way of upgrading postgresql in production?

[Gunther Mayer]

Bill Moran wrote:
> In response to Gunther Mayer <gunther.mayer at googlemail.com>:
>   
>> I've been grappling with this and read all sorts of email threads and 
>> blog posts but I still have no good solution to the following problem: I 
>> want to upgrade a postgresql installation on FreeBSD 6.3 from 8.2 to 8.3 
>> as we'd like to take advantage of its new features and performance 
>> enhancements. Yet none of the ports system offers no clean _and_ quick 
>> way of performing such an upgrade as the Makefiles of the respective 
>> ports choke as soon as they detect an existing older -server or -client.
>>
>> Yet all I want to do is in a script which fires automatically at 5am via 
>> cron (of course I'll get up to check just in case but I've done this 
>> many times before):
>>
>>    1. Do all backup tasks (build packages for all installed postgresql
>>       8.2 stuff for possible rollback, full database dump, configs etc.)
>>       and take down all processes that write to the db
>>    2. Shut down the database
>>    3. Uninstall all postgresql 8.2 ports (client, server and client libs
>>       we depend on)
>>    4. Install all postgresql 8.3 ports
>>    5. Fire up the new db, restore the complete database dump
>>    6. Restore the configs (pg_hba.conf, postgresql.conf etc.) and
>>       restart the database
>>    7. Start up all "db write" services again
>>
>> Ideally that process shouldn't take longer than 5 minutes but step 
>> number 4 is currently a big stumbling block as
>>
>>     * Building from ports will take a while
>>     * I can't find any binary packages for 8.3 (would need i386 for
>>       testing and amd64 for live) anywhere as far as I can see
>>       (ftp.freebsd.org has nothing, nor do the mirrors)
>>     * building binary packages myself is impossible on the same machines
>>       as pkg_create can only do that with installed packages, "make
>>       package" et. all choke when they realise you already have
>>       82-{client,server} installed and I don't see any other way of
>>       creating a package without installing one
>>
>> How can I get out of this catch-22 /without /resorting to complicated 
>> jail setups or even worse, manual compilations with different prefixes 
>> and other nasties? There must be a way to get a package somehow...
>>     
>
> Jail setups are not complicated.  You could also make the packages on
> another system.  How about doing a make package on the server that you've
> tested your application against 8.3 on?  You _have_ done that, right?
>   
That's not an option since my testing box runs i386 while the live one 
runs amd64, I guess I should really invest in a testing box with the 
same arch...
> What makes you think that jail setups are so complicated.  I set up new
> jails almost every week.  I get the impression that you have some reason
> for avoiding the obvious solution, and I suspect it revolves around some
> incorrect impression that jails are "complicated".
Ok, you're probably right, I just haven't worked with jails before and 
have just read the wrong articles. I will investigate how I could 
install the newer version in a jail and keep both running at the same 
time during the upgrade, I'll probably run into more problems along the 
way but will post again if I'm stuck.

Thanks,

Gunther