以下の公式ガイド翻訳です。私が躓いた点に【注釈】を入れてます。
https://github.com/tootsuite/documentation/blob/master/Running-Mastodon/PgBouncer-guide.md
#PgBouncerガイド
PgBouncerはPostgresqlへのコネクションをプールし、効率化するソフトウェアです。このガイドではPgBouncerを導入する手順について説明していきます。もし、もう少しMastodonとPgBouncerに関する事情が知りたい場合は”Scaling Mastodon”に経緯が言及されているので、読んでみてください。
##なぜPgBouncerが要るの？
もし、あなたの環境でのPostgresqlへのコネクションが利用可能な値（デフォルトでは100）を超え始めたとしたら、PgBouncerは良い解決策になるでしょう。このガイドはMastodon向けに、一般的な設定について説明していきます。
※コネクション数については、管理者画面のPgHeroから確認できます。
##PgBouncerのインストール
DebianとUbuntuの場合
sudo apt install pgbouncer
再起動
sudo service pbbouncer restart
※このガイドは非Docker環境を想定しています。
##PgBouncerの設定
###パスワードの設定
まず初めに、もしあなたのPostgresql上に存在するmastodonユーザがパスワード無しで設定されているのであれば、何らかの設定が必要です。PgBouncerを空パスワードで利用する方法がなさそうなので。
パスワードのリセット方法は次のようになります。
psql -p 5432 -U mastodon mastodon_production -w
その後
ALTER USER "mastodon" WITH PASSWORD 'password';
完了後は\qでpsqlを抜けます。
###PgBouncerの設定
PgBouncerは二つの設定ファイルがあり、/etc/pgbouncer/直下に格納されています。
- pgbouncer.ini ： 各種設定ファイル
- userlist.txt ：ユーザ名とパスワードのリスト
####userlist.txtの編集
mastodonユーザをuserlist.txtに追加します。
追加時は以下のような形式になります。
"mastodon" "md5d75bb2be2d7086c6148944261a00f605"
さて、このようにmd5ハッシュを利用したパスワード認証を利用します。【前半がユーザ名、後半がハッシュ部となります。間には半角スペースが一つ入ります。】
【ハッシュ部を生成する際は】**password + username**をハッシュにかけ、先頭にmd5を付け加えたものを利用します。ハッシュ生成コマンドは下記のものを参考にしてください。
例)
ユーザ名：mastodon
パスワード：password
# ubuntu, debian, etc.
echo -n "passwordmastodon" | md5sum
# macOS, openBSD, etc.
md5 -s "passwordmastodon"
得られたテキストの先頭にmd5を追加し、userlist.txtに記載します。
また、PgBouncerが管理するDBにpgbouncerという管理者ユーザでログインしたいかと思います。その場合のサンプルは次のようになります。
"mastodon" "md5d75bb2be2d7086c6148944261a00f605"
"pgbouncer" "md5a45753afaca0db833a6f7c7b2864b9d9"
この例では、どちらのパスワードもpasswordとしています。【後の工程でログインできない場合、ここの書き方が間違っている可能性があります。**パスワードだけをハッシュするのではなく、パスワード＋ユーザ名(例：passwordmastodon)**をハッシュに掛けてください。】
####pgbouncer.iniの設定
[databases]の下に接続したいDBの情報を記載します。接続先DBで利用しているユーザ名/パスワードを使ってPgBouncerに接続させるよう設定していきます。
[databases]
mastodon_production = host=127.0.0.1 port=5432 dbname=mastodon_production user=mastodon password=password
続いて、listen_addrとlisten_portを指定します。デフォルトで運用しているならそのままでOKです。
listen_addr = 127.0.0.1
listen_port = 6432
【シングルインスタンスで構成している場合はこれで問題ありませんが、複数インスタンスでDBを共有している場合はlisten_addrを*にしておくことで、PgBouncerが外部からの接続を受け付けます。】
auth_typeにはmd5を指定します。(ガイドに則っていれば、userlist.txtでmd5を利用してますよね。)
auth_type = md5
管理者ユーザとしてpgbouncerを指定します。
admin_users = pgbouncer
**次の設定は非常に重要です！**デフォルトのプールモードはセッションベースになっていますが、Mastodonはトランザクションベースの動作を期待します。
言い換えると、Postgresqlのコネクションはトランザクションの発生時に作られ、トランザクションの完了時に破棄されています。
というわけで、pool_modeをsessionからtransactionに変更します。
pool_mode = transaction
もう少し続きます。max_client_connが定義するのはPgBouncer自体が受け付けるコネクション数です。そして、default_pool_sizeによってPostgresqlが実際に受け付けるコネクション数を制限します。(PgHeroで確認できるコネクション数はdefault_pool_sizeの値の範疇になります。PgBouncerが抱えているコネクション数をPostgreSqlが知らないためです。)
まずはデフォルト値から始めるのがいいでしょう。後から好きな時に増やすことができますから。
max_client_conn = 100
default_pool_size = 20
【これで設定完了です。】忘れずにPgBouncerに設定を反映させておきましょう。
service pgbouncer reload
####動作確認
まず、PgBouncer経由で通常のPostgresqlに接続できるか確認しましょう。
psql -p 6432 -U mastodon mastodon_production
パスワードを入力してログインできるか確かめます。
ログインできない場合、次のようにログを確認してみましょう。
tail -f /var/log/postgresql/pgbouncer.log
###Mastodonの接続先をPgBouncerに変更
【ここまでの作業によって6432番でPgBouncerが接続を待ち受けるようになりましたが、まだMastodonのサービス群は5432番のPostgresqlに直接接続しています。】あなたのMastodonフォルダの.env.productionを編集しましょう。
まず、次の設定がコメントアウトされていることを確認しましょう。
PREPARED_STATEMENTS=false
トランザクションベースのプールを使うため、PREPARED_STATEMENTSは利用できません。
もう少し続きます。今はMastodonは5432(Postgresql)に接続しているので、代わりに6432(PgBouncer)に接続させます。
DB_HOST=localhost
DB_USER=mastodon
DB_NAME=mastodon_production
DB_PASS=password
DB_PORT=6432
【設定変更後、(サービスの一時停止を伴います)sudo systemctl restart mastodon-*しておきましょう。】
####PgBouncerの管理
もっとも簡単な方法はサービスの再起動です。
sudo service pgbouncer restart
もしPgBouncer管理者ユーザを設定していた場合は、管理者として接続することができます。
psql -p 6432 -U pgbouncer pgbouncer
接続後にRELOAD;してから\qでpsqlを抜けます。
###参考