要点
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 オプションを加える。
参考資料
マスター側での作業
マスターサーバーの起動
$ 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_userpassword=p@ssw0rdhost=127.0.0.1port=54321sslmode=prefersslcompression=1krbsrvname=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
