1 #PgBouncer Slony-I replication set follower
3 This script is a command-line utility to monitor the nodes in a Slony-I cluster
4 and reconfigure pgbouncer to follow an origin or subscriber of replication sets.
6 The idea is pretty simple; periodically poll the slony schema to identify origin
7 and subscriber nodes and repoint an existing pgbouncer instance to follow either
8 the origin for read-write or a subscriber for read-only operation. The script
9 takes a template pgbouncer.ini file containing all the desired settings except
10 for the target database and will write a copy with the target database and reload
11 pgbouncer. In 'ro' mode the script will try and choose a subscriber node closest
12 to pgbouncer host if IPv4 addressing has been used for the slony conninfos.
16 The script can be either run on a schedule or as a daemon with the "D" flag:
18 # pgbouncer_follower.pl -f <config file> [-D]
20 ##Configuration options
22 | Section | Parameter | Type | Default | Comment
23 |:----------|:-----------------------|:--------|:--------------------------------------------|:-----------------------------------
24 | Slony | slony_user | text | *'slony'* | Username used to connect to PostgreSQL and select from slony schema tables
25 | Slony | slony_pass | text | *''* | Recommended to leave blank and use .pgpass file
26 | Slony | slony_cluster_name | text | *'replication'* | Name of slony cluster (without leading underscore of schema name)
27 | Slony | server_conninfo | text | *null* | Conninfo string for slony a node, can be specified multiple times
28 | pgBouncer | debug | boolean | *false* | Churn out debugging info to log file / stdout
29 | pgBouncer | follower_poll_interval | integer | *1000* | Interval to poll slony cluster state when in daemon mode
30 | pgBouncer | sets_to_follow | text | *1* | Comma separated list of sets to follow or 'all' to follow all sets
31 | pgBouncer | pool_mode | 'ro/rw' | *'rw'* | Select a read-only subscriber or the origin for read-write
32 | pgBouncer | pool_all_databases | boolean | *'false'* | If true uses wildcard for database name in pgbouncer.ini, false uses slony database
33 | pgBouncer | status_file | text | *'/tmp/pgbouncer_follower_%mode.status'* | File used to store a hash depicting the state of the cluster
34 | pgBouncer | log_file | text | *'/tmp/pgbouncer_follower_%mode.log'* | Log file for the script
35 | pgBouncer | pid_file | text | *'/tmp/pgbouncer_follower_%mode.log'* | PID file for the script when run as a daemon
36 | pgBouncer | pool_conf_template | text | *'/etc/pgbouncer/pgbouncer_%mode.template'* | Template pgbouncer.ini file with your settings and a blank [databases] section
37 | pgBouncer | pool_conf_target | text | *'/etc/pgbouncer/pgbouncer_%mode.ini'* | Target pgbouncer.ini file to write a copy of pool_conf_template with a [databases] section to
38 | pgBouncer | pool_reload_command | text | *'/etc/init.d/pgbouncer_%mode reload"'* | System command to execute to reload pgbouncer instance
40 The status_file, log_file, pid_file, pool_conf_template, pool_conf_target and
41 pool_reload_command parameters can contain the following special values:
44 %clname - Slony cluster name