I have pgbouncer setup in front of 3 postgresql servers. Everything is running fine and pgbouncer is handling the pools. However, when I go to create a database, I get an error that template1 is in use and the database cannot be created. When I do a select on the pg_activity, I can see there is an active connection from my pgbouncer server. I have check the SHOW CLIENTS on pgbouncer and there are no other client connections so pgbouncer seems to be making and keeping this connection to template1. When I run SHOW POOLS on pgbouncer, it does show a pool created to database template1. Why is pgbouncer holding this open, and in effect locking me from creating databases, and how can I fix it?
pgbouncer=# show pools;
database | user | cl_active | cl_waiting | cl_active_cancel_req | cl_waiting_cancel_req | sv_active | sv_active_cancel | sv_being_canceled | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------+-----------+-----------+------------+----------------------+-----------------------+-----------+------------------+-------------------+---------+---------+-----------+----------+---------+------------+-----------
pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement
root | root | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | session
template1 | postgres | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | session
(3 rows)
pgbouncer.ini
;;; This is an almost minimal starter configuration file that only
;;; contains the settings that are either mandatory or almost always
;;; useful. All settings show their default value.
[databases]
* = host=localhost port=9999
[pgbouncer]
;; required in daemon mode unless syslog is used
;logfile =
;; required in daemon mode
pidfile = /usr/local/pgbouncer/pgbouncer.pid
syslog = 1
;; set to enable TCP/IP connections
listen_addr = *
;; PgBouncer port
listen_port = 5432
;; some systems prefer /var/run/postgresql
;unix_socket_dir = /tmp
;; change to taste
auth_type = trust
;; probably need this
auth_file = /usr/local/pgbouncer/etc/userlist.txt
;; pool settings are perhaps best done per pool
;pool_mode = session
;default_pool_size = 20
;; should probably be raised for production
max_client_conn = 10000
ignore_startup_parameters = extra_float_digits
admin_users = pgbouncer,postgres,root
The pool is created when someone or something connects to template1, so you should prevent that.
Add a line to pg_hba.conf that prevents connections to the template1 database from the pgBouncer machine. That should take care of the problem, and whatever tries to connect to template1 will get an error. It might be a misguided administrator.