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 PostgreSQL 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.