要点
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_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