Up to now it was only possible to replicate entire database instances from one node to the other. A slave always had to consume the entire transaction log created by the master turning the slave into a binary copy.
However, this is not always desirable. In many cases it is 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 slaves. The slave located in Europe should only have European data and the US slave should only contain data for the United States. As mentioned before vanilla-PostgreSQL cannot do that.
walbouncer (made by Cybertec Schönig & Schönig GmbH) 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 master 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 slaves at a time with very little overhead. Internally it forks for every slave 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 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 master: host: localhost port: 5432 configurations: - slave1: match: application_name: slave1 filter: include_tablespaces: [spc_slave1] exclude_databases: [test] - slave2: match: application_name: slave2 filter: include_tablespaces: [spc_slave2]
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 master. Finally there is one section for each slave. 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