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

ファイルをサーバに格納する方法のひとつとして、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からの操作はこれから調べていきます。