Based on different parameters, reuirements etc you have the following popular options.
- Block level replication – DRBD
- PostgreSQL Streaming replication (SR)– only available 9.0 +
- Slony Trigger based replication – Almost the gold standard for Master – Slave replication
- Bucardo – Perl based multi master Async replication
- pgPool- II – Load balancer, Query cache, connection pooller , Replication manager
- pgCluster – multi-master synchronous replication
Here is a technical summary of the available options. (Courtesy Bruce Momjin)
PGCluster looks great, but sadly still experimental and does not support PostgreSQL 9.0 yet and during a local test seems to have high performance cost
Slony is a very popular technique but can do the following
- Possible data loss during fail-over
- Triggers add over-head
- Replication possible even on slower links
- Table level granularity allows for complex portioning scheme and cascading slaves
Buocardo – Similar to slony , but allows multi-master replication and is based on perl. The conflict resolution is user configurable. However it only supports multi-master replication among 2 servers, and not more
- Automatically load balances read queries
- Allows parallel query execution
- Does connection pooling and query caching
- With added multi-master replication the problem of non-deterministic queries is solved.
Built-in Streaming Replication
- Hot Standby
- Slaves can execute queries
- Synchronous supported in 9.1
- Fastest way to replicate data to date. Since its all built-in
A good solution is a hybrid one, which would be to use PgPool-II with PostgreSQL built in streaming replication. This eliminates most problems with pgPool-II stand-alone version or SR standalone version and also ensures to a large degree ZERO downtime.
Applications will connect to pgPool-II just like they are connecting to a postgreSQL instance and pgPool will automatically load balance the read queries. Writes will only be done to one server, if the master server goes down, the slave can take over almost immediately since it is setup as a Hot standby, and pgPool will automatically redirect reads and writes to the slave.
- If the master goes down, PG Pool can do the failover, slave takes over
- If the slave goes down, well, all reads are redirected to master only
- The probem here could be that pgPool can be the single point of failure, we have an option of having 2 pgPool machines made high available using Linux HeartBeat.
- Another option if we don’t want to use two separate machines, is that we can put pgPool both on master and the slave, that will be a slightly complex configuration, but certainly possible to do.
References : http://www.dalibo.org/_media/pgpool.pdf