Slony 2.0.5 was just released. This version is the result of a lot of testing of the 2.0.x version and I feel that 2.0.5 is now more stable and a better choice than 1.2.x for most deployments.
PostgreSQL 9.0 (including streaming replication) is also now out.
Some people are asking if slony or streaming replication is a better choice for them. Here is my take on the issue.
Both the streaming replication and Slony are asynchronous. If you need synchronous replication then you will have to wait at least until 9.1. However, if your looking for asynchronous replication then ask your self the following questions:
- Are my master and slave running the same version of PostgreSQL on the same platform?
- Does my slave only need to perform failover or read-only queries?
- Do I only need one slave? (Multiple slaves can consume the same WAL segments but it is unclear to me how you will be able to keep the second slave after failing over)?
- Do I want my slave to be identical to the master(no extra tables, no extra databases, no extra indices)?
If the answer to all of the above questions is yes then streaming replication in 9.0 might be a good choice for you.
However if the answer to any of the following questions are yes:
- My master and slave are on different hardware platforms?
- I want to add some tables for reporting on my slave?
- I have multiple databases on my master but only want to replicate some of them to the slave?
- For security reasons I want tables to have different permissions on my slave then my master?
- I want to be able to take my master down for hardware maintenance but after I’m done I want to have the master take over from the slave without having to re-copy my entire database?
- I want to replicate from A==>B and then have B replicate to C and D?
- I can live without automatic DDL replication
then Slony (or another trigger based replication system) might be a better choice for you. It is unlikely that the WAL based replication in PostgreSQL will ever be able to deal with a lot of these use-cases. I see many situations where trigger based replication is appropriate and I don’t see this changing with 9.0 or 9.1
Great, copied for later reference.
BMA
Is Streaming Replication table level like slony-I?
no streaming replication must replicate the entire database cluster.
Thanks for the insight. I’m keen to try slony!
Good analysis, very useful to PostgreSQL DBAs
Can we configure same database with Streaming Replication to Cold Stand by server and Slony1 to replicate few tables to 14 slaves?
hi,
thanks for the info that you have given, need to know is there any way in streaming replication i dont want to replicate the delete of table in master to slave, in streaming replication what ever is written in the master WAL file will be replicated in the slave. any way out ?
That is not possible with streaming replication. If you don’t want to replicate delete’s to a slave then you should use a trigger based replication solution such as Slony.
thanks for the reply, but for my application in slony’s (ie trigger based ) performance is very low, it is not handling load…. and i am able to get the performance with streaming replication. any way thanks.
[...] replication‘ where I will give an overview of built in replication and Slony. My blog post comparing Slony and 9.0 replication is by far the most popular post on this blog, and the talk will [...]
Indeed it is a good analysis. However, I am still struggling with the issue that drew me to this page.
I have two Datacenters DC1 and DC2.
In DC1, I need to have 3 instances of the same DB (DBDC1-1, DBDC1-2 and DBDC1-3) and I need them to be all the time at the same level. So far I considered using pgpool with the 3 DB being masters.
This DB needs to be replicated on to DC2 on 3 instances as well (DBDC2-1, DBDC2-2, and DBDC2-3) and of course these 3 instances need to be at the same level all the time.
Does anybody have a solution on how to do this ?
Cheers