pgBouncerでPostgreSQLの接続プールを設定する手順｜最大接続数の削減とpgbouncer.iniの書き方

この記事のポイント

・pgBouncerをアプリとPostgreSQLの間に挟むことで、PostgreSQL本体の同時接続数を削減できる
・pgbouncer.iniの[databases]と[pgbouncer]セクションを設定し、userlist.txtでパスワードを登録する
・pool_modeはtransactionが最も効率的だが、prepared statementの扱いに注意が必要
・SHOW STATSとSHOW POOLSでリアルタイムに接続状況を監視できる

# パッケージを確認
dnf list pgbouncer

# インストール
sudo dnf install -y pgbouncer

# バージョン確認
pgbouncer --version

PgBouncer 1.22.0

sudo apt update
sudo apt install -y pgbouncer

pgbouncer --version

# Rocky Linux / RHEL
ls /etc/pgbouncer/

# Ubuntu
ls /etc/pgbouncer/

[databases]
# エイリアス名 = host=接続先 port=ポート dbname=DB名
myapp = host=127.0.0.1 port=5432 dbname=myapp_db

# 全DB名でそのまま受け付ける場合（ワイルドカード）
# * = host=127.0.0.1 port=5432

[pgbouncer]
# pgBouncerがリッスンするアドレスとポート
listen_addr = 127.0.0.1
listen_port = 6432

# 認証方式（scram-sha-256 または md5）
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# プールモード（後述）
pool_mode = transaction

# DB単位の最大コネクション数（PostgreSQL側の接続数上限）
max_client_conn = 200
default_pool_size = 20

# ログ設定
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

# 管理DB（SHOW STATS等で使用）
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

# PostgreSQLに接続
sudo -u postgres psql

# pgBouncerが使う接続ユーザーのハッシュを確認
SELECT usename, passwd FROM pg_shadow WHERE usename = 'myapp_user';

  usename   |                                                                passwd
------------+------------------------------------------------------------------------
 myapp_user | SCRAM-SHA-256$4096:xxxxxxxxxxxxxxxx==$yyy...==:zzz...==
(1 row)

# /etc/pgbouncer/userlist.txt の形式
# "ユーザー名" "パスワードハッシュ"
"myapp_user" "SCRAM-SHA-256$4096:xxxxxxxxxxxxxxxx==$yyy...==:zzz...=="
"pgbouncer_admin" "md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

pgbouncer -d /etc/pgbouncer/pgbouncer.ini
# エラーなしであれば設定ファイルは正常

# 起動
sudo systemctl start pgbouncer

# 自動起動を有効化
sudo systemctl enable pgbouncer

# ステータス確認
sudo systemctl status pgbouncer

* pgbouncer.service - A lightweight connection pooler for PostgreSQL
     Loaded: loaded (/usr/lib/systemd/system/pgbouncer.service; enabled; preset: disabled)
     Active: active (running) since Thu 2026-09-25 10:12:34 JST; 3s ago
   Main PID: 12345 (pgbouncer)
      Tasks: 2 (limit: 23156)
     Memory: 2.3 M
        CPU: 8ms
     CGroup: /system.slice/pgbouncer.service
             +--12345 pgbouncer /etc/pgbouncer/pgbouncer.ini

# pgBouncer経由でPostgreSQLへ接続（ポート6432を指定）
psql -h 127.0.0.1 -p 6432 -U myapp_user -d myapp

# 接続できたら実際にPostgreSQL本体に繋がっているか確認
myapp=> SELECT version();

                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20231218 (Red Hat 11.4.1-3), 64-bit
(1 row)

# admin_usersに設定したユーザーでpgbouncerDBへ接続
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

pgbouncer=# SHOW POOLS;

  database  |   user    | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait
------------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------
 myapp      | myapp_user|         8 |          0 |         8 |        12 |       0 |         0 |        0 |       0
(1 row)

pgbouncer=# SHOW STATS;

 database | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | avg_xact_time | avg_query_time
----------+------------------+-------------------+----------------+------------+-----------------+------------------+---------------+----------------
 myapp    |             2481 |              8935 |       29843291 |   87654321 |      3271845201 |       1248901234 |          1318 |            139
(1 row)

# pgbouncer.iniの[pgbouncer]セクションに追加
server_reset_query = DISCARD ALL

# database.yml（Rails）
production:
  prepared_statements: false

# systemd経由でリロード
sudo systemctl reload pgbouncer

# または管理DBから
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "RELOAD;"

# リアルタイムでログを追う
sudo journalctl -u pgbouncer -f

# またはログファイル直接
sudo tail -f /var/log/pgbouncer/pgbouncer.log