Best way of upgrading postgresql in production?

[Bill Moran]

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?

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".

-- 
Bill Moran
http://www.potentialtech.com