Posts Tagged ‘replication’

PostgreSQL is becoming a more popular choice for an embedded database because of its BSD license, relatively low memory footprint and great list of features. A few people have asked me if Slony would be a good choice for replication in an embedded environment. Embedded deployments haven’t been a primary use-case for Slony and some of the challenges you would face are worth writing about.


February 6’th 2012 marks the 10 year anniversary of the open source release of the DBMirror replication system. DBMirror was not the first PostgreSQL replication solution to be released but it was the first one I was involved with.

In the summer of 2001 I was working for Navtech System Support, an aviation software company. We were using PostgreSQL 6.x to store data for one of our applications. We needed to have an up to date copy of the database available on servers at a remote site. We also needed a standby database server in case our primary server failed.

My talk on PostGIS replication at FOSS4G 2011 went well. It looked like there were about 150 people in the room. Most of them had not yet deployed a PostGIS replication solution.

My talk covered Slony and streaming replication. It gave an overview of different replication patterns that can crop on in the GIS space. I then gave an overview of the key features and limitation of Slony and streaming replication.

A video of the talk is available at FOSSLC

My slides are available here

The slides from my PostgreSQL replication talk at FOSSLC are available here.

The talk covers both Slony and Streaming replication. The key points covered in the talk are

  • Why use replication
  • Some common load balancing architectures
  • 6 Simple steps to setting up Slony
  • 5 Simple steps to setting up streaming replication

I will update this post to link to a video of the talk when FOSSLC makes it available.

Updated: A video of the presentation is available at here

I’m planning on attending two conferences this September in Denver. The first conference is the annual OpenStreetMap State Of The Map‘ September 9-11. This year will mark the first time since I’ve been involved with OpenStreetMap that the main State Of The Map conference has been held in North America. I am looking forward to putting faces to names and meeting lots of awsome mappers. I might be giving a talk on new features in PostgreSQL 9.1 at the conference but they haven’t yet accepted talks or announced the schedule.

Following State Of the Map I will be hanging around in Denver for FOSS4G 2011 (September 12-16). FOSS4G is the annual conference for open source geo-spatial software. I will be giving a talk on ‘PostGIS 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 expand on that material.

They are expecting about 1000 people to attend FOSS4G this year. I am expecting there to be a lot of maps and talk about maps. In addition to my talk there are many other PostGIS/PostgreSQL talks on the schedule. If your going to be attending a conference related to databases this September what better place to be than Denver? The early registration discounts end on June 30’th so remember to register before the price goes up

clustertest is the distributed testing framework that we built for testing Slony. While in Ottawa for PGCon, I modified it such that clustertest can be used to test the streaming replication features built into PostgreSQL 9.1.


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:

  1. Are my master and slave running the same version of PostgreSQL on the same platform?
  2. Does my slave only need to perform failover or read-only queries?
  3. 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)?
  4. 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:

  1. My master and slave are on different hardware platforms?
  2. I want to add some tables for reporting on my slave?
  3. I have multiple databases on my master but only want to replicate some of them to the slave?
  4. For security reasons I want tables to have different permissions on my slave then my master?
  5. 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?
  6. I want to replicate from A==>B and then have B replicate to C and D?
  7. 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