PostgreSQL connection pooling com pgbouncer

conf_pg_bouncer_ubuntu.txt

Um método para otimizar as conexões com o banco de dados é estabelecer um pool de conexão, caso sua aplicação
 não faça isto nativamente você pode usar alguma ferramenta como o pgbouncer [1].

O principal motivo para manter um pool de conexões ativas com o banco de dados, é que o processo
de criar uma conexão com o banco de dados e posteriormente elimina-la gasta recursos e leva algum tempo, 
o que pode ser pouco para uma conexão, mas para apps onde a conexão / desconexão ocorre com muita
frequência (como em aplicações web) manter um pool de conexões ativas, pode economizar algum tempo
entre cada solicitação.

Um pool de conexões trabalha de maneira muito simples, após a aplicação criar e usar a conexão com o 
banco de dados, a mesma é colocada em espera no pool (similar a uma fila), assim, quando a aplicação precisar
criar mais uma conexão, primeiro será verificado se existe uma conexão já pronta esperando para ser usada, 
caso sim, é usado esta, caso não cria-se uma nova e então guarda-se esta no pool após usa-la.

[1] http://wiki.postgresql.org/wiki/PgBouncer


Neste gist citarei as principais configurações do do pgbouncer no ubuntu,
para mais detalhes sobre a configuração acesse http://pgbouncer.projects.postgresql.org/doc/config.html


# instalação
$ sudo apt-get install pgbouncer

# configuração
$ sudo vi /etc/pgbouncer/pgbouncer.ini

# no arquivo de configuração, a primeira variável representa o nome do db que será usado na uri de conexão 
pelo cliente, é interessante saber que se usado um nome de usuário, então todas as conexões serão feitas com
este usuário, e será mantido apenas um pool, do contrário cada usuário repassado na conexão pelo cliente, 
terá sua propria poll

mydb_name = host=localhost port=5432 dbname=mydb_name user=postgres password=my_pass

# defina a porta e endereço que o pgbouncer escutará os clientes, use o endereço 127.0.0.1 para escutar
somente a maquina local, ou outro endereço ip ou * para todos endereços, note que a porta informada
deverá ser usada na uri de conexão do cliente, certifique-se que esta porta é diferente da porta usada 
pelo postgres, e que sua uri esta usando esta porta (abaixo)

listen_addr = *
listen_port = 6432

# caso a versão do pg seja < 8.3) sete server_reset_query como
server_reset_query = RESET ALL; SET SESSION AUTHORIZATION DEFAULT;

# senão (8.3+)
server_reset_query = DISCARD ALL;

# em servidores de produção você pode querer desabilitar estes logs, para evitar trabalho adicional
set log_connections = 0
log_disconnections = 0

# caso o pgbouncer e pg estejam instalados no mesmo servidor, você pode usar o mesmo arquivo que o pg usa para 
armazenar os usuários
auth_file = /var/lib/postgresql/8.2/main/global/pg_auth

# caso contrario, defina no arquivo userlist.txt os usuários, ex: "user" "pwd" (com as aspas)
auth_file = /etc/pgbouncer/userlist.txt

#adicione aqui os usuários (separado por ,) que terão privilégio para alterar os dados do banco
admin_users = postgres


"""
aqui você define quando uma conexão poderá será jogada para o pool, os valores possíveis são:
 - session (por padrão e o mais leve) somente permite reusar a conexão após o cliente pedir para terminar a conexão, 
 - transaction reusa após uma transação em aberto ser fechada, ou 
 - statement (o mais agressivo, você pode querer estuda-lo bem, antes de usa-lo :) joga a conexão
para o pool logo após a execução de uma query, não tenho certeza mas acho que neste modo transações
manuais não funcionam"""
pool_mode = transaction

# digite :wq para gravar e sair do editor
        
# configure para o pgbouncer iniciar com o sistema
sudo vi /etc/default/pgbouncer

# defina start como 1 :)
set START=1

# descomente a linha para usar as configurações personalizadas
OPTS="-d /etc/pgbouncer/pgbouncer.ini"

# inicio do pgbouncer
sudo service pgbouncer start

# se precisar mudar alguma configuração use, reload para recarregar as configurações
sudo service pgbouncer reload

# ok, agora suas apps podem se conectar ao pgbouncer (pela porta 6432) que ele manterá o pool :), 
ex para conectar uma app python (note que ao fechar a conexão, ela continuará no poll):

$ python
>>> import psycopg2
>>> c = psycopg2.connect("dbname=mydb_name user=postgres password=user_pwd port=6432")
>>> c.close()

# Caso esteja ocorrendo algum erro na conexão, tente verificar o log do pgbouncer
tail -f /var/log/postgresql/pgbouncer.log