By Kaarel Moppel – Walbouncer was covered on the blog when it was first announced, but that was almost 2 years ago. So it’s 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 walbouncer code available on GitHub.
About the walbouncer update
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 load-balancing or analytical query reasons, one needs a single database only. With the 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 14 and any feedback or pull requests on GitHub would be very much appreciated.
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.