pgBouncer on Ubuntu — PostgreSQLコネクションプーリングの設定

PostgreSQLに100本のコネクションが来ると、サーバーのメモリが一気に逼迫します。pgBouncer は「コネクションプーリング」でこの問題を解決するミドルウェアで、アプリ側は100接続していても、実際にPostgreSQLに渡す接続を20本以下に抑えられます。

Ubuntu 24.04 LTS では apt install pgbouncer 一発でインストールでき、設定ファイルを2〜3行書くだけで動き始めます。本記事では、ubuntu:24.04 の Docker コンテナで実際にインストール・起動を行い、その実ログと設定ファイルの中身を余すことなくお見せします。

目次

1. pgBouncer とは？なぜ必要か
2. Ubuntu 24.04 へのインストール手順
3. pgbouncer.ini の設定
4. userlist.txt の作り方
5. 起動と動作確認
6. SHOW コマンドでプールの状態を確認
7. pool_mode の選び方
8. よくあるエラーと解決策
9. まとめ

pgBouncer とは？なぜ必要か

PostgreSQL は接続ごとにプロセスを fork します。接続が増えるほどメモリ消費が増え、1,000接続ともなると数 GB のメモリが接続のためだけに使われます。

pgBouncer はアプリと PostgreSQL の間に立つ軽量な接続プロキシです。アプリは pgBouncer（ポート 6432）に接続し、pgBouncer は必要なときだけ PostgreSQL（ポート 5432）への接続を確立します。接続数を大幅に削減できるので、特にコネクション数が多くなりがちな Web アプリや API サーバーに効果的です。

上の表は ubuntu:22.04 と ubuntu:24.04 の公式 Docker イメージで apt-cache policy pgbouncer を実行した結果です。Ubuntu 24.04 では pgBouncer 1.22.0 が標準リポジトリから入ります（Ubuntu 22.04 は 1.16.1）。PostgreSQL も 14 から 16 に上がっており、最新環境で始めるなら 24.04 が断然おすすめです。

Ubuntu 24.04 へのインストール手順

手順1：パッケージリストを更新する

apt update を忘れると古いキャッシュから検索してしまい、最新バージョンが入らないことがあります。必ず最初に実行してください。

手順2：pgBouncer をインストールする

インストールが完了すると、systemd サービスが自動で登録されます。設定ファイル /etc/pgbouncer/pgbouncer.ini と認証ファイル /etc/pgbouncer/userlist.txt も自動で作成されます。

手順3：バージョンを確認する

ubuntu:24.04 の Docker コンテナで実際に確認した出力です。PgBouncer 1.22.0 が入り、systemd: yes と表示されていることからシステムの systemd と連携できることがわかります。

手順4：設定ファイルの場所を確認する

両ファイルのオーナーは postgres ユーザーです。pgbouncer.ini は約10KB のコメント入り設定ファイルで、userlist.txt は空の状態で作成されます。

pgbouncer.ini の設定

設定ファイルは3つのセクションに分かれています。最低限押さえるべき設定を解説します。

①[databases] セクション — 接続先DBを指定する

[databases] セクションに「pgBouncer 側の DB 名 = 接続先」を書きます。上の例では、アプリが mydb という名前で接続してくると、pgBouncer が 127.0.0.1:5432 の mydb データベースへ転送します。

②[pgbouncer] セクション — 主要パラメータ

デフォルトの /etc/pgbouncer/pgbouncer.ini で有効になっている設定（コメントアウトなし）は以下の通りです（ubuntu:24.04 で確認）：

デフォルトでは listen_addr = localhost かつ listen_port = 6432 でローカルのみ受け付けます。外部からの接続を受け付けたい場合は listen_addr = * に変更し、ファイアウォールも合わせて設定してください。

userlist.txt の作り方

ここが最もつまずきやすいポイントです。pgBouncer は独自の認証ファイル userlist.txt を使います。いちばん確実なのは、PostgreSQL の pg_shadow に保存されているパスワード文字列をそのままコピーする方法です。これなら次に説明する md5／SCRAM のどちらでも確実に一致します。

pg_shadow からパスワード文字列を取得する

取得した値を /etc/pgbouncer/userlist.txt に以下の形式で書きます（ユーザー名・パスワード文字列の両方をダブルクォートで囲むのがポイント）：

このとき pgBouncer 側は auth_type = md5 のままで構いません。auth_type = md5 は「md5 または SCRAM の格納形式を自動で扱う」設定なので、pg_shadow の SCRAM 文字列をそのまま貼れば認証が通ります。実際にこの手順で、6432 ポート経由の接続が成功するのを確認しました（前掲の SELECT version()）。

md5 でユーザーを作っている場合

古い設定（password_encryption = md5）でユーザーを作っている、あるいは互換性のため md5 で揃えたい場合は、シェルで md5 ハッシュを計算して書けます：

PostgreSQL の MD5 認証は「パスワード + ユーザー名」を連結した文字列のハッシュです。パスワードだけのハッシュでは認証が通りませんので注意してください。ただし前述の通り PostgreSQL 16 のデフォルトは SCRAM なので、まずは pg_shadow の値をそのままコピーする方法を試すのが確実です。

起動と動作確認

手順1：pgBouncer を起動する

enable --now で自動起動の有効化と即時起動を同時に行えます。

手順2：サービス状態を確認する

systemd で動いている VPS では、systemctl status で稼働状態を確認できます（以下は systemd ホストでの表示例です）：

Active: active (running) と表示されていれば起動成功です。systemd を PID 1 で持たない Docker コンテナでは、代わりに ps で直接プロセスを確認しました。ubuntu:24.04 コンテナでの実出力です：

RSS（実メモリ）は 約2.6MB（2628KB）。pgBouncer は非常に軽量な常駐プロセスであることが実測からも分かります。

続いて、実際に ubuntu:24.04 のコンテナで pgBouncer を起動したときのログです（/var/log/postgresql/pgbouncer.log の実出力）：

起動直後に process up: PgBouncer 1.22.0 と出れば正常です。Unix ソケット（/var/run/postgresql/.s.PGSQL.6432）と TCP（ポート 6432）の両方でリッスンします。

手順3：接続テストをする

ubuntu:24.04 のコンテナで PostgreSQL（testdb / ユーザー pgtest）を起動し、pgBouncer の 6432 ポート経由で接続した実出力です：

PostgreSQL の 5432 ではなく、pgBouncer の 6432 ポートに接続しているのに、ちゃんと PostgreSQL 16.14 が返ってきました。アプリの接続先をこのポートに変えるだけで、コネクションプーリングが有効になります。

SHOW コマンドでプールの状態を確認

pgBouncer には管理用の仮想データベース pgbouncer があります。SHOW コマンドでプールの状態をリアルタイムに確認できます（管理コンソールに入るには、設定ファイルの admin_users / stats_users にユーザーを登録しておきます）。

ここからは 実際にプーリングが効いている瞬間を見せます。ubuntu:24.04 のコンテナで pool_mode = transaction ・ default_pool_size = 5 に設定し、12 本のクライアントから同時にトランザクションを投げた状態で SHOW POOLS; を実行した実出力です（列は見やすいよう一部抜粋）：

この出力の見方：

• cl_active：いまクエリを処理中のクライアント数（5本）
• cl_waiting：サーバー接続の空きを待っているクライアント（7本）
• sv_active：PostgreSQL に実際に張っている接続数（5本だけ）
• maxwait：いちばん長く待っているクライアントの待機秒数

クライアントは合計 12 本（処理中5 ＋ 待機7）接続しているのに、PostgreSQL への実接続は default_pool_size で決めた 5 本で打ち止めになっています。残り7本は順番待ちで、空いた接続を使い回します。これがコネクションプーリングの効果で、12本のクライアントを5接続でさばけているわけです。SHOW CLIENTS; で数えると確かに 12 行返ってきました。

トランザクションがすべて終わると、サーバー接続は sv_idle（アイドル）としてプールに残り、次の接続に再利用されます。続いて累積統計を SHOW STATS; で確認します（縦表示 -x・抜粋）：

SHOW STATS; でトランザクション数（total_xact_count）・クエリ数・転送バイト数（total_received / total_sent）などの累積統計が見られます。avg_xact_time はマイクロ秒単位の平均トランザクション時間です。設定変更後は RELOAD; で設定を再読み込みできます（pgBouncer を再起動する必要はありません）。

pool_mode の選び方

pgBouncer の pool_mode は3種類あります。接続の解放タイミングが違い、アプリへの影響も大きく変わります。

• session（デフォルト）：クライアントが切断したときにサーバー接続を解放。既存アプリを変更せずに使えますが、接続節約効果は低めです。最初の移行先として安全。
• transaction（Webアプリに推奨）：トランザクション終了後に解放。接続節約効果が高く、Webアプリやマイクロサービスとの相性が最も良いモードです。ただし SET や LISTEN/NOTIFY、PREPARE はトランザクション外では使えません。
• statement：SQL ステートメント終了後に解放。制約が多すぎるため実用上はほぼ使われません。

新規で構築するWebアプリには transaction を選び、既存アプリを移行する場合はまず session で動作確認してから transaction に移行するのが安全です。

よくあるエラーと解決策

①「no such database」— データベース名が一致しない

pgbouncer.ini の [databases] セクションに接続先 DB の設定がないか、DB 名のタイポです。設定後は sudo systemctl reload pgbouncer または管理 DB で RELOAD; を実行してください。

②「auth failed for user」— 認証エラー

userlist.txt の MD5 ハッシュが正しくない場合です。PostgreSQL の MD5 は「パスワード＋ユーザー名」の連結ハッシュなので、パスワードだけの MD5 では通りません。pg_shadow から直接取得した値を使うのが確実です。

③「client_login_timeout (server down)」— バックエンドが落ちている

pgBouncer のログに以下のエラーが出る場合：

これは pgbouncer.ini に書いたホスト・ポートで PostgreSQL が動いていない状態です。実際に ubuntu:24.04 のコンテナで pgBouncer の起動ログを取得した際に確認した実ログです。service postgresql status で PostgreSQL の起動状態を確認してください。

④設定変更が反映されない

設定ファイルを変更した後は再起動ではなく reload で済みます：

まとめ

Ubuntu 24.04 での pgBouncer セットアップをまとめます（ubuntu:24.04 Docker コンテナで実際に確認した手順です）。

pgBouncer のセットアップで一番詰まるのは userlist.txt のMD5ハッシュ形式です。pg_shadow から直接コピーするのが確実で、手動計算だとパスワード+ユーザー名の順番を間違えがちです。

本格的に PostgreSQL を運用するなら、VPS 上に専用サーバーを立てるとパフォーマンスが格段に上がります。VPS 各社の性能比較は以下の記事で実測データを紹介しています。

[LINK_bench_vps ARTICLE]