postgres マスタとスタンバイ構成を構築する

参考サイト
https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/article-index/streaming-replication1/

※前条件

・dockerで同じpostgresql 9.5のコンテナを構築しておく(マスター用とレプリケーション用の二つ)。

1.マスター側設定

1.マスター側のクラスタを作成

# 必ずpostgresユーザーで実行する
# root権限で実行した場合、作成されたクラスタのディレクトリが
# root権限、かつグループになってしまうため
# pg_createcluster (任意のポート)  (任意のバージョン)  (任意のクラスタ名) 
pg_createcluster --port 5433 9.5 mater

2.クラスタ作成にミスがあった場合

port番号などの指定ミスをして再度同じクラスタを作成しようとすると
以下のようなエラーが表示されて、同名のクラスタを作成できなくなる

Error: cluster configuration already exists

上記のような例に出くわした場合、以下かコマンドでクラスタを完全に削除する

pg_dropcluster 9.5 master

3.マスター側の/etc/postgresql/9.5/master/postgresql.confを編集する

# 外部のホストからのアクセスをすべて許可
# ここはデフォルトでこうなっている
listen_addresses = '*'

# 9.5ではhot_standby (9.6以降は replicaとなっている模様)
wal_level = hot_standby 

# WALレコードがディスク上に書き込まれるのを完了後
# マスターの更新処理を完了とする => 同期スタンバイサーバーとして使用する
synchronous_commit = on

# 「スタンバイサーバの数+1」を指定します。
# ただし、max_connectionsを超える値は設定できません。(※前述した参考サイトより)
max_wal_senders = 2

# 同期スタンバイサーバーを任意の名前で指定する
# スタンバイ側の設定で後ほど必要になる
synchronous_standby_names = 'standby1'

#hot_standby = on

4.マスター側の/etc/postgresql/9.5/master/pg_hba.confを編集する

この設定は、アプリケーション側から作成したマスタークラスタへのアクセスを許可するため

# 末尾に以下を追加する(外部からアクセスできるように修正)
host      all        all        all        md5

4.マスター側のpostgresユーザーのパスワードを設定する

作成したクラスタは、新しいポート5433で動作するためpostgresのパスワードを新たに設定しなおす(既存のpostgresアカウントとは異なるため)

#postgresロールのパスワードを変更
psql -U postgres
postgres=# alter role postgres with password 'パスワード文字列';

ただし、マスターのpostgresql.confの設定を同期スタンバイサーバーでしている場合は上記パスワード変更のSQLのレスポンスが戻ってこないため、強制的にCLIを終了させるしかない。それが嫌な場合、postgresql.confの設定を非同期スタンバイにしておく

5.マスター側でreplication用のユーザーを作成する

ここではreplication用のユーザー replicant (任意の名前) を作成する

# レプリケーション用ユーザーを作成
psql -U postgres
CREATE ROLE replicant LOGIN REPLICATION PASSWORD 'xxxxxxxxx';

# \duコマンドでユーザー一覧を表示させる
postgres=# \du 

Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 replicant | Replication                                                | {}

レプリケーション用のユーザーreplicantが作成されていたらOK

6.マスター側でスタンバイサーバーからのアクセスを許可する

スタンバイ側サーバーからのアクセスを許可する。そのためにスタンバイサーバー側のIPを確認する

# スタンバイサーバー側で
hostname -I 
172.31.0.2 # スタンバイ側のIP

上記のコマンド実行結果のIPからマスターサーバー側へのアクセスを許可するため下記の設定を行う

# vi /etc/postgresql/9.5/master/pg_hba.conf
# 今回の場合だと以下のようになる
# host  replication  ${レプリケーション用ユーザー}  ${スタンバイサーバIP}  md5
host    replication　       replicant               172.31.0.2/32　       md5

# また上記設定が煩わしい場合は以下の設定でも動作が可能
host    replication           all                 all                  md5

再度master側postgresを再起動させる

/etc/init.d/postgresql restart

以上でマスター側の作業は終わり

スタンバイ側設定

1.スタンバイ用のクラスタを作成する

まず、スタンバイ用のクラスタを作成する*

# 必ずpostgresユーザーで実行する
su - postgres

# レプリケーション側クラスタを、作成
pg_createcluster --port 5433 9.5 slave

以下のディレクトリがスタンバイ用クラスタとして作成される

/var/lib/postgresql/9.5/slave 

2.スタンバイクラスタを空っぽにする

作成したレプリケーション側クラスタストレージを削除
これはマスター側のDBクラスタの状態をコピーしてくる際に邪魔になって
コマンドが実行できないため

rm -rf /var/lib/postgresql/9.5/slave/*

3.マスター側からベースバックアップを取得してくる

では、マスタDBからベースとなるベースバックアップを取得してくる
今回は公式のdockerイメージを使用したため、マスター先IPがコンテナ名になっているがIP指定でももちろん構わない

# -h => マスター側のIP
# -D => ベースバックアップ先となるディレクトリ。今回はスタンバイ用に作成した
# slaveディレクトリとする
pg_basebackup -h 172.31.0.5 -p 5433  -D /var/lib/postgresql/9.5/slave/ -R --progress -U replicant

4.スタンバイサーバーへ、アプリケーション側からのアクセスを許可する

# 同じく作成したスタンバイクラスタ内の/etc/postgresql/9.5/slave/pg_hba.confを編集する
# 末尾に以下を追加する
# 私の場合は普段navicatでDBの中身をみているので
# 外部ホストのアプリケーション側からアクセスできるように

host all all all mb5

5.スタンバイサーバの/etc/psotgresql/9.5/slave/postgresql.confを編集する

/etc/postgresql/9.5/slave/postgresql.conf内のhot_standby項目をONにする

# 以下の設定にする
hot_standby = on

これを忘れるとスタンバイが機能しない

6.スタンバイサーバの/var/lib/postgresql/9.5/slave/recovery.confを編集する

マスター側設定 の手順3で記載したが synchronous_standby_namesという項目とスタンバイ側のrecovery.confのprimary_conninfoという項目をマッチさせる必要がある

# デフォルトでは遅く以下のような内容で設定されている
# これはプライマリサーバー(マスタ側)への接続情報となる
standby_mode = 'on'
primary_conninfo = 'user=replicant password=******** host=172.31.0.5 port=5433 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any application_name=standby1'

# 上記内容に  "application_name=スタンバイサーバーの名前"を設定する
# 記述例としては以下のようになる

primary_conninfo = 'user=replicant password=******** host=172.31.0.5 port=5433 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any application_name=standby1'

7.スタンバイサーバを再起動する

#postgresの再起動

/etc/init.d/postgresql.conf restart 

以上でマスター側、スタンバイ側双方の設定が完了する。

それぞれ、任意のアプリケーションでマスターとスタンバイへ
postgresユーザーとパスワードで接続し、レプリケーションを確認する