Walbouncer was covered on the blog already when it was first announced, but that was almost 2 years ago – so it would be a good time to echo it out again, especially in light of compatibility update to support PostgresSQL 9.5, addition of a helper script for bootstrapping replicas called Walbouncer-companion and making the code available on Github.

To recap the previous blogpost – Walbouncer is a standalone application acting as a replication proxy, developed by Cybertec. It stands between a master and a replica, enabling selective replication by forwarding data only for specific databases/tablespaces to the connected replicas, filtering out other data (replacing it with “no-op”-s). One Walbouncer can serve multiple replicas, with different database/tablespace configurations.

Main use case

The main use case for applying Walbouncer is in environments where many databases happen to be located on the same instance, but say for a load-balancing or analytical query reasons one needs a single database only. With current means provided by the Postgres project, one would need to copy the whole instance with all of the databases! Thus disk space could become a serious problem. Also with Walbouncer the general system load on the replicas would sink as uninteresting WAL data wouldn’t be applied but just thrown away.

Steps for trying out Walbouncer


# Download and compile Walbouncer
git clone https://github.com/cybertec-postgresql/walbouncer.git
cd walbouncer/src && make

# Customize the sample config file by saying which databases/tablespaces should be replicated
vim ../walbouncer.conf.sample

./walbouncer -c ../walbouncer.conf.sample

# Download the helper Python script for creating a “selective basebackup” for cases when disk space is scarce or network is slow, so that only data files for databases/tablespaces that we’re interested in are copied. NB! When using tablespaces, one needs to ensure that identical paths exist on the replica also
# pg_basebackup could be used as well here, when the initial full size of the cluster is not a problem
git clone https://github.com/cybertec-postgresql/walbouncer-companion.git
cd walbouncer-companion
pip install -r requirements.txt
walbouncer_companion.py -c path_to_walbouncer/walbouncer.conf.sample –replica-name repl1 –pgdata filtered_replica/

# Create an customize a recovery.conf to point to the running Walboucer instance
cat  filtered_replica/recovery.conf
recovery_target_timeline = 'latest'
primary_conninfo = 'host=localhost port=5433 user=postgres application_name=repl1'

# Start the replication server. All done. When databases/tablespaces that are filtered out are connected to, user will just get an error
pg_ctl -D filtered_replica/ start

Summary

Before full built-in logical replication is integrated into core PostgreSQL, using Walbouncer would currently be the easiest way to implement highlighted special scenarios, where having a full replica is not feasible. Walbouncer currently works with PostgreSQL 9.4 and 9.5 and any feedback or pull requests on Github would be very much appreciated.