mastodonへのpgbouncer導入ガイド(非Docker)

以下の公式ガイド翻訳です。私が躓いた点に【注釈】を入れてます。
https://github.com/tootsuite/documentation/blob/master/Running-Mastodon/PgBouncer-guide.md

- - - 

#PgBouncerガイド
PgBouncerはPostgresqlへのコネクションをプールし、効率化するソフトウェアです。このガイドではPgBouncerを導入する手順について説明していきます。もし、もう少しMastodonとPgBouncerに関する事情が知りたい場合は[”Scaling Mastodon”](https://medium.com/@Gargron/scaling-mastodon-1becde463090)に経緯が言及されているので、読んでみてください。

##なぜ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

``` bash:
# ubuntu, debian, etc.
echo -n "passwordmastodon" | md5sum
# macOS, openBSD, etc.
md5 -s "passwordmastodon"
```

得られたテキストの先頭にmd5を追加し、userlist.txtに記載します。

また、PgBouncerが管理するDBに`pgbouncer`という管理者ユーザでログインしたいかと思います。その場合のサンプルは次のようになります。

```text:userlist.txt
"mastodon" "md5d75bb2be2d7086c6148944261a00f605"
"pgbouncer" "md5a45753afaca0db833a6f7c7b2864b9d9"
```
この例では、どちらのパスワードも`password`としています。【後の工程でログインできない場合、ここの書き方が間違っている可能性があります。**パスワードだけをハッシュするのではなく、パスワード＋ユーザ名(例：passwordmastodon)**をハッシュに掛けてください。】

####pgbouncer.iniの設定
[databases]の下に接続したいDBの情報を記載します。接続先DBで利用しているユーザ名/パスワードを使ってPgBouncerに接続させるよう設定していきます。

```bash:pgbouncer.ini
[databases]

mastodon_production = host=127.0.0.1 port=5432 dbname=mastodon_production user=mastodon password=password
```

続いて、`listen_addr`と`listen_port`を指定します。デフォルトで運用しているならそのままでOKです。

```text:pgbouncer.ini
listen_addr = 127.0.0.1
listen_port = 6432
```
【シングルインスタンスで構成している場合はこれで問題ありませんが、複数インスタンスでDBを共有している場合はlisten_addrを`*`にしておくことで、PgBouncerが外部からの接続を受け付けます。】

`auth_type`には`md5`を指定します。(ガイドに則っていれば、`userlist.txt`でmd5を利用してますよね。)

```text:pgbouncer.ini
auth_type = md5
```

管理者ユーザとして`pgbouncer`を指定します。

```text:pgbouncer.ini
admin_users = pgbouncer
```

**次の設定は非常に重要です！**デフォルトのプールモードはセッションベースになっていますが、Mastodonはトランザクションベースの動作を期待します。
言い換えると、Postgresqlのコネクションはトランザクションの発生時に作られ、トランザクションの完了時に破棄されています。
というわけで、`pool_mode`を`session`から`transaction`に変更します。

```text:pgbouncer.ini
pool_mode = transaction
```

もう少し続きます。`max_client_conn`が定義するのはPgBouncer自体が受け付けるコネクション数です。そして、`default_pool_size`によってPostgresqlが実際に受け付けるコネクション数を制限します。(PgHeroで確認できるコネクション数は`default_pool_size`の値の範疇になります。PgBouncerが抱えているコネクション数をPostgreSqlが知らないためです。)

まずはデフォルト値から始めるのがいいでしょう。後から好きな時に増やすことができますから。

```text:pgbouncer.ini
max_client_conn = 100
default_pool_size = 20
```

【これで設定完了です。】忘れずにPgBouncerに設定を反映させておきましょう。

```bash:
service pgbouncer reload
```

####動作確認
まず、PgBouncer経由で通常のPostgresqlに接続できるか確認しましょう。

```bash:
psql -p 6432 -U mastodon mastodon_production
```

パスワードを入力してログインできるか確かめます。
ログインできない場合、次のようにログを確認してみましょう。

```bash:
tail -f /var/log/postgresql/pgbouncer.log
```

###Mastodonの接続先をPgBouncerに変更
【ここまでの作業によって6432番でPgBouncerが接続を待ち受けるようになりましたが、まだMastodonのサービス群は5432番のPostgresqlに直接接続しています。】あなたのMastodonフォルダの`.env.production`を編集しましょう。
まず、次の設定がコメントアウトされていることを確認しましょう。

```text:.env.production
PREPARED_STATEMENTS=false
```

トランザクションベースのプールを使うため、PREPARED_STATEMENTSは利用できません。

もう少し続きます。今はMastodonは5432(Postgresql)に接続しているので、代わりに6432(PgBouncer)に接続させます。

```text:.env.production
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を抜けます。

###参考
* ["Scaling Mastodon"](https://medium.com/@Gargron/scaling-mastodon-1becde463090)
* [PgBouncer documentation](https://pgbouncer.github.io/)
* [Connection Pooling in PostgreSQL using pgbouncer](https://www.slideshare.net/sameerkasi200x/5th-pugs-meetupjuly2014pgbouncer)
* [PgBouncer on the Postgres wiki](https://wiki.postgresql.org/wiki/PgBouncer)