PostgreSQL 9.6: レプリケーションの設定（非同期）

要点

MySQL 用語の「マスター」と「スレーブ」を、PostgreSQL 用語では「マスター」と「スタンバイ（standby）」と呼ぶ。
レプリケーションには「非同期（async）」と「同期（sync）」の区別がある。可用性を上げたいなら「同期」を選択する。
pg_basebackup コマンドを利用すると、マスターを停止させずにスタンバイを構築できる。
PostgreSQL 9.4 で導入された「ロジカルレプリケーション」、「レプリケーションスロット」を活用すべし。

PostgreSQL 12 でレプリケーションに関する重要な仕様変更が行われた。本稿末尾に「PostgreSQL 12 のための追記」という節を追加した。（2020-09-06）

参考資料

クラスタ群の作成

本稿では、同一のマシン上にふたつの PostgreSQL クラスタ cluster1 と cluster2 を作成し、前者をマスター、後者をスタンドバイとするレプリケーションを行う方法を解説する。

$ sudo pg_createcluster --port=54321 9.6 cluster1
$ sudo pg_createcluster --port=54322 9.6 cluster2

sudo -u postgres pg_createcluster ... のように postgres ユーザーで実行してもクラスタは作成される。しかし、systemctl daemon-reload の実行ができないため警告が出る。root ユーザーで実行する。

PostgreSQL にログを英語で出力させたい場合は、--lc-messages=C オプションを加える。

参考資料

http://tkrd.hatenablog.com/entry/2017/04/23/015216

マスター側での作業

マスターサーバーの起動

$ sudo systemctl start postgresql@9.6-cluster1

sudo pg_ctlcluster 9.6 cluster1 start でもよい。

`repl_user` ロールの追加

$ sudo -u postgres psql --port 54321
> CREATE ROLE repl_user LOGIN REPLICATION PASSWORD 'p@ssw0rd';

パスワード p@ssw0rd は例。適宜変更する。

`postgresql.conf` の書き換え

テキストエディタで /etc/postgresql/9.6/cluster1/postgresql.conf に以下の設定を追加。

wal_level = logical
max_wal_senders = 16
max_replication_slots = 16

wal_level = logical により「ロジカルレプリケーション」が有効になる。
max_wal_senders と max_replication_slots には、「スタンドバイの個数 + 1」以上の値をセットする。max_connections （デフォルト値: 100）の値を超えてはならないが、適宜余裕を持って設定する。
WALはWrite Ahead Loggin（ログ先行書き込み）の略。データの一貫性を確実にするための仕組み。WALファイルをスレーブに転送するプロセスがWAL Sender。
レプリケーションスロットは、レプリケーションの実施を確実にするための仕組み。スレーブ単位でレプリケーションの状態を管理し、スレーブが必要としているWALファイルを消したり、（スレーブが参照している）不要データ領域をVACUUM処理で削除したりしないようにできる。

`pg_hba.conf` の書き換え

テキストエディタで /etc/postgresql/9.6/cluster1/pg_hba.conf につぎの設定を追加。

host replication repl_user 127.0.0.1/32 md5

レプリケーションを実行するユーザー（ロール）として、先に作った repl_user を指定。別のマシンとの間でレプリケーションを行う場合の設定については後述。

クラスタの再起動

$ sudo systemctl restart postgresql@9.6-cluster1

reload ではうまく行かない。

レプリケーションスロットの追加

$ sudo -u postgres psql --port 54321
> SELECT * FROM pg_create_physical_replication_slot('cluster2');

スタンドバイ側での作業

クラスタの停止

念のため、クラスタ cluster2 を停止する。

$ sudo systemctl stop postgresql@9.6-cluster2

pg_createcluster コマンドでクラスタ cluster2 を作ったまま起動していなければ、この手順は不要。

データディレクトリの削除

クラスタ cluster2 のデータディレクトリを削除する。

$ sudo rm -rf /var/lib/postgresql/9.6/cluster2

マスターからデータを複製

マスタークラスタからデータディレクトリを複製する。

$ sudo -u postgres \
PGPASSWORD=p@ssw0rd /usr/lib/postgresql/9.6/bin/pg_basebackup \
-h 127.0.0.1 --port=54321 -U repl_user \
-D /var/lib/postgresql/9.6/cluster2 \
-S cluster2 -X stream --progress -R

オプション -S にはレプリケーションスロットの名前を指定する。オプション -R を付けると、recovery.conf のひな型が作成される。

`postgresql.conf` の書き換え

テキストエディタで /etc/postgresql/9.6/cluster2/postgresql.conf に以下の記述を追加。

hot_standby = on
hot_standby_feedback = on

レプリケーションスロットを利用するには hot_standby_feedback の値を on にしなければならない。

クラスタの起動

$ sudo systemctl start postgresql@9.6-cluster2

うまく起動しない場合は、/var/log/postgresql/postgresql-9.6-cluster2.log を見る。 psql: FATAL: the database system is starting up という行が連続する場合、postgresql.conf の hot_standby の値が on になっていること（誤字がないこと）を確認する。

動作確認

マスター側に接続

$ sudo -u postgres psql --port 54321
> \x

\x は「垂直表示モード」への切り替え。

レプリケーションの状態

> SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 31545
usesysid         | 16384
usename          | repl_user
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 56390
backend_start    | 2017-04-23 20:25:14.440237+09
backend_xmin     | 
state            | streaming
sent_location    | 0/D001748
write_location   | 0/D001748
flush_location   | 0/D001748
replay_location  | 0/D001748
sync_priority    | 0
sync_state       | async

sync_state が「非同期（async）」になっている。

レプリケーションスロットの状態

> SELECT * FROM pg_replication_slots;
-[ RECORD 1 ]-------+----------
slot_name           | cluster2
plugin              | 
slot_type           | physical
datoid              | 
database            | 
active              | t
active_pid          | 31820
xmin                | 596
catalog_xmin        | 
restart_lsn         | 0/D002490
confirmed_flush_lsn |

active 列が t である点をチェック。

データベースの作成

> CREATE DATABASE repl_test1;

スタンバイに接続

$ sudo -u postgres psql --port 54322
> \x

データベース `repl_test1` の情報を確認

> \l repl_test1
データベース一覧
-[ RECORD 1 ]-----+------------
名前              | repl_test1
所有者            | postgres
エンコーディング  | UTF8
照合順序          | ja_JP.UTF-8
Ctype(変換演算子) | ja_JP.UTF-8
アクセス権        |

\l はデータベースのリストを表示するコマンド。

同期レプリケーションモードへの移行

`postgresql.conf` （マスター側）の書き換え

# sudo vim /etc/postgresql/9.6/cluster1/postgresql.conf
synchronous_standby_names = 'cluster2'

synchronous_standby_names には、スレーブ側の recovery.conf （後述）に記載する primary_conninfo の application_name として指定する。複数の名前を指定する場合はコンマで区切る。

`recovery.conf` の書き換え

テキストエディタで /var/lib/postgresql/9.6/cluster2/recovery.conf を開き、primary_conninfo に指定された文字列の末尾に application_name=cluster2 を追加する。

このファイルは pg_basebackup コマンドによって生成される（-R オプションを指定した場合）。

ちなみに primary_conninfo に指定される文字列は attr=value の形式の設定値をスペースで連結したもの。初期値は以下のようになっている。

user=repl_user
password=p@ssw0rd
host=127.0.0.1
port=54321
sslmode=prefer
sslcompression=1
krbsrvname=postgres

application_name の値は、マスター側の postgresql.conf に記載する synchronous_standby_names の値と一致させる。

recovery.conf には、primary_conninfo の他に standby_mode と repl_slot_name という設定項目がある。

standby_mode の値は on とする。repl_slot_name の値は、マスター側で追加したレプリケーションスロットの名前と同じにする。本稿では application_name と同じにしたが、異なっていてもよい。

クラスタの再起動

$ sudo systemctl restart postgresql@9.6-cluster2
$ sudo systemctl restart postgresql@9.6-cluster1

リロードではうまく行かない。

別のマシンとの間でレプリケーションを行う

まず、マスターの postgresql.conf の listen_addresses 属性にマスターが動作するマシンの IP アドレスを指定する。

listen_addresses = '192.168.0.3'

この属性のデフォルト値は localhost であり、そのままでは外部からの接続を受け付けないことになる。

次に、スタンドバイの IP アドレスまたはスタンドバイの属するネットワークの IP アドレス範囲を、マスターの pg_hba.conf に指定する。例えば、スタンドバイがネットワーク 192.168.0.0/24 に属するなら、次のように記述する。

host replication repl_user 192.168.0.0/24 md5

これらの設定を変更したら、マスターを再起動する。

$ sudo systemctl restart postgresql@9.6-cluster1

続いて、スタンドバイ側の recovery.conf にある 127.0.0.1 をマスターの IP アドレスで書き換え、スタンドバイを再起動する。

$ sudo systemctl restart postgresql@9.6-cluster2

遅延レプリケーション

スタンドバイの recovery.conf に次の記述を追加すると、レプリケーションを12時間遅らせることができる。

recovery_min_apply_delay = '12h'

遅延レプリケーションを設定しておくと、誤操作等によってマスターのデータが消失・損壊したときに役に立つ。例えば、WHERE 節を指定せずに DELETE 文や UPDATE 文を発行してしまった場合に、一定時間はデータを復元できる。

PostgreSQL 12 のための追記（2020-09-06）

PostgreSQL 12 以降、recovery.conf の代わりに standby.signal と postgresql.auto.conf という 2 つのファイルが作られることになった。

スタンドバイからプライマリー（マスター）への接続情報は postgresql.auto.conf で次のように記述されている。

primary_conninfo = 'user=repl_user password=p@ssw0rd host=127.0.0.1 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'cluster2'

しかし、この postgresql.auto.conf をテキストエディタで書き換えてはならない。代わりに、PostgreSQL コンソールにおいて ALTER SYSTEM コマンドで設定する。

ALTER SYSTEM SET primary_conninfo = 'user=repl_user password=p@ssw0rd host=192.168.0.3 port=54321 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any';

この設定変更は、スタンドバイを再起動することで有効になる。

$ sudo systemctl restart postgresql@9.6-cluster2

要点

参考資料

クラスタ群の作成

参考資料

マスター側での作業

マスターサーバーの起動

repl_user ロールの追加

postgresql.conf の書き換え

pg_hba.conf の書き換え

クラスタの再起動

レプリケーションスロットの追加

スタンドバイ側での作業

クラスタの停止

データディレクトリの削除

マスターからデータを複製

postgresql.conf の書き換え

クラスタの起動

動作確認

マスター側に接続

レプリケーションの状態

レプリケーションスロットの状態

データベースの作成

スタンバイに接続

データベース repl_test1 の情報を確認

同期レプリケーションモードへの移行

postgresql.conf （マスター側）の書き換え

recovery.conf の書き換え

クラスタの再起動

別のマシンとの間でレプリケーションを行う

遅延レプリケーション

PostgreSQL 12 のための追記（2020-09-06）

`repl_user` ロールの追加

`postgresql.conf` の書き換え

`pg_hba.conf` の書き換え

`postgresql.conf` の書き換え

データベース `repl_test1` の情報を確認

`postgresql.conf` （マスター側）の書き換え

`recovery.conf` の書き換え