本エントリーはPostgreSQL Advent Calendar 2023の14日目です。
前日の記事はこちらです。
はじめに
本記事ではPostgreSQLの拡張機能の一つであるpg_surgeryについて解説します。この拡張機能は、その名の通りデータベースの「surgery(手術)」に使うものです。実際にはデータ（DB内のレコードデータ)」の論理破損からの復旧に役立つ機能を提供してくれます。
ただし、使い方によっては簡単にDBを壊すことができるので用法を守って使う必要があります。
pg_surgeryとは
PostgreSQL14で追加された拡張機能です。この機能はPostgreSQLのバグの影響などでレコードのヘッダ情報などが意図せず不正なもの・破損したものになってしまい、正常な処理を受け付けなくなってしまったデータの状態を強制的に修正するために作られました。従来はテーブルデータのファイルを直接バイナリエディタなどで修正するといった方法しか取れませんでしたが、この機能により修正がやや簡単になっています。
現在は２つの関数を提供しています。今後に他の関数が追加されていくかもしれません。
pg_surgeryの基本的な使い方はマニュアルに記載されています。一般的な拡張機能とインストールなどの方法は同じです。
[postgres@localhost ~]$ cd /path/to/postgresql-16.1/contrib/pg_surgery/
[postgres@localhost ~]$ make && make install
[postgres@localhost ~]$ psql
psql (16.1)
Type "help" for help.
postgres=# CREATE EXTENSION pg_surgery ;
CREATE EXTENSION
これでpg_surgeryが利用が可能になりました。ではpg_surgeryで提供している２つの関数、heap_force_kill() と heap_force_freeze() について説明していきます。
TIDについて
２つの関数は引数にTIDを取るため、関数の話の前にちょっとだけTIDについて補足します。(知っている方は読み飛ばしてしまって構いません)
TIDはレコードの物理的な位置を示す識別子で、ページ番号(0開始)とそのページ内におけるレコードの位置(1開始)の組み合わせで表現されます。例えば最初のページの先頭に位置するレコードのTIDは(0,1)となります。テーブルの各レコードのTIDは、全テーブルにあるシステム列の一つであるctidという列で確認することもできます。
heap_force_kill()
一つ目はheap_force_kill()です。関数の形で利用可能で、特定のテーブルの特定のレコードを強制的に無効化します。無効化したいレコードは主キー列の値などではなく、TIDを指定します。
このheap_force_kill()は内部的に指定したTIDを指すラインポインタ(以降、LPと表記)のフラグを強制的にLP_DEADにセットし、LPが指すレコードを無効化します。LPはItemIdとも呼ばれます。詳細はこちらをご覧ください。LPはレコードへの位置、LPの状態(UNUSED, NORMAL, REDIRECT, DEADの4種)、LPの指すレコードのサイズの3つのデータを持っています。PostgreSQLはテーブルデータのアクセスの際に原則としてこのLPを辿りますが、LPの状態によって対象のレコードが有効かどうかを判断しています。このLPの状態を変えることで、レコードのヘッダ破損などに影響を受けることなく無効化させることができます。なお、無効化の際、必要に応じてVisibilityMapの変更も行います。
では使い方を見ていきます。なお、heap_force_kill()によりレコードのTIDを指すLPのデータを書き換えるので、それが分かりやすく可視化されるpageinspectも併用して様子を見てます。
-- pageinspectも導入
postgres=# CREATE EXTENSION pageinspect;
CREATE EXTENSION
-- テスト用のテーブルとデータを用意
postgres=# CREATE TABLE t1 (c1 int primary key, c2 text);
CREATE TABLE
postgres=# INSERT INTO t1 select 1, 'AAA';
INSERT 0 1
postgres=# INSERT INTO t1 select 2, 'BBB';
INSERT 0 1
-- ２行のレコードが見える
postgres=# SELECT ctid, xmin, xmax, * FROM t1;
ctid | xmin | xmax | c1 | c2
-------+------+------+----+-----
(0,1) | 770 | 0 | 1 | AAA
(0,2) | 771 | 0 | 2 | BBB
(2 rows)
-- pageinspectで各行のヘッダ情報も確認
postgres=# SELECT * FROM heap_page_items(get_raw_page('t1', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
1 | 8160 | 1 | 32 | 770 | 0 | 0 | (0,1) | 2 | 2306 | 24 | | | \x0100000009414141
2 | 8128 | 1 | 32 | 771 | 0 | 0 | (0,2) | 2 | 2306 | 24 | | | \x0200000009424242
(2 rows)
-- この関数は引数にテーブルのOIDとTID配列を取る。t1テーブルの２行目(0,2)を指定して実行
-- 成功しても何も返らない(RETURN VOID)
postgres=# SELECT heap_force_kill('t1'::regclass, ARRAY['(0,2)']::tid[]);
heap_force_kill
-----------------
(1 row)
-- ２行目のレコードが消えている
postgres=# SELECT ctid, xmin, xmax, * FROM t1;
ctid | xmin | xmax | c1 | c2
-------+------+------+----+-----
(0,1) | 770 | 0 | 1 | AAA
(1 row)
-- 2行目のlp_offが0、lp_flagsが3(LP_DEAD)、lp_lenが0に更新されている
postgres=# SELECT * FROM heap_page_items(get_raw_page('t1', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
1 | 8160 | 1 | 32 | 770 | 0 | 0 | (0,1) | 2 | 2306 | 24 | | | \x0100000009414141
2 | 0 | 3 | 0 | | | | | | | | | |
(2 rows)
このようにレコードを無効化できました。見た目上は単純に２行目のレコードをDELETEしただけに見えます。実際、効果としてはそれに近く、LPで無効化していますがレコードの実体は消えずに残っています。無効化したレコードはVACUUMで回収することができます。
heap_force_kill()はいわばレコードの物理位置を用いた低レイヤーのデータ削除に近く、通常のDELETE処理と異なり各種制約は無視できます(外部参照制約における参照データを削除することが可能)し、トリガーが発動することもありません。
heap_force_freeze()
二つ目はheap_force_freeze()です。heap_force_kill()とは逆に、特定のテーブルの特定のレコードを強制的に可視にします。レコードの指定方法はheap_force_kill()と同じです。
このheap_force_freeze()は内部的に指定したTIDのレコードのヘッダxminにFrozenTransactionId、xmaxにInvalidTransactionIdをセットし、その他可視性に関わるフラグをクリアします。xminやxmaxについてはマニュアルを参照してください。この関数は簡潔に言うと、どのような状態のレコードであれ、強制的にFREEZE済の有効な行の状態にします。
では使い方を見ていきます。
-- 先ほどのテーブルを一旦空にし再度データを投入
postgres=# TRUNCATE t1;
TRUNCATE TABLE
postgres=# INSERT INTO t1 SELECT 1, 'AAA';
INSERT 0 1
postgres=# INSERT INTO t1 SELECT 2, 'BBB';
INSERT 0 1
postgres=# SELECT ctid, xmin, xmax, * FROM t1;
ctid | xmin | xmax | c1 | c2
-------+------+------+----+-----
(0,1) | 791 | 0 | 1 | AAA
(0,2) | 792 | 0 | 2 | BBB
(2 rows)
postgres=# SELECT * FROM heap_page_items(get_raw_page('t1', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
1 | 8160 | 1 | 32 | 791 | 0 | 0 | (0,1) | 2 | 2306 | 24 | | | \x0100000009414141
2 | 8128 | 1 | 32 | 792 | 0 | 0 | (0,2) | 2 | 2306 | 24 | | | \x0200000009424242
(2 rows)
-- この関数は引数にテーブルのOIDとTID配列を取る。t1テーブルの２行目(0,2)を指定して実行
-- 成功しても何も返らない(RETURN VOID)
postgres=# SELECT heap_force_freeze('t1'::regclass, ARRAY['(0,2)']::tid[]);
heap_force_freeze
-------------------
(1 row)
-- ２行目のレコードのxminが792 -> 2 (FrozenXid)に更新
-- これは２行目だけVACUUM FREEZEした状態とあまり変わらない
postgres=# SELECT ctid, xmin, xmax, * FROM t1;
ctid | xmin | xmax | c1 | c2
-------+------+------+----+-----
(0,1) | 791 | 0 | 1 | AAA
(0,2) | 2 | 0 | 2 | BBB
(2 rows)
-- ２行目のレコードのxminとinfomaskが更新されている
postgres=# SELECT * FROM heap_page_items(get_raw_page('t1', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
1 | 8160 | 1 | 32 | 791 | 0 | 0 | (0,1) | 2 | 2306 | 24 | | | \x0100000009414141
2 | 8128 | 1 | 32 | 2 | 0 | 0 | (0,2) | 2 | 2818 | 24 | | | \x0200000009424242
(2 rows)
-- 2行目のレコードをUPDATE
postgres=# UPDATE t1 SET c2 = 'CCC' WHERE c1 = 2;
UPDATE 1
-- ctid(0,2)のレコードが更新され、新規にctid(0,3)の更新後のレコードが追記される
postgres=# SELECT ctid, xmin, xmax, * FROM t1;
ctid | xmin | xmax | c1 | c2
-------+------+------+----+-----
(0,1) | 791 | 0 | 1 | AAA
(0,3) | 793 | 0 | 2 | CCC
(2 rows)
-- pageinspectからは更新されたctid(0,2)のデータが確認可能。
-- xmaxが793(UPDATEしたトランザクションのXID)となっている。
postgres=# SELECT * FROM heap_page_items(get_raw_page('t1', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
1 | 8160 | 1 | 32 | 791 | 0 | 0 | (0,1) | 2 | 2306 | 24 | | | \x0100000009414141
2 | 8128 | 1 | 32 | 2 | 793 | 0 | (0,3) | 16386 | 1794 | 24 | | | \x0200000009424242
3 | 8096 | 1 | 32 | 793 | 0 | 0 | (0,3) | 32770 | 10498 | 24 | | | \x0200000009434343
(3 rows)
-- 更新済で不要レコードとなったctid(0,2)に対してheap_force_freeze()を実施
postgres=# SELECT heap_force_freeze('t1'::regclass, ARRAY['(0,2)']::tid[]);
heap_force_freeze
-------------------
(1 row)
-- ctid(0,2)のxmin, xmaxなどが更新され、再び参照可能になる
-- (c1が主キーだが、c1=2の行が2つあるという主キー制約に違反していることに注意)
postgres=# SELECT ctid, xmin, xmax, * FROM t1;
ctid | xmin | xmax | c1 | c2
-------+------+------+----+-----
(0,1) | 791 | 0 | 1 | AAA
(0,2) | 2 | 0 | 2 | BBB
(0,3) | 793 | 0 | 2 | CCC
(3 rows)
-- ctid(0,2)のxmaxが793から0(InvalidXid)になるなど更新されている。
postgres=# SELECT * FROM heap_page_items(get_raw_page('t1', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
1 | 8160 | 1 | 32 | 791 | 0 | 0 | (0,1) | 2 | 2306 | 24 | | | \x0100000009414141
2 | 8128 | 1 | 32 | 2 | 0 | 0 | (0,2) | 2 | 2818 | 24 | | | \x0200000009424242
3 | 8096 | 1 | 32 | 793 | 0 | 0 | (0,3) | 32770 | 10498 | 24 | | | \x0200000009434343
このようにレコードを可視にできました。本来はヘッダ破損などで正攻法のアクセスができないレコードを強制的に可視にしてサルベージなどを行う用途のものです。ただし前述のように削除済のデータなどを(VACUUMなどで回収される前ならば)可視にすることもできます。これもheap_force_kill()同様に、DBの整合性を簡単に破壊することができるので注意しましょう。
使用上の注意点
最後に、pg_surgeryの利用上の注意点をまとめておきます。(既に色々と注意点を書きましたが、改めて整理しておきます)
非可逆かつ非トランザクショナルな処理であること
heap_force_kill()、heap_force_freeze()ともに、実施した結果をもとに戻すことはできません。一旦kill/freezeしたらそのままです。また、BEGIN; heap_force_freeze(); ROLLBACK; といったトランザクションブロックで囲ってロールバックすることもできません。関数を実施したタイミングで作用します。また、別トランザクションの未コミットのデータもkill/freezeが可能です。
万が一、重要なデータを扱う環境で使わざるを得ない場合は物理バックアップを取得しておくと安心です。
WALを生成する
heap_force_kill()、heap_force_freeze()は更新処理となるためkill/freezeしたデータを含むページのWALが生成されます。VisibilityMapの変更が行われればそのWALも生成されます。VACUUMが不要領域を回収したページのWALを生成するのと同様です。そのため、レプリケーション構成のスタンバイやPITRなどに影響することを意識しておくと良いでしょう。
各種制約チェックやトリガをすり抜ける
利用例でいくつか示しましたが、通常のUPDATEやDELETEではないため、全ての制約は無視してkill/freezeが可能です。また全ての更新用のトリガもすり抜けます。整合性維持のため、kill/freeze対象のデータに関する制約をユーザ側で把握してから使うようにしましょう。
想定外の事象が起こる
heap_force_kill()、heap_force_freeze()は正常では起こらないレコードの状態を作り出すため、場合によっては想定外の事象が起こることがあります。詳細は省きますが、例えばkill/freezeしたレコードを含むテーブルについて、DELETEできないレコードが発生したり、そのテーブルへのVACUUMが無限ループに陥り終わらなくなる、という状態になることもあります。
LPがNormalのものだけ処理可能
heap_force_kill()、heap_force_freeze()とも、指定したTIDのLPの状態がNORMAL
のみ作用します。前述のようにLPは(UNUSED, NORMAL, REDIRECT, DEAD)の4種の状態のどれかです。SELECTなどで見えるレコードは基本的にNORMALですし、(状況次第ですが)pageinspectのlp_flag(1がNORMAL)で確認もできます。
なお指定したTIDのLPがNORMALではなかった場合、以下のようにNOTICEでスキップした旨のメッセージが出て処理はスキップされます。
postgres=# SELECT heap_force_freeze('t1'::regclass, ARRAY['(0,2)']::tid[]);
NOTICE: skipping tid (0, 2) for relation "t1" because it is marked dead
heap_force_freeze
-------------------
(1 row)
注意点として挙げましたが、ERRORで止まることはないという利点にもなります。とりあえず大量のTIDを指定してある一定範囲のレコードを一括でkill/freezeしたい場合はこの振る舞いを利用すると良いでしょう。
終わりに
pg_surgeryの使い方を解説してみました。あまり気軽に使う機能ではないと思いますし、できればお世話になりたくはないものです。ただし非常に強力な機能ですし、クラウド上のマネージドなDBのようにファイルをさわれない環境では(ユーザとして実施可能な)サルベージの唯一の選択肢になるかもしれません。こういった機能の仕様も押さえておくと、いざという時に助かるかもしれませんね。