walbouncer: Filtering the transaction log

10.2014 / Category: How To / Tags: administration | transactions

Up until now, it was only possible to replicate entire database instances from one node to the other. A standby always had to consume the entire transaction log created by the primary turning the standby into a binary copy.
However, this is not always desirable. In many cases, it's necessary to split the database instance and replicate data to two different places. Just imagine having a server storing data for Europe in one and data for the US in some other database. You might not want to replicate all the data to your standbys. The standby located in Europe should only have European data and the US standby should only contain data for the United States. As mentioned before vanilla-PostgreSQL cannot do that.

walbouncer (made by CYBERTEC) can step in and fill the gap. By filtering the transaction log, it is possible to replicate only the data needed in one place. Walbouncer will act as a WAL proxy for PostgreSQL and take care of all the filtering. Here is an image showing how things work:

walbouncer is directly connected to the WAL stream of the primary and decides, based on its config, what to replicate to which host. This makes life very easy.

How it works:

Internally the WAL is based on so called wal-records. wal-records are available for different types of operations: btree changes, COMMIT, etc. In addition to useful WAL records, PostgreSQL provides dummy records. Those records are mainly for testing but can also be used to filter WAL. Transaction log, which is meant to be left out, will be replayed with dummy records of the same length by the walbouncer. This way only the data destined for a certain server will actually reach it.

walbouncer is capable of serving many standbys at a time with very little overhead. Internally it forks for every standby and thus offers a robust system architecture. To the end user walbouncer is totally transparent - it simply acts as a proxy - and all the configuration of PostgreSQL stays the same.

Configuring walbouncer

Configuring walbouncer is simple. At this stage a simple YAML file is used to tell the system what to do. Here is an example:

listen_port: 5433
primary:
    host: localhost
    port: 5432
configurations:
    - standby1:
        match:
            application_name: standby1
        filter:
            include_tablespaces: [spc_standby1]
            exclude_databases: [test]
    - standby2:
        match:
            application_name: standby2
        filter:
            include_tablespaces: [spc_standby2]

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

listen_port: 5433

primary:

host: localhost

port: 5432

configurations:

- standby1:

match:

application_name: standby1

filter:

include_tablespaces: [spc_standby1]

exclude_databases: [test]

- standby2:

match:

application_name: standby2

filter:

include_tablespaces: [spc_standby2]

First of all the port the walbouncer is listening to is defined. Then all it needs are the hostname and the port to connect to the primary. Finally there is one section for each standby. In the “match” section an application name can be defined (needed for synchronous replication). In addition to that there is a “filter” section, which tells the system, what to include respectively what to leave out.

Find more information and download walbouncer here: CYBERTEC walbouncer

In case you need any assistance, please feel free to contact us.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.