Re: PG on two nodes with shared disk ocfs2 & drbd

On 02/27/11 4:48 AM, Jasmin Dizdarevic wrote:
>
> I have to build a load balanced pg-cluster and I wanted...

master-master doesn't work real well with databases, especially ones
like postgres that are optimized for a high level of concurrency and
transactional integrity.

on proper hardware, postgres can run quite high transactional volumes on
a single server. If high availability is a requirement, you can run a
2nd server as a standby slave using either postgres streaming
replication, or drbd style block replication, or another such similar
technique.

you -can- distribute read accesses between a master and a slave server
via things like pgpool2, where all inserts, updates, DDL changes, etc
are made to the master server, but reads are done to either. note you
do NOT want to use block level replication like drbd for this as the
drbd slave can not be actively mounted, nor could the slave instance of
postgres be aware of changes to the underlying storage, rather you would
use the streaming replication built into postgresql 9.0. another
approach is to use the master server for all OLTP type accesses, and the
hot standby server for more complex long running OLAP queries for
reporting, etc. in case of master failure, the slave becomes the new
master, and you shut down the presumably less important OLAP operations
until such time as a new slave can be deployed.