PgBouncer: Como Gerenciar Conexões em VPS com PostgreSQL

O Desafio de Escalabilidade em Bancos de Dados

A arquitetura moderna de software, especialmente em ambientes de microserviços e aplicações web escaláveis, impõe uma demanda constante por conexões simultâneas ao banco de dados. O PostgreSQL, reconhecido por sua robustez, integridade transacional ACID e suporte avançado a tipos de dados complexos, possui um limite natural para o número de conexões concorrentes que pode atender eficientemente.

Cada conexão ativa no servidor do banco de dados consome memória RAM significativa (geralmente entre 5MB a 10MB por backend, dependendo da configuração e carga) e ciclos da CPU para gerenciamento de contexto. Em uma VPS com recursos limitados ou mesmo em instâncias cloud bem dimensionadas, o número máximo de conexões permitidas (max_connections) pode se tornar um gargalo crítico. Aumentar esse valor indiscriminadamente não é a solução ideal, pois leva ao esgotamento da memória e à degradação severa do desempenho devido à troca de contexto excessiva.

Aqui entra o PgBouncer, um gerenciador de conexões leve e rápido para PostgreSQL. Ele atua como um proxy entre sua aplicação e o banco de dados, mantendo um pool de conexões ativas reutilizáveis. Dessa forma, múltiplas solicitações da aplicação podem compartilhar uma única conexão física com o PostgreSQL, otimizando o uso de recursos e permitindo que seu sistema escale muito além dos limites nativos do banco de dados.

Arquitetura e Modos de Operação

Antes de prosseguir com a instalação, é fundamental compreender os três modos de operação do PgBouncer, pois a escolha impacta diretamente na compatibilidade com sua aplicação:

• Session Mode (Modo Sessão): É o modo mais seguro e padrão. Uma conexão do cliente fica vinculada a uma conexão de servidor até que seja fechada. Ideal para a maioria das aplicações, garantindo que transações longas sejam executadas corretamente.
• Transaction Mode (Modo Transação): Permite o compartilhamento agressivo de conexões. Uma conexão do cliente usa uma conexão de servidor apenas durante a execução de uma única transação SQL. Ao final da transação, a conexão é retornada ao pool. Requer que as aplicações não mantenham estado entre consultas e suportem reutilização imediata.
• Statement Mode (Modo Instrução): Similar ao Transaction Mode, mas libera a conexão do servidor após cada instrução SQL individual. É o modo mais eficiente em termos de recursos, mas oferece menos garantia de isolamento e compatibilidade com comandos que dependem de estado de sessão.

Para a maioria dos cenários corporacionais e desenvolvimento padrão, o Session Mode é a recomendação inicial. O Transaction Mode deve ser utilizado apenas após testes rigorosos de compatibilidade com sua aplicação.

Instalação do PgBouncer no Linux

A instalação varia conforme a distribuição Linux utilizada em sua VPS. Abaixo, apresentamos os passos para as distribuições mais comuns:

Ubuntu/Debian

No Debian e Ubuntu, o pacote está disponível nos repositórios padrão. Execute os seguintes comandos com privilégios de root ou sudo:

sudo apt update
sudo apt install pgbouncer

RHEL/CentOS/Amazon Linux

Em sistemas baseados em RHEL, o PgBouncer pode estar disponível no repositório EPEL ou diretamente nas versões mais recentes do AppStream:

sudo yum install epel-release
sudo yum install pgbouncer

Compilação a partir do Código Fonte

Se você precisar de uma versão mais recente que não esteja nos repositórios oficiais, pode compilar manualmente:

git clone https://github.com/pgbouncer/pgbouncer.git
cd pgbouncer
./configure
make
sudo make install

Configuração do PgBouncer

O arquivo de configuração principal é geralmente localizado em /etc/pgbouncer/pgbouncer.ini. É crucial editar este arquivo com cuidado, pois erros de sintaxe impedirão a inicialização do serviço.

Abra o arquivo para edição:

sudo nano /etc/pgbouncer/pgbouncer.ini

Seção [pgbouncer]

Esta seção controla os parâmetros globais do gerenciador de conexões. Configure conforme seu ambiente:

[pgbouncer]
listen = 127.0.0.1
port = 6432
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

Explicação dos Parâmetros Chave:

• listen e port: Define onde o PgBouncer escuta. Por padrão, ele fica no localhost na porta 6432.
• auth_type: Tipo de autenticação. Use trust se estiver em ambiente interno seguro, ou md5 se precisar verificar senhas contra o arquivo userlist.txt.
• pool_mode: Define o modo de operação (session, transaction, statement). Comece com session.
• max_client_conn: Número máximo total de conexões de clientes permitidas.
• default_pool_size: Número de conexões a manter abertas para cada banco de dados definido. Ajuste este valor baseado na memória disponível. Uma regra geral é: (Memória RAM Total - Memória do OS) / (Tamanho médio por conexão PostgreSQL).
• reserve_pool_size: Conexões reservadas para conectar clientes em modo de espera quando o pool está cheio.

Seção de Banco de Dados

Defina os bancos de dados que o PgBouncer irá gerenciar. O nome à esquerda é o nome lógico que sua aplicação usará; o valor à direita contém as credenciais e endereço do servidor PostgreSQL real.

[databases]
meubanco = host=127.0.0.1 port=5432 dbname=meubanco

Se o banco de dados estiver em um servidor remoto, altere host para o IP ou hostname do servidor PostgreSQL.

Arquivo de Usuários (userlist.txt)

Se você configurou auth_type = md5, precisa criar o arquivo /etc/pgbouncer/userlist.txt. O formato é simples:

"username" "md5hashedpassword"

Para gerar a senha MD5 correta para o PostgreSQL/PgBouncer, você pode usar uma função SQL no próprio banco de dados ou ferramentas de linha de comando. No PostgreSQL, execute:

SELECT md5('sua_senha' || 'seu_usuario');

Copie o hash resultante e coloque-o no arquivo userlist.txt.

Otimização do PostgreSQL para Trabalho com PgBouncer

A configuração do PgBouncer é apenas metade da equação. O servidor PostgreSQL também deve ser tuneado para lidar eficientemente com o pool de conexões.

1. Ajuste do max_connections: Você pode reduzir drasticamente o max_connections no postgresql.conf. Como o PgBouncer gerencia as conexões, você não precisa ter um número alto de backends reais. Um valor entre 100 e 200 é frequentemente suficiente, dependendo do tamanho do pool configurado no PgBouncer.
2. Configuração de Memória: Com menos conexões ativas, você pode alocar mais memória para o shared_buffers e wal_buffers, melhorando o desempenho geral das queries.
3. Timeouts: Configure timeouts adequados no PgBouncer para liberar conexões ociosas rapidamente. Parâmetros como server_idle_timeout ajudam a manter o pool limpo.

Iniciando e Habilitando o Serviço

Após salvar as configurações, inicie o serviço do PgBouncer:

sudo systemctl restart pgbouncer
sudo systemctl enable pgbouncer

Verifique se o serviço está rodando corretamente:

sudo systemctl status pgbouncer

Para validar a configuração sem reiniciar, use:

sudo pgbouncer -c /etc/pgbouncer/pgbouncer.ini --check

Conectando sua Aplicação ao PgBouncer

Agora que o proxy está ativo, suas aplicações devem apontar para a porta do PgBouncer (geralmente 6432) em vez da porta padrão do PostgreSQL (5432).

Exemplo com PSQL

psql -h 127.0.0.1 -p 6432 -U usuario -d meubanco

Exemplo em Python (psycopg2)

import psycopg2

conn = psycopg2.connect(
    host="127.0.0.1",
    port=6432,
    database="meubanco",
    user="usuario",
    password="senha"
)

Exemplo em Node.js (pg)

const { Client } = require('pg');

const client = new Client({
  host: '127.0.0.1',
  port: 6432,
  database: 'meubanco',
  user: 'usuario',
  password: 'senha'
});

Monitoramento e Troubleshooting

O PgBouncer possui uma interface administrativa integrada que permite monitorar o estado do pool em tempo real. Para acessá-la, conecte-se ao banco de dados pgbouncer na porta 6432:

psql -h 127.0.0.1 -p 6432 -U pgbouncer -d pgbouncer

Dentro do console, execute o comando SHOW POOLS;. Você verá informações detalhadas sobre:

• dname: Nome do banco de dados.
• user: Usuário conectado.
• cl_active: Clientes ativos atualmente conectados ao servidor.
• cl_waiting: Clientes aguardando uma conexão disponível.
• sv_active: Servidores (backends PostgreSQL) ativos.
• sv_idle: Servidores ociosos prontos para uso.

Se você observar um aumento constante em cl_waiting, significa que seu pool está saturado. As soluções incluem aumentar o default_pool_size no PgBouncer ou otimizar as consultas lentas no PostgreSQL para liberar conexões mais rapidamente.

Considerações Finais sobre Segurança e Alta Disponibilidade

Segurança: O PgBouncer deve ser executado em uma rede segura. Se exposto publicamente, certifique-se de usar auth_type = md5 ou scram-sha-256 e restrinja o acesso via firewall (iptables/firewalld) apenas aos IPs das suas aplicações.

Alta Disponibilidade: Para ambientes críticos, configure múltiplas instâncias do PgBouncer atrás de um balanceador de carga (como HAProxy ou NGINX). Isso garante que, se uma instância do gerenciador falhar, as conexões sejam redirecionadas para outra, mantendo a disponibilidade do banco de dados.

A implementação correta do PgBouncer é um passo essencial na jornada de otimização de infraestrutura de bancos de dados. Ele não apenas protege seu PostgreSQL contra o esgotamento de recursos, mas também oferece uma camada adicional de controle e visibilidade sobre as conexões da sua aplicação. Ao seguir estas diretrizes, você estará preparado para escalar suas soluções com confiança e eficiência.