small is beautiful

be the worst

PostgreSQL: Updateで大量の行を処理時にWAF Archiver停止、DB停止した為PITRしたい

事象

  • WALのarchive commandが停止, 続いてDBも停止した。
  • 挙動の確認はPostgreSQL9.6。
# 出力されたerror.
could not write to file "pg_xlog/xlogtmp.111111" No space left on device

原因

  • Updateで大量の行を処理(既存データのクレンジング)しようとした為。
    • clusterdb command 4並列実行。

調査

archiveが停止したことで, WALが格納されているdisk sizeが残り数MBになっていた。

# df -h ./*
ファイルシス                                        サイズ  使用  残り 使用% マウント位置
/dev/mapper/vg_data_01_lv_data_01              xxG   xxG  xxG   24% /pgsql/data_01/data
/dev/mapper/vg_data_01_lv_data_01_pglog_96   xxG   xxM  xxG    1% /pgsql/data_01/pg_log
/dev/mapper/vg_data_01_lv_data_01_xlog_96      xxG   xxG  10M  100% /pgsql/data_01/pg_xlog
  • archiveのprocessが動いていない。
  • Postgresのprocessが落ちている。
  • .ready fileが残っている。
ll /pgsql/data_01/data/pg_xlog/archive_status

-rw-------. 1 postgres postgres 0  xxx.ready
-rw-------. 1 postgres postgres 0  xxx.ready
-rw-------. 1 postgres postgres 0  xxx.ready
-rw-------. 1 postgres postgres 0  xxx.ready
DETAIL:  postmaster on DB node 0 was shutdown by administrative command
LOG:  received degenerate backend request for node_id: 0 from pid [10777]
LOG:  reading and processing packets

PITR

チェックポイントは、一連のトランザクションにおいて、そのチェックポイント以前に書かれた全ての情報によりヒープとインデックスファイルがすでに更新されていることを保証する場所です。 チェックポイントの時刻において、全てのダーティページデータはディスクにフラッシュされ、特殊なチェックポイントレコードがログファイルに書き込まれます。 (変更されたレコードは以前にWALフラッシュされています。) クラッシュした時、クラッシュからの復旧処理は最後のチェックポイントレコードを見つけ、ログの中でどのレコード(これはredoレコードと呼ばれています)から復旧処理がREDOログ操作を開始すべきかを決定します。 このチェックポイント以前になされたデータの変更は、すでにディスク上にあることが保証されています。 従って、チェックポイント後、redoレコード内のそのチェックポイント以前のログセグメントは不要となり、再利用または削除することができます (WALアーカイブが行われる場合、このログセグメントは削除もしくは再利用される前に保存されなければなりません)。

check point recordを見つけ、それ以前のデータ変更はdisk上にあることが保証されている為、削除する (又はbackupする)。

30.4. WALの設定

  1. pg_controldata を確認し、最後のWAL checkpoint record (redo record) をメモ。
  2. pg_archivecleanup でメモしたredo recordを指定してそれ以前のsegmentを削除 (削除前にbackup)。
  3. disk sizeに空きが出たことを確認。
  4. postgres起動。
  5. archive_status.ready から.done になることを確認する。
cd /pgsql/data_01/
pg_controldata data/

pg_controlバージョン番号:                         xxx
カタログバージョン番号:                           xxx
データベースシステム識別子:                       xxx
データベースクラスタの状態:                       運用中
pg_control最終更新:                               xxx
最終チェックポイント位置:                         20A5/20000011
前回のチェックポイント位置:                       20A5/1D000011
最終チェックポイントのREDO位置:                   20A5/20000011
最終チェックポイントのREDO WALファイル:    00000004000020A500000011
最終チェックポイントの時系列ID:                   4
最終チェックポイントのPrevTimeLineID:   4
最終チェックポイントのfull_page_writes オン
最終チェックポイントのNextXID:                    xxx
最終チェックポイントのNextOID:                    xxx
最終チェックポイントのNextMultiXactId:  1
最終チェックポイントのNextMultiOffset:  0
最終チェックポイントのoldestXID:      xxx
最終チェックポイントのoldestXIDのDB:  xxx
最終チェックポイントのoldestActiveXID: xxx
最終チェックポイントのoldestMultiXid:   1
最終チェックポイントのoldestMulti'sのDB: xxx
最終チェックポイントのoldestCommitTsXid:0
最終チェックポイントのnewestCommitTsXid:0
最終チェックポイント時刻:                         xxx
ログを取らないリレーション向けの偽のLSNカウンタ:   0/1
最小リカバリ終了位置:                             0/0
最小リカバリ終了位置のタイムライン:   0
バックアップ開始位置:                 0/0
バックアップ終了位置:                 0/0
必要なバックアップ最終レコード:        no
wal_level の設定:                    replica
wal_log_hints の設定:                オン
max_connections の設定:              350
max_worker_processes の設定:         8
max_prepared_xacts の設定:           350
max_locks_per_xact の設定:           64
track_commit_timestamp の設定:       オフ
最大データアラインメント              8
データベースのブロックサイズ:                     xxx
ラージリレーションのセグメント当たりのブロック数: xxx
WALブロックのサイズ:                              xxx
WALセグメント当たりのバイト数:                  xxx
識別子の最大長:                                   64
インデックス内の最大列数:             32
TOASTチャンクの最大サイズ:                               xxx
ラージオブジェクトチャンクのサイズ:         xxx
日付/時刻型の格納方式:                            64ビット整数
Float4 引数の渡し方:                 値渡し
Float8  引数の渡し方:                値渡し
データベージチェックサムのバージョン:           0

このモードでは、.backupファイルが指定されると、そのプレフィックス部だけがoldestkeptwalfileとして使用されます。 この機能は、エラーを起こすことなく特定のベースバックアップより前のすべてのWALファイルを削除することを可能にします。

pg_archivecleanup

# cleanup WAL before check point.
pg_archivecleanup -d /pgsql/data_01/000.backup

完了。