Kaarel Moppel: Walbouncer refreshed – a proxy for selective PostgreSQL physical replication

Datetime:2016-08-23 02:16:55          Topic: PostgreSQL           Share

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

Kaarel Moppel

I’ve been interested with databases for the last 9 years, working last 5 years exclusively with PostgreSQL. And still I’m constantly surprised by it’s powerful set of features and the fast pace of development by the globally friendly community. On my spare time I enjoy playing soccer and travelling.





About List