作成したPostgreSQL拡張 pg_pageinspect_plus の紹介記事です。
PostgreSQL の生データ解析
PostgreSQLのデータが物理レベルで破損したみたい、という場合に、どう壊れたのか調べたり、救出できるデータは無いかを探るには、contribモジュール として付属している pageinspect 拡張を使うのが便利です。
PostgreSQLのテーブルデータは （デフォルトで）8KB のページ（ブロックともいう）が連なる 1つのファイル（1GBを超えると分割される）に格納されています。
テーブルに対応したファイルの各ページについて、pageinspect を使って以下のように解析できます。
db=# CREATE EXTENSION pageinspect;
CREATE EXTENSION
db=# SELECT * FROM public.t1;
id | txt | ts | bool | i8
-----+-------+---------------------+------+--------------
100 | ABCDE | 2020-08-01 12:00:00 | t | 98765432
101 | abcde | 2025-08-01 12:00:00 | f | 554433221100
(2 rows)
db=# SELECT tuple_data_split('public.t1'::regclass, t_data, t_infomask, t_infomask2, t_bits), *
FROM heap_page_items(get_raw_page('public.t1', 0));
tuple_data_split | 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
---------------------------------------------------------------------------------------+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+----------+-------+------------------------------------------------------------------------------------
{"\\x64000000","\\x0d4142434445","\\x00f0e069ce4e0200","\\x01","\\x780ae30500000000"} | 1 | 8128 | 1 | 64 | 501 | 0 | 0 | (0,1) | 5 | 11010 | 24 | | | \x640000000d414243444500000000000000f0e069ce4e02000100000000000000780ae30500000000
{"\\x65000000","\\x0d6162636465","\\x00b01b444bde0200","\\x00","\\xec91cb1681000000"} | 2 | 8064 | 1 | 64 | 502 | 0 | 0 | (0,2) | 5 | 11010 | 24 | | | \x650000000d616263646500000000000000b01b444bde02000000000000000000ec91cb1681000000
{"\\x66000000","\\x0d5858585858","\\x01cefdce0a4f0200",NULL,"\\x0000000000000000"} | 3 | 8008 | 1 | 56 | 549 | 550 | 0 | (0,3) | 8197 | 259 | 24 | 11101000 | | \x660000000d585858585800000000000001cefdce0a4f02000000000000000000
(3 rows)
get_raw_page関数で指定テーブルの何番目のページについて、バイト列を bytea型データで取得して、それを heap_page_items関数で解析します。
さらに、heap_page_item関数が返す行データ部分（t_data）を tuple_data_split関数が列ごとに分割して配列で返してくれます。
テーブル指定 → 解析結果 ではなく、テーブルのページ指定 → バイト列 → 解析結果 という形態になっているため、テーブルのページデータを他の PostgreSQLサーバ（ただし、同アーキテクチャ、同OS、同ビルドオプション）に bytea型のデータとして持ち込んで解析することも可能です。
2行のデータに対して 3行が返っているのは、削除済み等で SELECT では返らない行も含まれるためです。また、これは 0番目ページに含まれるデータですので、複数ページにわたる、より大きなテーブルの場合には各ページについて調べることになります。
バイナリデータを人間が読むには
pageinspectモジュールの関数を使って列ごとの物理格納データを参照できました。
しかし、できることなら、バイト列ではなく、人間に読みやすい形でデータ内容を確認したいところです。
\x0d4142434445 が 'ABCDE' というのは人によっては一目でわかるかもしれません。
また、「SET bytea_output TO escape;」とすれば出力は '\015ABCDE' になりますので一応は見分けがつきます。
しかし、'\x00f0e069ce4e0200' が '2020-08-01 12:00:00' というのは、ちょっと分かりません。
各データ型の格納されたバイト列から、SQLで扱える各データ型の値に変換する方法が、PostgreSQL機能を使って実現できないものか色々試したのですが、うまい方法が見つかりませんでした。
そこで力業です。そのための拡張モジュールを作成しました。
pg_pageinspect_plus
pg_pageinspect_plus は、よく使われるデータ型に対して、bytea型の格納形式バイト列から、そのデータ型の値に変換する関数群を提供します。
また、pageinspect の tuple_data_split関数の出力を読み込んで、各列の値を各データ型の表示形式の文字列の配列にして返す関数 tuple_data_parse も提供します。
インストール手順
インストール手順は標準的な PostgreSQL の拡張モジュールの導入手順通りです。
PostgreSQLのヘッダファイルがある状態（rpmパッケージからの導入なら postgresql12-devel などのパッケージが導入済み）で、PostgreSQLのコマンド群にパスが通った状態で、以下を実行します。
$ cd pg_pageinspect_plus
$ make
$ su -c 'make install'
開発は 13beta2 で行いましたが、比較的新しい PostgreSQL であれば動作するはずです。
動作させる
先ほどの tuple_data_split関数の結果をtuple_data_parse に喰わせると以下のようになります。テーブル定義からデータ型を読み取って、各列のバイト列を各データ型のテキスト表現に変換してくれます。
db=# CREATE EXTENSION pg_pageinspect_plus;
CREATE EXTENSION
db=# SELECT tuple_data_parse('public.t1'::regclass, tuple_data_split('public.t1'::regclass, t_data, t_infomask, t_infomask2, t_bits))
FROM heap_page_items(get_raw_page('public.t1', 0));
tuple_data_parse | 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
------------------------------------------------------+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+----------+-------+------------------------------------------------------------------------------------
{100,ABCDE,"2020-08-01 12:00:00",true,98765432} | 1 | 8128 | 1 | 64 | 501 | 0 | 0 | (0,1) | 5 | 11010 | 24 | | | \x640000000d414243444500000000000000f0e069ce4e02000100000000000000780ae30500000000
{101,abcde,"2025-08-01 12:00:00",false,554433221100} | 2 | 8064 | 1 | 64 | 502 | 0 | 0 | (0,2) | 5 | 11010 | 24 | | | \x650000000d616263646500000000000000b01b444bde02000000000000000000ec91cb1681000000
{102,XXXXX,"2020-08-04 12:03:14.428417",NULL,0} | 3 | 8008 | 1 | 56 | 549 | 550 | 0 | (0,3) | 8197 | 259 | 24 | 11101000 | | \x660000000d585858585800000000000001cefdce0a4f02000000000000000000
(3 rows)
これで、SELECT結果との対応付けが分かりやすくなりました。
個別にバイト列を各データ型の値に変換する例は以下の通りです。
bytea2～という関数群が提供されます。
db=# SELECT bytea2text('\x0d5858582020');
bytea2text
------------
XXX
(1 row)
db1=# SELECT bytea2timestamptz('\x90fbb5cbe24e0200');
bytea2timestamptz
----------------------------
2020-08-02 21:19:00.698+09
(1 row)
db=# SELECT bytea2interval('\x0010acd1530000000000000000000000');
bytea2interval
----------------
100:00:00
(1 row)
db=# SELECT bytea2float8('\x33f68845cac02340');
bytea2float8
--------------
9.87654321
(1 row)
今のところ、対応しているデータ型は、timestamp、timestamp with time zone、interval、integer（int、int4）、bigint（int8）、boolean、text、char、varchar、json、real（float4）、double precision（float8）だけです。
このほか格納形式がtext型と同じデータ型は、bytea2text関数が利用できます。
関数の一覧は本拡張モジュールの README を参照してください。