Slony and PGPool for failure

Question

We see Slony and PGPool as alternatives to fault tolerance management in our application - and it seems that since we need at least two database servers, we could also take advantage of load balancing -

Under what circumstances is Slony better than PGPool and vice versa?

Answer 1

It's anecdotal, so take it with salt.

PGPool and WAL streaming replication (with hot spare or not) work the way database replication should be done. Your application does not need to know anything about replication or whether it is part of a cluster or something else, it just talks to the database, like any other. Stream replication is reliable and has the ability to refuse WAL delivery if stream breaks. PGPool simplifies the management of this process and gives good results and monitoring information.

Too, on the other hand, Slony is an administrative tar-well to which it is necessary to add trigger functions and many other objects for your database to work. In addition, Slony does not (easily) support the ability to replicate schema changes (DDL) in the same way that it replicates normal input / update / delete (DML) operations. In general, these characteristics mean that in many cases your application must have special cases for managing Slony eccentricities. In my opinion, this is bad: the application does not need to know / make changes in order to deal with the database replication solution in which it runs. I spent most of the year hacking into Slony to work as a stable replication solution, and eventually came to the conclusion that this is a bad ideal / bad replication mechanism implemented in a dull, inaudible way that is no different from the stability or readiness of an enterprise, EDIT : like PostgreSQL 9.3, you can set triggers (which Slony uses to detect changes) in DDL objects, which may allow Slony to replicate more aspects of the database.

However, Slony does two things better than streaming replication (administered through PGPool or not):

• Slony allows you to replicate across each table or across each database. Stream replication enables replication of the entire Postgres instance. If such granularity is important to you, you may need Slony.
• Slony allows cascading (master -> slave -> slave) replication. Stream replication allows only one level. EDIT: This is now supported in PostgreSQL streaming replication, starting with Postgres version 9.2.

Literally, everything else, streaming replication, is better and more stable.

But you're not just asking about streaming replication: PGPool does a lot more. It allows you to balance read and write loads between read-only slave databases and wizards, as well as complete backup plans and a host of other things. Especially when compared to the syntax of the Slony arcane command and polite administration scripts, PGPool wins in almost all cases.

In terms of fault tolerance, PGPool has heart rate monitors and the ability to automatically route database traffic in a cluster. Slony only has the "fail over to slave now" command, leaving you with all the monitoring and application routing.

TL; DR: PGPool is good. Bad bad.