I just stumbled over an old architecture diagram from one of the projects I used to work on. The type of services and project in question are left as an exercise to the curious reader, since this is not the point of this posting.

What I wanted to show is, how complex multi-tiered applications can be these days, especially when you phase in new services or try to replace old ones by setting up the new services to run in parallel to the existing ones.

To quote Jeff Atwood in his blog:

Server Fault is a sister site to Stack Overflow, which we launched back in September 2008. It uses the same engine, but it's not just for programmers any more:

Server Fault is for system administrators and IT professionals, people who manage or maintain computers in a professional capacity. If you are in charge of ...
* servers
* networks
* many desktop PCs (other than your own)
... then you're in the right place to ask your question! Well, as long as the question is about your servers, your networks, or desktops you support, anyway.

Slony-I is a trigger-based replication solution which allows you to replicate database tables and sequences asynchronously from one master to several read-only slaves (which can also be cascaded).

Trigger-based means, that each table and sequence which gets replicated has triggers assigned, which will fire whenever the content of the given database object changes. The stored procedures, which are associated in the triggers, will then record the changes and store these in a replication log table. Separate daemons monitor the log table for changes and distribute the changes according to their defined rules.

This approach allows for extremely flexible setups, having different master servers for different tables, but this comes at a price.

As we've seen in our previous example, doing log shipping with pg_standby can be quite a hassle if you take your slave servers regularly online to use them for queries and then want to resume replication again.

The guys from Skype were probably faced by exactly the same problems when they decided to write walmgr.

If you're not familiar with log shipping I strongly suggest to read the previous post first.

Log shipping?!

PostgreSQL offers support for "shipping" it's WAL, the Write Ahead Log, where the changes of every transaction are recorded, to other database systems. The other database system then reads the changes from the WAL file and applies the changes to it's local data store.

Log shipping has the drawback that the slave servers can't be used for queries as long as they are replicating data and cannot be put back in replication after they've been taken online. Additionally the replication isn't very granular, PostgreSQL natively itself will accept only completed WAL files.

On the other hand this mechanism is very efficient and very reliable since the WAL is at the core of normal PostgreSQL operation.

I want to provide an introduction, annotated examples and an easy to setup test environment for a few common and "simple" PostgreSQL replication solutions.

I planned on providing images, but after what I've seen so far it seems to be much easier to just provide a HowTo ;).

Prerequisites

I chose Debian as test platform because I'm familiar with it and the PostgreSQL related packages are in excellent shape there.

What you need is

• two separate Debian Lenny instances
• with the following packages installed:
  • postgresql postgresql-contrib postgresql-8.3-slony1 slony1-bin mercurial less libdbd-pg-perl libpoe-perl rsync psmisc ssh screen python-psycopg2 libstring-random-perl
• which are reachable via the respective hostnames "master1" and "slave1"
• Where both the root and the postgres user from master1 can ssh into root and postgres on slave1

Der Workshop ist nicht so gelaufen wie ich mir's erwartet habe.

Ich war noch etwas gefertigt von der halben Grippe die ich mitgenommen habe, insgesamt waren nur drei Personen dabei¹, Virtualbox hat bei niemandem out of the Box funktioniert (Fuck this, I'm going back to VMware) und mit dem Scoping ist's bei einer sehr kleinen, Erfahrungsmässig weit verstreuten Gruppe, auch immer extrem schwer.

Damit der Rest der Welt aber auch etwas davon hat (und ich den beiden auch noch was zum durchtesten geben kann) werde ich den Praxis-Teil in ein paar Artikeln aufbereiten und hier zur Verfügung stellen.

Die Slides vom Workshop gibts mal hier

¹ Und nach 'ner Stunde war ich mit Kristian allein, weil ein Teilnehmer seinen Zug erwischen musste und ein anderer ob des dysfunktionalen Virtualboxes lieber noch einen Talk erwischen wollte.

It was a nice conference, the guys and gals from Netways surely know how to run an event. It's all the nice little details which make up a great experience¹.

I was also surprised by NH Hoteles, the Nuremberg City one greeted us with one of the most attractive parking garages I've ever seen (_very_ clean, "follow me"-lines on the floor, automatic hinged safety doors, complimentary window cleaning for hotel guests, etc.) and the hotel lived up to the standards it set in it's garage ;). The only problem I noticed was that the dining area was constantly understaffed for the 70-something people which attended the conference.

The lineup of the conference was quite nice although I prefer "war stories" told from real world scenarios over feature presentations of a single solution. Fortunately Kristian Köhntopp was able to speak about his experiences from his times as a MySQL consultant and the stuff he's doing over at booking.

More on that after the break.

¹ A few examples:

• Everything planned & communicated in advance, no uncertainties on what/how/where
• Taped down cables everywhere
• Enough power sockets for laptops and other gadgets in every conference room and the lounge area
• Nameplates on the speakers podests
• Constantly refilled/replaced bottles & glasses