Re: Postgres for a "data warehouse", 5-10 TB

On Sun, Sep 11, 2011 at 4:16 PM, Andy Colson <andy(at)squeakycode(dot)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.