Congrats on the 9.0 release of PostgreSQL. One of the features I am really interested in is the built-in binary replication.
Our production environment has been using PostgreSQL for more than 5 years (since this project started). We have been using Slony-I as our replication mechanism. I am interested to find out the pros and cons of Slony vs the built-in replication in 9.0. Based on what I understand:
* Slony has a higher overhead than the binary replication in 9.0 * When using Slony, schema change must be applied via slonik (in most cases) * Unfortunately, IMO it is easy to make a mistake when applying schema changes in Slony, fortunately, it is easy to drop and recreate the replication sets * Slony is an asynchronous replication mechanism * Slony allows you to replication some tables, while ignoring others
* PostgreSQL 9.0 with hot standby& streaming replication is an asynchronous replication mechanism * Overhead is low compared to Slony
Are there some cases where it is better to use Slony, for example, when you must specifically exclude tables from replication? I believe our system will be better off using the built-in replication mechanism of 9.0, and I am guessing most people will be in the same boat.
You have summarized the differences well. Streaming replication has lower overhread, but doesn't allow per-table granularity or allow replication between different versions of Postgres.
Slony will also allow you to:
-run custom schema (like extra indexes) on replicas -replicate between different hardware architectures and OS's -run lengthy queries against replicas having to worry about trade offs surrounding query cancellation vs standby lagging. -switch roles of two nodes without entering a degraded state or worrying about STONITH. If you switch roles in a controlled manner, both nodes remain in the cluster. Slony prevents writes against the replica.
I do agree that for most, Slony is overkill and streaming replication and hot standby will be the better choice.