PostgreSQL11で非同期レプリケーションを設定する

# 環境

PostgreSQL11で非同期レプリケーションの設定を実施した際のメモです。

- CentOS 7.5(firewalldとSELinuxは無効化しています)
- PostgreSQL 11.2

マスタ側のサーバは以下のとおり
- サーバ名：postgresserver1
- IPアドレス：192.168.10.168
- 以下の投稿の内容で環境構築を実施済み。
　[CentOS 7にPostgreSQL11をインストールする](https://qiita.com/mkyz08/items/519bd8d6a7140c8ed57e)

ホットスタンバイ側のサーバは以下のとおり
- postgresserver2
- IPアドレス：192.168.10.169
- マスタ側からデータベースを取得するので、PostgreSQLのインストールのみ実施済み。

/etc/hostsには以下を追加。

192.168.10.168  postgresserver1
192.168.10.169  postgresserver2


# マスタサーバの設定

レプリケーション用のユーザ（replication_user）を作成します。

```
-bash-4.2$ psql -U postgres
psql (11.2)
Type "help" for help.

postgres=# CREATE ROLE replication_user LOGIN REPLICATION PASSWORD 'xxxxxxxxx';
CREATE ROLE
```

postgresql.confを以下のように修正します。

```
# vi /data/postgresql.conf

wal_level = replica（デフォルト値なので変更なし）
max_wal_senders = 10（1以上。デフォルトが10なので変更なし）
archive_mode = on
archive_command = ''（デフォルト値なので変更なし）
synchronous_commit = off（デフォルトonから変更）
synchronous_standby_names = ''（デフォルト値なので変更なし）
```

ホットスタンバイ側のサーバからreplication_userユーザで接続できるように設定します。

```
# vi /data/pg_hba.conf

以下の1行を追加
host    replication     replication_user        192.168.10.0/24           md5
```

最後にPostgreSQLを再起動します。

```
$ pg_ctl restart
```

# ホットスタンバイ側の設定

マスタ側のデータベースクラスタを「/data」以下に作成しているので、あらかじめスタンバイ側にもディレクトリを作成します。
※データベースクラスタがデフォルト設定の場合は不要です。

```
# mkdir /data
# chown -R postgres:postgres /data
```

pg_basebackupコマンドを使用して、マスタ側のデータベースクラスタを取得します。

```
$ pg_basebackup -h 192.168.10.168 -p 5432 -U replication_user -D /data/ --wal-method=fetch --checkpoint=fast --write-recovery-conf --progress
Password: 
65390/65390 kB (100%), 1/1 tablespace
```

postgresql.confの設定を変更します。

```
# vi /data/postgresql.conf

hot_standby = on
```
※PostgreSQLのデフォルト値なので設定しなくても動くはず。


recovery.confは、"--write-recovery-conf"オプションをつけているので以下の内容で自動生成されます。
必要に応じて修正しますが、今回はこのまま使用します。

```
# cat /data/recovery.conf 
standby_mode = 'on'
primary_conninfo = 'user=replication_user password=XXXXXXXXXXXXXXX host=192.168.10.168 port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'
```

PostgreSQLを起動します。


# レプリケーションの確認

pg_stat_replicationテーブルでレプリケーションできているか確認します。

```
$ psql postgres
psql (11.2)
Type "help" for help.

postgres=# select usename, application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn from pg_stat_replication;
     usename      | application_name |  client_addr   |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn 
------------------+------------------+----------------+-----------+-----------+-----------+-----------+------------
 replication_user | walreceiver      | 192.168.10.130 | streaming | 0/701F530 | 0/701F530 | 0/701F530 | 0/701F530
(1 row)
```

マスター側では以下のように「walsender」のプロセスが起動しています。

```
# ps -efl | grep walsender
1 S postgres  3601  3588  0  80   0 - 99361 ep_pol 03:57 ?        00:00:00 postgres: walsender replication_user 192.168.10.130(46994) streaming 0/701F6B8
```

スタンバイ側では以下のように「walreceiver」のプロセスが起動しています。

```
# ps -efl | grep walreceiver
1 S postgres  3322  3258  0  80   0 - 100913 ep_pol 03:57 ?       00:00:00 postgres: walreceiver   streaming 0/701F6B8

また、以下のログも出力されます。

2019-05-05 02:41:25.456 CEST [3264] LOG:  started streaming WAL from primary at 0/7000000 on timeline 1
```

マスタ側に接続し、テーブルとレコードを作成します。

```
testdb=> create table test2 (id int, value text);
CREATE TABLE
testdb=> insert into test2 (id, value) values (1, 'test text');
INSERT 0 1
```

スタンバイ側で、追加したレコードを検索してみます。

```
-bash-4.2$ psql testdb testuser
Password for user testuser: 
psql (11.2)
Type "help" for help.

testdb=> select * from test2;
 id |   value   
----+-----------
  1 | test text
(1 row)
```

また、以下のようにスタンバイ側でインサートすると失敗します。

```
testdb=> insert into test2 (id, value) values (2, 'test text');
ERROR:  cannot execute INSERT in a read-only transaction
```

# pgbenchでベンチマーク実行

pgbenchでベンチマーク実行してみます。

マスタ側の結果

```
$ pgbench -c 30 -t 1000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 30
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 30000/30000
latency average = 15.143 ms
tps = 1981.171016 (including connections establishing)
tps = 1981.438979 (excluding connections establishing)
```

ホットスタンバイ側の結果
"-S"で検索のみ、"-n"でvacuumを実行しないように設定しています。

```
$ pgbench -c 30 -t 1000 -S -n testdb
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 30
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 30000/30000
latency average = 1.386 ms
tps = 21647.820665 (including connections establishing)
tps = 21675.004146 (excluding connections establishing)
```

# マスタを切り替える

まず、マスタ側でPostgreSQLを停止します。

```
$ pg_ctl stop -m immediate
waiting for server to shut down.... done
server stopped
```

"pg_ctl promote"コマンドでスタンバイをマスタに昇格させます。

```
$ pg_ctl promote
waiting for server to promote.... done
server promoted
```

旧スタンバイがマスタになっており、今度はインサートに成功します。

```
$ psql testdb testuser
Password for user testuser: 
psql (11.2)
Type "help" for help.

testdb=> insert into test2 (id, value) values (2, 'test text');
INSERT 0 1
```

旧マスタ側を再度マスタとして利用するためには、差分を新マスタから旧マスタへ同期させ、旧スタンバイ側のデータベースを削除し、再度pg_basebackupでデータベースをマスタから取得します。


# 参考

- [PostgreSQL9.6 レプリケーション設定](https://qiita.com/nyao/items/50c8216050b726838e45)
- [PostgreSQLの冗長構成の構築(同期ストリーミングレプリケーション)](https://qiita.com/kouyaf77@github/items/e0372783839cec6729ea)
- [ストリーミング・レプリケーションの構築 by Let's Postgres](https://lets.postgresql.jp/documents/technical/replication/1)