TECH BLOG
技術ブログ

ARTICLE

  • 2024-06-20 PostgreSQL

    PostgreSQLにバイナリデータを格納/出力する

プロジェクトやシステムによっては、画像や pdf などのドキュメントを PostgreSQL のテーブルに格納したいという要件があるかもしれません。
PostgreSQL には、画像や pfd などのドキュメントを読み取るための pg_read_binary_file という名前の関数が用意されています。
本記事では PostgreSQL でバイナリファイルを扱う際の具体的な手順や注意点などを解説いたします。

pg_read_binary_file 関数を利用した画像の取り込みと出力

では実際に pg_read_binary_file 関数を利用して jpg 画像をテーブルに取り込みと出力の挙動を確認します。

環境

検証に利用した環境は

  • CentOS 7.9
  • PostgreSQL 16.3

となります。

1. バイナリデータを格納するためのテーブルを作成

まずはバイナリデータ(画像データ)を格納するためのテーブルを作成します。
PostgreSQLでバイナリデータを管理する場合には、bytea 型を利用するか、ラージオブジェクトを利用するかのどちらかになります。
1 GB 未満のデータであれば、bytea 型を利用するのが便利ですので、今回は bytea 型のカラムをもつテーブルを作成します。

postgres=# CREATE TABLE image_box (id INTEGER PRIMARY KEY, dat BYTEA);
CREATE TABLE

image_box という名前のテーブルで、id と bytea 型の dat カラムを持たせました。

2. バイナリデータを格納

INSERT 文で画像データをテーブルに格納します。
画像のパスは /tmp/logo.jpg です。
pg_read_binary_file 関数の引数に画像のパスを指定することで、そのファイルのバイナリデータを取得できます。

postgres=# INSERT INTO image_box SELECT 1, pg_read_binary_file('/tmp/logo.jpg');
INSERT 0 1

無事にテーブルに格納できました。

3. バイナリデータを出力

テーブルに格納された jpg ファイルを COPY 文で取り出します。
FORMAT オプションには忘れずに binary を指定します。

postgres=# COPY (SELECT dat FROM image_box WHERE id = 1) TO '/tmp/output_logo.jpg' WITH (FORMAT binary);
COPY 1

テーブルに格納していたバイナリデータを output_logo.jpg という名前で出力しました。

4. 出力したバイナリデータの確認

最後に ls コマンドで画像のサイズを比較します。

$ ls -l
合計 416
-rw-r--r-- 1 postgres postgres 209243  6月 9 15:44 logo.jpg
-rw-r--r-- 1 postgres postgres 209270  6月 9 17:08 output_logo.jpg

logo.jpg のサイズは 209,243 バイトで、output_log.jpg のサイズは 209,270 バイトとなりました。
テーブルに格納する前と、テーブルから出力した後でサイズが違っています。

COPY コマンドで出力したバイナリデータにはヘッダが付与される

この微妙なサイズの違いは PostgreSQL の COPY コマンドの仕様となります。
公式ドキュメントの説明を抜粋します。

【 PostgreSQL ドキュメント - COPY - 】
https://www.postgresql.jp/document/16/html/sql-copy.html#id-1.9.3.55.9.4

binaryファイルの形式は、ファイルヘッダ、行データを含む0以上のタプル、ファイルトレーラから構成されます。

つまり COPY コマンドでバイナリデータを出力する場合、自動的にファイルヘッダとファイルトレーラが PostgreSQL によって付与されることになります。

ファイルヘッダは

ファイルヘッダは15バイトの固定フィールドとその後に続く可変長ヘッダ拡張領域から構成されます。

という形式になっています。

ファイルトレーラは

ファイルトレーラは、16ビットの整数ワードで構成され、-1が入っています。

となっています。
それぞれの詳細については上記の公式ドキュメントを確認していただくとして、COPY コマンドでバイナリデータを出力した場合には自動的にデータの先頭と末尾にデータが付与されるということが分かりました。

5. COPY コマンドで付与されるファイルヘッダ、ファイルトレーラを除去する

ファイルヘッダ、ファイルトレーラが付与された状態では画像として認識してくれなくなります。そのため、ファイルヘッダとファイルトレーラを除去する必要がありますが、確認した限りでは標準機能には用意されていませんでした。
そこで、少々強引ですが dd コマンドでファイルヘッダとファイルトレーラを手動で削ってみて、元のデータに復元することにします。PostgreSQL 16.3 では、ファイルヘッダは 25 バイト、ファイルトレーラは 2 バイトとなっているようですので、実行する dd コマンドは下記のようなものとなります。
(※バージョンによって ファイルヘッダとファイルトレーラのバイト数は変動する可能性がありますので、ご注意ください。特にファイルヘッダは可変長な項目を含んでいますので、別のバージョンではサイズが違う可能性があります。)

$ dd if=output_logo.jpg bs=1 skip=25 count=$(($(stat -c '%s' output_logo.jpg)-25-2)) > output_logo2.jpg 2>/dev/null

dd コマンドの結果を output_logo2.jpg として出力しました。
ls コマンドでサイズを確認します。

$ ls -l
合計 624
-rw-r--r-- 1 postgres postgres 209243  6月 9 15:44 logo.jpg
-rw-r--r-- 1 postgres postgres 209270  6月 9 17:08 output_logo.jpg
-rw-rw-r-- 1 postgres postgres 209243  6月 9 17:59 output_logo2.jpg

無事に logo.jpg と output_logo2.jpg のサイズが同じであることを確認できました。

バイナリデータとして格納するのを諦める

バイナリデータを COPY コマンドで出力しようとすると、どうしてもファイルヘッダとファイルトレーラが付与されてしまいます。
上記の通り dd コマンドで除去することは可能ですが、ぱっと見わかりにくいコマンドなのが気になります。バイナリデータではなくテキストデータとして格納すれば、でファイルヘッダとファイルトレーラは付与されませんので、その方法も紹介します。

バイナリデータをテキストデータに変換してテーブルに格納(と出力)する

バイナリデータをテキストデータに変換するには xxd コマンドや base64 コマンドなどで実現可能です。以下では base64 コマンドを利用してバイナリデータをテキストデータに変換して、テーブルに格納し、それを出力します。

$ base64 logo.jpg > logo.txt

base64 コマンドで logo.jpg をテキストデータ(64進数化した数値)に変換し、logo.txt として保存しました。
これをテーブルに格納します。

postgres=# CREATE TABLE text_box (id INTEGER PRIMARY KEY, dat TEXT);
CREATE TABLE

postgres=# INSERT INTO text_box SELECT 1, pg_read_file('/tmp/logo.txt');
INSERT 0 1

text_box というテーブルを作成し、logo.txt を格納しました。
通常のテキストファイルですので、pg_read_file 関数を利用しています。
では COPY コマンドで格納したテキストデータを出力します。

postgres=# COPY (SELECT dat FROM text_box WHERE id = 1)  TO '/tmp/output_logo.txt' WITH (FORMAT csv);
COPY 1

出力されたテキストファイルを base64 コマンドを利用してバイナリデータに戻します。

$ sed 's/^"//; s/"$//' output_logo.txt | base64 -d > output_logo3.jpg

バイナリデータ化したものは output_log3.jpg という名前で保存しました。

※ COPY コマンドで出力する際に FORMAT csv とすると、データの前後にダブルコーテーションが付与されるので、それを除去するために sed コマンドを実行しています。
COPY コマンドで FORMAT text とすると、データの前後にダブルコーテーションは付与されませんが、今度は改行が\nとなって文字列としてデータに記載されるようになりました。\nを文字列として出力しないようにする方法を見つけられませんでしたので、今回は FORMAT csv で出力し、前後のダブルコーテーションを除去する方法を止む無くとっています。

$ ls -l
合計 1392
-rw-r--r-- 1 postgres postgres 209243  6月 9 15:44 logo.jpg
-rw-rw-r-- 1 postgres postgres 282663  6月 9 17:12 logo.txt
-rw-r--r-- 1 postgres postgres 209270  6月 9 17:08 output_logo.jpg
-rw-r--r-- 1 postgres postgres 282666  6月 9 18:15 output_logo.txt
-rw-r--r-- 1 postgres postgres 209243  6月 9 17:59 output_logo2.jpg
-rw-rw-r-- 1 postgres postgres 209243  6月 9 18:20 output_logo3.jpg

無事に logo.jpg と output_logo3.jpg のサイズが同じになることを確認できました。

まとめ

以上が PostgreSQL でバイナリデータを操作する際の挙動の解説となります。
注意点としては COPY コマンドでバイナリデータを出力する際にヘッダやトレーラが付与されてしまうという点です。
バイナリデータを扱うことは少ないと思いますが、いざ扱うとなった際に困らないように手順や挙動は予め知っておくのが推奨となります。
※ 今回は COPY コマンドで出力したデータに対して手を加えていますが、もう少しスマートな方法が確認できれば、本記事の内容を更新する予定です。

CATEGORY

ARCHIVE

PostgreSQLの
ハイパフォーマンスチューニングのご相談は
株式会社インサイトまで
お気軽にお問い合わせください。

CONTACT