Message-ID: <48AE5A31.8090003@tauceti.net>
Date: Fri, 22 Aug 2008 08:18:25 +0200
From: RW <postgres@tauceti.net>
User-Agent: Thunderbird 2.0.0.16 (X11/20080812)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: The state of PG replication in 2008/Q2?
References: <48ADABAF.8030602@drivefaster.net>
In-Reply-To: <48ADABAF.8030602@drivefaster.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


> My company finally has the means to install a new database server for 
> replication.  I have Googled and found a lot of sparse information out 
> there regarding replication systems for PostgreSQL and a lot of it 
> looks very out-of-date.  Can I please get some ideas from those of you 
> that are currently using fail-over replication systems?  What 
> advantage does your solution have?  What are the "gotchas" I need to 
> worry about?
>
> My desire would be to have a parallel server that could act as a hot 
> standby system with automatic fail over in a multi-master role.  If 
> our primary server goes down for whatever reason, the secondary would 
> take over and handle the load seamlessly.  I think this is really the 
> "holy grail" scenario and I understand how difficult it is to 
> achieve.  Especially since we make frequent use of sequences in our 
> databases.  If MM is too difficult, I'm willing to accept a 
> hot-standby read-only system that will handle queries until we can fix 
> whatever ails the master.
> We are primary an OLAP environment but there is a constant stream of 
> inserts into the databases.  There are 47 different databases hosted 
> on the primary server and this number will continue to scale up to 
> whatever the server seems to support.  The reason I mention this 
> number is that it seems that those systems that make heavy use of 
> schema changes require a lot of "fiddling".  For a single database, 
> this doesn't seem too problematic, but any manual work involved and 
> administrative overhead will scale at the same rate as the database 
> count grows and I certainly want to minimize as much fiddling as 
> possible.
>
>
If you really need "only" need automatic failover than use DRBD + Heartbeat
somebody already mentioned. We are using this solution since 3 years now.
With DRBD replication is done at filesystem block level. So you don't 
have to
bother about changes done with a DDL statement and Heartbeat will
automatically failover if one server goes down. It's really stable.

If you want MM you should give Cybercluster a try. 
(http://www.postgresql.at/english/pr_cybercluster_e.html)
They offer good support and is Open Source since a few month now.

Robert