Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

What is the Difference between Streaming Replication and Slony

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


Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

7 Steps to configure BDR replication in postgresql

Postgres Streaming Replication Configuration

vacuumlo - removing large objects orphans from a database PostgreSQL