Results tagged “pgrep”

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
1