pgbouncer session failover

PgBouncer는 automatic session failover를 지원하지 않는다. session failover는 connection을 여러 개로 설정하며, 순차적으로 connection을 시도하여 connection 가능한 database에 접속한다.

pgbouncer 공식 문서(https://pgbouncer.github.io/faq.html)에서 다음과 같이 언급하고 있다. 

How to failover
PgBouncer does not have internal failover-host configuration nor detection. It is possible via some external tools:
DNS reconfiguration - when ip behind DNS name is reconfigured, pgbouncer will reconnect to new server. This behaviour can be tuned via 2 config parameters - dns_max_ttl tunes lifetime for one hostname, and dns_zone_check_period tunes how often zone SOA will be queried for changes. If zone SOA record has changed, pgbouncer will re-query all hostnames under that zone.
 
Write new host to config and let PgBouncer reload it - send SIGHUP or use RELOAD; command on console. PgBouncer will detect changed host config and reconnect to new server.

pgbouncer.ini 파일에 여러 database를 추가할 수 있다.

중복된 database명은 하나만 가능하다. 

[databases]
pgb = host=127.0.0.1 port=5432 dbname=pgb
pgb = host=127.0.0.1 port=15432 dbname=pgb

위와 같이 설정을 했다면 다음과 같이 "pgb = host=127.0.0.1 port=15432 dbname=pgb" 부분이 설정된다.

pgbouncer=# show databases;
   name    |   host    | port  | database  | force_user | pool_size | reserve_pool | pool_mode | max_connections | current_connections | paused | disabled
-----------+-----------+-------+-----------+------------+-----------+--------------+-----------+-----------------+---------------------+--------+----------
 pgb       | 127.0.0.1 | 15432 | pgb       |            |        80 |            0 |           |               0 |                   0 |      0 |        0
 pgbouncer |           |  6543 | pgbouncer | pgbouncer  |         2 |            0 | statement |               0 |                   0 |      0 |        0
(2 rows)

"*" 기호를 사용할 수 있다. "*" 기호를 사용하면 모든 database에 접속할 수 있다.

[databases]
* = host=127.0.0.1 port=5432
* = host=127.0.0.1 port=15432

위와 같이 설정을 했다면 다음과 같이 "* = host=127.0.0.1 port=15432" 부분이 설정된다.

pgbouncer=# show databases;
   name    |   host    | port  | database  | force_user | pool_size | reserve_pool | pool_mode | max_connections | current_connections | paused | disabled
-----------+-----------+-------+-----------+------------+-----------+--------------+-----------+-----------------+---------------------+--------+----------
 bylee     | 127.0.0.1 | 15432 | bylee     |            |        80 |            0 |           |               0 |                   0 |      0 |        0
 pgb       | 127.0.0.1 | 15432 | pgb       |            |        80 |            0 |           |               0 |                   0 |      0 |        0
 pgbouncer |           |  6543 | pgbouncer | pgbouncer  |         2 |            0 | statement |               0 |                   0 |      0 |        0
 ttt       | 127.0.0.1 | 15432 | ttt       |            |        80 |            0 |           |               0 |                   0 |      0 |        0
(4 rows)