Edited at

PostgreSQLでバイナリファイルを扱う

More than 1 year has passed since last update.

ファイルをサーバに格納する方法のひとつとして、DBMS(ここではPostgreSQL)に格納する方法を調べました。

今回、格納したいデータは数MB程度小さいので、ファイルシステムに格納するより、DBの方が管理が簡単そうだし、メモリ上も問題ないかと思いましたので。

要件は、

・バイナリデータ(PDFファイルやWordファイルを想定)を読み書きできること。

・Java(MyBatis)から読み書きできること。

・SQL(psql)から読み書きできること。テストデータ等を気軽に作れるように。

・単体試験用に、バイナリデータがDBUnitのXML形式で保存できること。


PostgreSQLにおけるデータ型

bytea型と、ラージオブジェクトの2つがあるようです。


bytea型

byteaは普通の型。1GBまでOKなようです。

https://www.postgresql.jp/document/9.6/html/datatype-binary.html

上記公式サイトでは最大サイズが読み取れなかったのですが、Let's Postgresの説明にありました。(下記記事は違う主旨なのですけどね)

https://lets.postgresql.jp/documents/technical/text-processing/1

汎用ファイルアクセス関数を用いると、SELECT文などでファイルシステム上のファイルを読むことが可能で、そのデータをINSERTすれば、DBに格納できます。

https://www.postgresql.jp/document/9.6/html/functions-admin.html#FUNCTIONS-ADMIN-GENFILE

サンプルがLet's Postgresに載っていました。

https://lets.postgresql.jp/documents/technical/gen_data/1


ラージオブジェクト

ラージオブジェクトは、何か特別な格納のされ方になるようです。

こちらも、Let's Postgresに解説されています。

https://lets.postgresql.jp/documents/technical/large_objects

公式サイトにも、1セクション使って解説が載っています。OS上のファイルのインポート・エクスポートも説明があります。

https://www.postgresql.jp/document/9.6/html/largeobjects.html


で、どちらにする?

今回格納したいデータは数MB程度小さいので、簡単そうなbyteaにします。

ラージオブジェクトは別テーブルに格納されるようなので、バックアップしたり、テストデータを用意したりといった作業が面倒なのかなと思いました。


ではbyteaを使ってみよう

早速使ってみましょう。


SQLでファイルを読み込み、テーブルにINSERT

DBにテーブルを用意します。特別なことはなく、格納するカラムの型を「bytea」とするだけでした。

続いて、Windowsのファイルシステム上にあるファイルを、SQLでテーブルにINSERTしてみます。

ファイルは、データベースクラスタのディレクトリに格納する必要があるとのことなので、まずファイルリストを取得してみます。

select pg_ls_dir('.');

こんなリストが取れます。私の環境では「C:\Program Files\PostgreSQL\9.4\data」にあるディレクトリとファイルの一覧に一致します。

"base"

"global"
"pg_clog"
"pg_dynshmem"
"pg_hba.conf"
"pg_ident.conf"
"pg_log"
"pg_logical"
"pg_multixact"
"pg_notify"
"pg_replslot"
"pg_serial"
"pg_snapshots"
"pg_stat"
"pg_stat_tmp"
"pg_subtrans"
"pg_tblspc"
"pg_twophase"
"PG_VERSION"
"pg_xlog"
"postgresql.auto.conf"
"postgresql.conf"
"postmaster.opts"
"postmaster.pid"

それでは、「postgresql.conf」をテーブルに読み込んでみます。

pg_read_binary_file関数で、ファイル内容をbytea型で取得することができます。

SELECT pg_read_binary_file('postgresql.conf') 

これをINSERT文に渡してあげればOKです。例えば、id(integer)とbigdata(bytea)というカラムを持つdataテーブルにINSERTするときは、下記のようにSELECTした結果をINSERTすればいいです。ここでは便宜上、idの値は固定的に1としていますが深い意味はありません。

INSERT INTO data (id, bigdata)

SELECT 1, pg_read_binary_file('postgresql.conf')


SQLでテーブルからSELECTしファイルに書き出し

先ほどINSERTしたデータを、ファイルに書き出すには、COPYコマンドがいいのかな? ここでは、ファイル名を固定で指定していますが、テーブルにINSERTするときに、ファイル名を格納するカラムを作っておいて、その名前で書き出せば復元できますね。

copy ( select bigdata from data )

to 'c:\home\output.dat' (format binary)

MyBatisからの操作はこれから調べていきます。