Best way of upgrading postgresql in production?

Gunther Mayer gunther.mayer at
Thu May 29 12:47:01 UTC 2008

Hi guys,

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


More information about the freebsd-questions mailing list