昨日、仮想マシンのPostgreSQLにホストOSから接続する環境を整えました。接続に加え、ホスト側とのファイル入出力もできたら便利。クエリでのCOPY、pg_read_file、あとPL/Rストアドでのグラフ出力など、仮想側でクエリするだけでホスト側と入出力したいです。というか5月28日の記事はその環境を作って書いたので、今日は設定の経過をメモ。ホスト側はWindows 7 32bit + Portable VirtualBox 4.3.6、仮想側はCentOS 6.6。
Contents

VirtualBoxで共有フォルダ設定
事前に、仮想側CentOSにVirtualBox Guest Additionsのインストールが必要。手順は6月8日の記事の後ろの方に書きました。次に仮想マシンの設定画面から共有フォルダの所 ↓ を開き、ホストOSの任意の場所に対し、好きなフォルダ名を付けて作成。今回はwin_rという名にしました。オプションのAuto-mountとMake Permanentを入れないと、マシン起動のたびに設定が必要になります。


設定したら仮想マシンを（再）起動。下は共有フォルダ設定画面で出るツールチップですが。Auto-mountにしていれば自動的に［/media/sf_共有フォルダ名］でアクセスでき、とくに気にしなくて済みます。


共有フォルダへの読み書き権限を、PostgreSQLユーザに付与
以下、ユーザpostgresに付与する前提です。共有フォルダの所有グループはvboxsfなので、それにpostgresを加えます。…というのを、Qiitaでの投稿（VirtualBoxで共有フォルダにアクセス権限がないと言われるときの対処）で知るまで嵌まってました（汗
作業前後の確認を含めて、ターミナルでの具体的なコマンド例です ↓
# root権限で共有フォルダの中を見られることを確認
sudo ls /media/sf_win_r

# postgresをグループに加える（必須な作業は、これだけ）
sudo gpasswd -a postgres vboxsf

# グループに追加されたのを確認 vboxsf:x:492:postgres のようになっていればOK
cat /etc/group

# postgresユーザとして、共有フォルダ内を見たりテキストを書き込めるか確認
su - postgres
ls /media/sf_win_r
echo 1 > /media/sf_win_r/test.txt


共有フォルダへのシンボリックリンクを、PostgreSQLのデータディレクトリに置く
これは、一つには利便性のため（対象とするファイルを相対パスで短く書ける）、もう一つの理由はpg_read_fileなどPostgreSQLのデータディレクトリ以下のみにアクセスできる関数も、共有フォルダ内を読み取れるようにするため。lnコマンドで下記のように設定します。
# データディレクトリの場所を確認（環境によって異なる）
ls /var/lib/pgsql/9.3/data

# root権限でシンボリックリンクを設定（必須な作業は、これだけ）
# sudo ln -s /media/sf_win_r /var/lib/pgsql/9.3/data/win_r

# postgresユーザとして、シンボリックリンク経由で共有フォルダへ読み書きできるか確認
su - postgres
ls /var/lib/pgsql/9.3/data/win_r
echo 2 > /var/lib/pgsql/9.3/data/win_r/test.txt

以上の設定を反映するためPostgreSQLを再起動して、準備終わり。
service postgresql-9.3 restart


PostgreSQL再起動、動作確認
昨日VirtualBoxのポートフォワーディングで設定した仮想側PostgreSQLのポート番号を使い、ホスト側psqlから接続します。
psql -U postgres -p 5436

↓pg_ls_dirという、指定されたフォルダ直下の中身を返す関数をテスト。ファイルもディレクトリも区別なくその名前が返るだけですが（サブディレクトリ内も見ない）、確かにホスト側のフォルダにアクセスできました。
SELECT pg_ls_dir('win_r');


↓ 次はもう少し実用的に、pg_read_file関数でテキストファイルの中身を一括読み込みするテスト。ファイルの改行コードがCRLFで、psqlだとCRが明示され、pgAdminのクエリツールではCRが省略されました。設定がうまくいっていないと、ERROR: could not stat file "win_r/test.txt": Permission deniedと出ます。
SELECT pg_read_file('win_r/test.txt');


↓ 最後に、COPY ... TOを使って書き込みテスト。ファイルを絶対パスで指定しないといけないので（COPY ... FROMでは相対パスで可）、共有フォルダのパスを直接指定する方が楽です。二つ目の画像が、出力されたTSVをホスト側のエディタで開いたところ。
COPY (SELECT * FROM pg_tables)
TO '/media/sf_win_r/pg_tables.tsv'
-- TO '/var/lib/pgsql/9.3/data/win_r/pg_tables.tsv'
(DELIMITER E'\t', FORMAT csv, HEADER TRUE);