内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch10 (高可用化と負荷分散) (3/6)
PostgreSQLで構成できる3つのスタンバイ
- コールド、ウォーム、ホット
- それぞれ一長一短
コールドスタンバイ
- 特徴
- プライマリとスタンバイとが1つのデータを共有する
- 公式/実現方法
- 「Shared Disk Failover」「File SYstem (Block Device) Replication」がこれにあたる
- メリ
- 運用方法が最もシンプル
- 切り替え
- 監視
- 運用方法が最もシンプル
- デメ
- スタンバイに参照クエリを実行できない
- 平時において、スタンバイ機は無駄になる
- 共有ディスクが高くつくことがある
- 共有ディスクがSPOFになる
- スタンバイに参照クエリを実行できない
ウォームスタンバイ
- 特徴
- 非同期ストリーミングレプリケーションで構築
- 公式/pg_standby
- ウォームスタンバイサーバを構築するコマンド
- メリ
- デメ
- スタンバイに参照クエリを実行できない
- 非同期レプリケーションだとデータ損失に直結する
ホットスタンバイ
- 公式
- 特徴
- コールド、ウォームのいいとこどり
- 同期ストリーミングレプリケーションで構築
- メリ
- 同期レプリケーションなのでデータ損失の危険性ほとんどなし
- デメ
- スタンバイを遠隔地に置くと性能影響が大きいため、DRには向かない
ホットスタンバイの詳細
- 公式
- スタンバイの
postgresq.conf
設定一行で有効化
postgresql.conf
#hot_standby = on # "off" disallows queries during recovery # (change requires restart)
postgres=# SELECT name,setting,context,boot_val FROM pg_settings WHERE name='hot_standby'; name | setting | context | boot_val -------------+---------+------------+---------- hot_standby | on | postmaster | on (1 row)
- スタンバイが「一貫性のある状態」になったら接続許可
- ベースバックアップ取得時のチェックポイントが完了した時点までリカバリが済んだ状態
- 一貫性のある状態になるまでは接続拒否
- クライアント側ではエラーメッセージを目にすることになる
- 接続許可するようになったらこんなログが出る
LOG: entering standby mode ... then some time later ... LOG: consistent recovery state reached LOG: database system is ready to accept read only connections
- 結果整合性
- 同期レベル(
synchronous_commit
)がremote_apply
よりも低い場合、レプリケーション遅延がある - プライマリの書き込み直後は、プライマリとスタンバイとで読み取りクエリ結果が異なる可能性がある
pg_stat_replication
システムビューで確認可能
- 同期レベル(
postgres=# SELECT * FROM pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 97 usesysid | 16384 usename | replication_user application_name | walreceiver client_addr | 172.22.0.3 client_hostname | client_port | 32834 backend_start | 2020-02-10 15:09:34.524869+00 backend_xmin | state | streaming sent_lsn | 0/3013310 write_lsn | 0/3013310 flush_lsn | 0/3013310 replay_lsn | 0/3013310 write_lag | flush_lag | replay_lag | sync_priority | 1 sync_state | sync reply_time | 2020-02-10 15:16:16.326192+00
ホットスタンバイで実行可能なクエリ
- 厳密に読み取りのみ
- 一時テーブル(コネクション切断時にdropされるやつ)も書き込み不可
postgres=# CREATE TEMPORARY TABLE tmptbl (col int); ERROR: cannot execute CREATE TABLE in a read-only transaction
- 実行可能なクエリ
Transactions started during hot standby may issue the following commands: * Query access - SELECT, COPY TO * Cursor commands - DECLARE, FETCH, CLOSE * Parameters - SHOW, SET, RESET * Transaction management commands * BEGIN, END, ABORT, START TRANSACTION * SAVEPOINT, RELEASE, ROLLBACK TO SAVEPOINT * EXCEPTION blocks and other internal subtransactions * LOCK TABLE, though only when explicitly in one of these modes: ACCESS SHARE, ROW SHARE or ROW EXCLUSIVE. * Plans and resources - PREPARE, EXECUTE, DEALLOCATE, DISCARD * Plugins and extensions - LOAD * UNLISTEN
ベースバックアップの取得
- スタンバイからでも
pg_basebackup
コマンドを実行してベースバックアップ取得可能- cf.
pg_start_backup()
関数やpg_stop_backup()
関数はスタンバイから実行不可能
- cf.
recovery_target_action
パラメータ- 取得完了時のアクションの設定
#recovery_target_action = 'pause' # 'pause', 'promote', 'shutdown' # (change requires restart)
postgres=# SELECT name,setting,context,enumvals,boot_val FROM pg_settings WHERE name='recovery_target_action'; name | setting | context | enumvals | boot_val ------------------------+---------+------------+--------------------------+---------- recovery_target_action | pause | postmaster | {pause,promote,shutdown} | pause (1 row)
関数名 | 説明 |
---|---|
pg_is_wal_replay_paused() |
リカバリが停止していたらtrue |
pg_wal_replay_pause() |
リカバリ即停止 |
pg_wal_replay_resume() |
リカバリ再開 |
ホットスタンバイの弱点
細かいけど、実際にコンフリクトするのはスレーブ側でのSELECTとそのSELECTしている行を物理削除するWALの適用(その他にもある)。SELECTのロックとUPDATEのロックは通常のクエリ処理でも競合しないようになっている。
— Masahiko Sawada (@masahiko_sawada) 2017年12月27日
PostgreSQLのレプリケーションのコンフリクトについてhttps://t.co/O4kEcoUVR3
- コンフリクトが弱点
- 現象がおこる流れ
- スタンバイが読み取りクエリを発行し、共有ロックを獲得する
- PostgreSQLでは
SELECT
文でもロックを獲得する
- PostgreSQLでは
- プライマリ側で物理行削除を伴う操作を行うった場合
- プライマリの
DROP TABLE
- データベースオブジェクトの削除
- VACUUMによるメンテナンス
- プライマリの
- スタンバイはそのWALを受信するが、適用できないためこれを待つ
- 待っている間、
- スタンバイが読み取りクエリを発行し、共有ロックを獲得する
- ぶつかったらデフォルトで30秒WAL適用を回避する
- 30秒分レプリケーション遅延が起きるということ
- 要件によっては致命的
- ミリ秒単位で設定可能
- 30秒分レプリケーション遅延が起きるということ
#max_standby_streaming_delay = 30s # max delay before canceling queries # when reading streaming WAL; # -1 allows indefinite delay
postgres=# SELECT name,setting,context,unit,boot_val FROM pg_settings WHERE name='max_standby_streaming_delay'; name | setting | context | unit | boot_val -----------------------------+---------+---------+------+---------- max_standby_streaming_delay | 30000 | sighup | ms | 30000 (1 row)
【補】コンフリクトを再現してみる
- 【プライマリ】物理的にスッカスカなテーブルを作る
INSERT
とかDELETE
とかして作る- あとで
VACUUM FULL
するため
CREATE TABLE tbl (col int); INSERT INTO tbl VALUES(generate_series(1,100)); -- 1,2,3,... 100行 DELETE FROM tbl WHERE col % 2 = 0; -- 1,3,5,... 50行
SELECT count(*) FROM tbl;
count ------- 50 (1 row)
- 【スタンバイ】
ACCESS_SHARE_LOCK
取得、50行であることを確認
BEGIN; LOCK tbl IN ACCESS SHARE MODE;
SELECT count(*) FROM tbl;
count ------- 50 (1 row)
- 【プライマリ】50行挿入してみる
INSERT INTO tbl VALUES(generate_series(2,100,2));
- 【スタンバイ】行数確認
- 100行になっている
- コンフリクトせず、WALが適用されていることの確認
SELECT count(*) FROM tbl;
count ------- 100 (1 row)
- 【プライマリ】
VACUUM FULL
で行を物理削除- プライマリはスタンバイの事なんか知ったこっちゃないので問題なく実行できる
- スタンバイ側では
ACCESS SHARE
ロックと競合するためWALを適用できない
VACUUM FULL VERBOSE tbl;
INFO: vacuuming "public.tbl" INFO: "tbl": found 50 removable, 100 nonremovable row versions in 1 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM
- 【プライマリ】再度50行挿入
INSERT INTO tbl VALUES(generate_series(2,100,2));
- 【スタンバイ】行数確認
- 100行のまま
- WAL適用がロックに待たされている(コンフリクト)
SELECT count(*) FROM tbl;
count ------- 100 (1 row)
- 【スタンバイ】30秒たってから何かクエリを発行するとコンフリクト発生、怒られる
FATAL: terminating connection due to conflict with recovery DETAIL: User was holding a relation lock for too long. HINT: In a moment you should be able to reconnect to the database and repeat your command. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.
- コンフリクト履歴は
pg_stat_database_conflicts
で確認可能
postgres=# \x Expanded display is on. postgres=# SELECT * FROM pg_stat_database_conflicts WHERE datname='postgres'; -[ RECORD 1 ]----+--------- datid | 13408 datname | postgres confl_tablespace | 0 confl_lock | 1 confl_snapshot | 0 confl_bufferpin | 0 confl_deadlock | 0