PostgreSQLの冗長構成の構築(同期ストリーミングレプリケーション)

PostgreSQLの標準機能であるReplication機能を使って冗長構成(マスタ:スタンバイ＝1:1)を構築してみました。これまでAWSのコンソールからポチポチして冗長構成を組んだ経験しかないので、今回は手動で構築していきます。自動フェイルオーバーは含みません。

## レプリケーションの方法
PostgreSQLの標準機能のレプリケーションの方法はログシッピングとストリーミングレプリケーションの2種類があります。標準のレプリケーション機能では、自動でフェイルオーバーをする機能はないため、自動でフェイルオーバーを行いたい場合は、HAクラスタを構築するミドルウェアを導入する必要があります。

### ログシッピング
ログシッピングはWAL(Write ahead log)という更新に関するログファイルをスタンバイに転送し、WALを基にスタンバイのデータを更新することでマスタとスタンバイで同期を行います。

### ストリーミングレプリケーション
ログシッピングはWALをファイル単位でスタンバイに転送するのに対して、ストリーミングレプリケーションはWALをレコード単位で転送します。そのため、ログシッピングより同期の遅延が少ないです。

## その他
もともとPostgreSQLは冗長構成を構築する機能を標準で実装しないという方針をとっていました。そのため、PosrgreSQL 9.0以前で冗長構成を構築するためには、HA(High Avalilable)クラスタを構築するミドルウェアが必要で、多くのミドルウェアが開発されています。しかし9.0からReplication機能が入りました。
[PosgreSQL Wiki](https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling#Comparison_matrix)で紹介されているので、興味のある人は見てください。Pgpool-IIが一番有名なんじゃないかと思います。

## 環境
Ubuntsu 14.04
PostgreSQL 9.6

## 手順
1. VMの準備
2. マスタ(db1)の設定
3. スタンバイ(db2)の設定

## VMの準備
Vagrantを使ってVMを用意します。ひとつのVagrantfileで複数のVMを起動できるので便利です。
[Vagrantfile](https://github.com/kouyaf77/postgresql-replication-sample/blob/master/Vagrantfile)

## PostgreSQL 9.6のインストール
`db1`と`db2`の両方にPostgreSQLをインストールします。

```sh
$ sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main"
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
sudo apt-key add -
$ sudo apt-get update
$ sudo apt-get install postgresql-9.6
```

## マスタ(db1)の設定
`db1`に入り`postgres`ユーザに切り替えます。

```
$ vagrant ssh db1
$ sudo su - postgres
```

レプリケーション用のユーザ`repl_user`を作成します。ユーザ名、パスワードは任意です。

```
$ psql
psql (9.6.3)
Type "help" for help.

postgres=# CREATE ROLE repl_user LOGIN REPLICATION PASSWORD 'password';
```

どのユーザがどのIPから接続できるの設定が書かれている`pg_hba.conf`を修正します。念のため、デフォルトの設定は別ファイルにバックアップをとります。

```
$ cp /etc/postgresql/9.6/main/pg_hba.conf /etc/postgresql/9.6/main/pg_hba.conf.org
$ vim /etc/postgresql/9.6/main/pg_hba.conf
```

以下を追加してください。
```
host    replication     repl_user        192.168.50.2/32            md5
```

次に`postgresql.conf`を修正します。こちらも念のためバックアップをとっておきます。

```
$ cp /etc/postgresql/9.6/main/postgresql.conf /etc/postgresql/9.6/main/postgresql.conf.org
$ vim /etc/postgresql/9.6/main/postgresql.conf
```

以下を追加してください。

```
listen_addresses = '*'
wal_level = replica
synchronous_commit = on
max_wal_senders = 2
synchronous_standby_names = 'db2'
```
`listen_addresses = '*'`はなぜ必要なのかわからなかったのですが、スタンバイからのベースバックアップの取得ができなかったので追加しました。

9.6から`wal_leve`の設定が変わりました。`archive`、`hot_standby`がなくなり`replica`に変更されました。使い分けは以下の通りです。

|wal_level  |説明 |
|---|---|
|minimal  |クラッシュまたは即時停止から回復するのに必要な情報のみ書き出します。  |
|replica  |WALアーカイビングに必要なログ出力、およびスタンバイサーバ上で読み取り専用問い合わせを実行するために必要な情報を追加します。  |
|logical|更にロジカルデコーディングをサポートするのに必要な情報を追加します。|

`synchronous_commit`は同期レプリケーションにするので、`on`にします。各設定は、同期：on、メモリ同期：remote_write、スレーブ非同期：local、完全非同期：off。詳しくは、[こちら](https://gist.github.com/yukpiz/1d8da300fb416bcf0bf8#wal%E5%90%8C%E6%9C%9F%E6%96%B9%E5%BC%8F)で紹介されています。

`max_wal_senders`はスレーブからの同時接続数の上限を設定します。通常、スタンバイの数+1を設定します。

同期レプリケーションにする場合`synchronous_standby_names`の設定が必要です。この設定がなければ、スレーブ非同期レプリケーションで動作します。9.6より前は同期レプリケーションできるスタンバイは1つでしたが、9.6から複数のスタンバイで同期レプリケーションができるようになりました。


マスタの設定が終わったので、`vagrant`ユーザに戻って、PostgreSQLを再起動します。

```
$ sudo service postgresql restart
```

## スタンバイ(db2)の設定
PostgreSQLが動いている場合、一旦止めます。

```
$ sudo service postgresql stop
```

PGDATAを空にしてマスタからベースバックアップを取得します。

```
$ cd /var/lib/postgresql/9.6/main
$ rm -rf *
$ pg_basebackup -h 192.168.50.1 -p 5432 -U repl_user -D ~/9.6/main --xlog --checkpoint=fast --progress
```

`postgresql.conf`をバックアップをとったあとに修正します。

```
$ sudo su - postgres
$ cp /etc/postgresql/9.6/main/postgresql.conf /etc/postgresql/9.6/main/postgresql.conf.org
$ vim /etc/postgresql/9.6/main/postgresql.conf
```

ホットスタンバイ(参照可能)にするため、以下を追加します。
```
hot_standby = on
```

最後に`recovery.conf`を作成します。

```
$ vim /etc/postgresql/9.6/main/recovery.conf
```

スタンバイモードしマスタと連携させるため、以下を追加します。


```
standby_mode = on
primary_conninfo = 'host=192.168.50.1 port=5432 user=repl_user password=password application_name=db2'
```


`vagrant`ユーザに戻り、PostgreSQLを再起動する。

```
$ sudo service postgresql start
```

## 起動確認
マスタ(db1)で`state`がstreamingになっていればOKです。

```
$ psql -c "SELECT * FROM pg_stat_replication;"
```

データを入れて確認してみましょう。マスタ(db1)でデータを入れます。

```
$ psql postgres -c "CREATE TABLE test_table(i int);"
CREATE TABLE
$ psql postgres -c "INSERT INTO test_table VALUES (1);"
INSERT 0 1
```

スタンバイ(db2)でデータを確認します。値が入っていれば、レプリケーションできています。

```
$ postgres@db2:~$ psql postgres -c "SELECT * FROM test_table;"
 i
---
 1
(1 row)
```

## 参考
- [How to install PostgreSQL 9.6 on any Ubuntu version](https://askubuntu.com/a/831293)
- [Postgresqlレプリケーション ストリーミングレプリケーション仕組み](http://d.hatena.ne.jp/ttshrk/20110421/1303380112)
- [19.5. ログ先行書き込み（WAL）](https://www.postgresql.jp/document/9.6/html/runtime-config-wal.html)

## 設定ファイル
今回設定したconfのファイルは全てGithubに上げています。
https://github.com/kouyaf77/postgresql-replication-sample