Features:

- supported distributions: Redhat 6, CentOS 6, Scientific Linux 6, Oracle Linux.

- allow specify pools, allowed users and other pgbouncer settings.

- supported only md5, any or trust auth types.

- add logrotate config

Known issues:

- plain and crypt auth types does not implemented and not tested.

Todo:

How-to use:

- download repo with git clone;

- cd into role directory;

- change hosts: variable in role.yml;

- start ansible-playbook with role.yml and your inventory file.

roles_path = ../

pgbouncer_ini: /etc/pgbouncer/pgbouncer.ini

pgbouncer_pool_mode: transaction

enable_logrotate: yes

pgbouncer_pools:

- { name: "db1", conninfo: "host=127.0.0.1 port=5432 user=johndoe password=test1234" }

pgbouncer_admin_user: pgbouncer

pgbouncer_admin_group: postgres

pgbouncer_stats_users:

- zabbix

- munin

pgbouncer_allowed_users:

- johndoe

- vpupkin

pgbouncer_logfile: /var/log/pgbouncer.log

pgbouncer_pidfile: /var/run/pgbouncer/pgbouncer.pid

pgbouncer_listen_addr: '*'

pgbouncer_listen_port: 6432

pgbouncer_auth_type: md5

pgbouncer_auth_file: /etc/pgbouncer/userlist.txt

pgbouncer_max_client_conn: 10000

pgbouncer_default_pool_size: 30

---

- hosts: staging

roles:

- { role: install-pgbouncer, sudo: yes, user: root, tags: [ 'install' ] }

- name: "Stage 2: stop pgbouncer service"

service:

name: pgbouncer

state: stopped

- name: "Stage 2: create pgbouncer.ini"

template:

src: pgbouncer.ini.j2

dest: "{{ pgbouncer_ini }}"

owner: "{{ pgbouncer_admin_user }}"

group: "{{ pgbouncer_admin_group }}"

mode: 0600

- name: "Stage 2: remove old userlist.txt"

file: state=absent dest={{ pgbouncer_auth_file }}

- name: "Stage 2: create new empty userlist.txt"

file:

state: touch

dest: "{{ pgbouncer_auth_file }}"

owner: "{{ pgbouncer_admin_user }}"

group: "{{ pgbouncer_admin_group }}"

mode: 0660

- name: "Stage 2: add users into userlist.txt"

sudo: yes

sudo_user: postgres

shell: psql -qAtXF' ' -c "select rolname,rolpassword from pg_authid where rolname = '{{ item }}'" |sed -e 's/^/\"/' -e 's/$/\"/' -e 's/ /\" \"/' >> {{ pgbouncer_auth_file }}

with_items: pgbouncer_allowed_users

- name: "Stage 2: fix permissions userlist.txt"

file:

state: file

dest: "{{ pgbouncer_auth_file }}"

mode: 0600

- name: "Stage 2: start pgbouncer"

service:

name: pgbouncer

state: started

enabled: yes

- name: "Stage 1: install pgbouncer"

yum: pkg=pgbouncer state=latest

when: ansible_pkg_mgr == 'yum'

- include: install.yml

- include: configure.yml

- include: post-install.yml

- name: "Stage 3: add logrotate configuration"

template:

src: pgbouncer.j2

dest: /etc/logrotate.d/pgbouncer

owner: root

group: root

mode: 0644

when: enable_logrotate

- name: "Stage 3: reload logrotate"

command: logrotate -s /var/lib/logrotate.status /etc/logrotate.conf

when: enable_logrotate

[databases]

{% for pool in pgbouncer_pools %}

{{ pool.name }} = {{ pool.conninfo }}

{% endfor %}

[pgbouncer]

;;;

;;; Administrative settings

;;;

logfile = {{ pgbouncer_logfile }}

pidfile = {{ pgbouncer_pidfile }}

;;;

;;; Where to wait for clients

;;;

; ip address or * which means all ip-s

listen_addr = {{ pgbouncer_listen_addr }}

listen_port = {{ pgbouncer_listen_port }}

; unix socket is also used for -R.

; On debian it should be /var/run/postgresql

;unix_socket_dir = /tmp

;unix_socket_mode = 0777

;unix_socket_group =

;;;

;;; Authentication settings

;;;

; any, trust, plain, crypt, md5

auth_type = {{ pgbouncer_auth_type }}

auth_file = {{ pgbouncer_auth_file }}

;;;

;;; Users allowed into database 'pgbouncer'

;;;

; comma-separated list of users, who are allowed to change settings

admin_users = {{ pgbouncer_admin_user }}

; comma-separated list of users who are just allowed to use SHOW command

stats_users = {{ pgbouncer_admin_user }},{{ pgbouncer_stats_users |join(",")}}

;;;

;;; Pooler personality questions

;;;

; When server connection is released back to pool:

; session - after client disconnects

; transaction - after transaction finishes

; statement - after statement finishes

pool_mode = {{ pgbouncer_pool_mode }}

;

; Query for cleaning connection immediately after releasing from client.

; No need to put ROLLBACK here, pgbouncer does not reuse connections

; where transaction is left open.

;

; Query for 8.3+:

; DISCARD ALL;

;

; Older versions:

; RESET ALL; SET SESSION AUTHORIZATION DEFAULT

;

; Empty if transaction pooling is in use.

;

server_reset_query = {{ 'DISCARD ALL' if pgbouncer_pool_mode == 'session' else '' }}

;

; Comma-separated list of parameters to ignore when given

; in startup packet. Newer JDBC versions require the

; extra_float_digits here.

;

ignore_startup_parameters = extra_float_digits

;

; When taking idle server into use, this query is ran first.

; SELECT 1

;

;server_check_query = select 1

; If server was used more recently that this many seconds ago,

; skip the check query. Value 0 may or may not run in immediately.

;server_check_delay = 30

;;;

;;; Connection limits

;;;

; total number of clients that can connect

max_client_conn = {{ pgbouncer_max_client_conn }}

; default pool size. 20 is good number when transaction pooling

; is in use, in session pooling it needs to be the number of

; max clients you want to handle at any moment

default_pool_size = {{ pgbouncer_default_pool_size }}

; how many additional connection to allow in case of trouble

reserve_pool_size = 0

; if a clients needs to wait more than this many seconds, use reserve pool

;reserve_pool_timeout = 3

; log if client connects or server connection is made

;log_connections = 1

; log if and why connection was closed

;log_disconnections = 1

; log error messages pooler sends to clients

log_pooler_errors = 1

; If off, then server connections are reused in LIFO manner

;server_round_robin = 0

;;;

;;; Timeouts

;;;

;; Close server connection if its been connected longer.

server_lifetime = 7200

;; Close server connection if its not been used in this time.

;; Allows to clean unnecessary connections from pool after peak.

server_idle_timeout = 30

;; Cancel connection attempt if server does not answer takes longer.

server_connect_timeout = 10

;; If server login failed (server_connect_timeout or auth failure)

;; then wait this many second.

server_login_retry = 10

;; Dangerous. Server connection is closed if query does not return

;; in this time. Should be used to survive network problems,

;; _not_ as statement_timeout. (default: 0)

;query_timeout = 0

;; Dangerous. Client connection is closed if the query is not assigned

;; to a server in this time. Should be used to limit the number of queued

;; queries in case of a database or network failure. (default: 0)

;query_wait_timeout = 0

;; Dangerous. Client connection is closed if no activity in this time.

;; Should be used to survive network problems. (default: 0)

;client_idle_timeout = 0

;; Disconnect clients who have not managed to log in after connecting

;; in this many seconds.

;client_login_timeout = 60

;; Clean automatically created database entries (via "*") if they

;; stay unused in this many seconds.

; autodb_idle_timeout = 3600

;;;

;;; Low-level tuning options

;;;

;; buffer for streaming packets

;pkt_buf = 2048

;; man 2 listen

;listen_backlog = 128

;; networking options, for info: man 7 tcp

;; Linux: notify program about new connection only if there

;; is also data received. (Seconds to wait.)

;; On Linux the default is 45, on other OS'es 0.

;tcp_defer_accept = 0

;; In-kernel buffer size (Linux default: 4096)

;tcp_socket_buffer = 0

;; whether tcp keepalive should be turned on (0/1)

tcp_keepalive = 1

;; following options are Linux-specific.

;; they also require tcp_keepalive=1

;; count of keepaliva packets

;tcp_keepcnt = 0

;; how long the connection can be idle,

;; before sending keepalive packets

;tcp_keepidle = 0

;; The time between individual keepalive probes.

;tcp_keepintvl = 0

;; DNS lookup caching time

;dns_max_ttl = 15

;; DNS zone SOA lookup period

;dns_zone_check_period = 0

{{ pgbouncer_logfile }} {

missingok

daily

notifempty

sharedscripts

create 0640 {{ pgbouncer_admin_user }} {{ pgbouncer_admin_group }}

postrotate

/etc/init.d/pgbouncer -q reload

endscript

}