On Sun, Sep 11, 2011 at 4:16 PM, Andy Colson <andy@squeakycode.net> wrote:
> Upgrading to major versions of PG may or may not be painful. (mysql
> sometimes works seamlessly between versions, it appears brilliant. But I
> have had problems with an update, and when it goes bad, you dont have a lot
> of options). In the past PG's only method of upgrade was a full backup of
> old, restore in new. Things have gotten better, there is new pg_upgrade
> support (still kinda new though), and there is some 3rd party replication
> support where you replicate your 9.0 database to a new 9.1 database, and at
> some point you promote the new 9.1 database as the new master. Or something
> like that. I've only read posts about it, never done it. But with that
> much data, you'll need an upgrade plan.
I have used slony to do database migration. It is a pain to set up,
but it saves you hours of downtime.
Basically, you replicate your 9.0 database into a 9.1 slave while the
9.0 is still hot and working, so you only have a very small downtime.
It's an option, but it's a lot of work to set up, only warranted if
you really cannot afford the downtime.