Best way of upgrading postgresql in production?

Gunther Mayer gunther.mayer at
Thu May 29 16:31:15 UTC 2008

Bill Moran wrote:
> In response to Gunther Mayer <gunther.mayer at>:
>> 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
>>       ( 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.



More information about the freebsd-questions mailing list