【PostgreSQL】psqlのCOPYコマンドに変数で絶対パスを渡す

はじめに

サーバーリプレース作業にて、データベースを Oracle 11g から PostgreSQL 9.6 に移行作業をしています。
Oracle 用に作成していたバッチを、PostgreSQL 用にしていく作業で同僚が嵌っていたので、休日を利用して調査してみました。

嵌りポイント

pg_dump コマンドではスキーマやtオプションでテーブル単位でダンプファイルが作成できます。
しかし、条件を指定して一部のテーブルデータのダンプファイルは作成できません。

ネットで軽く調べてみると COPY コマンドを使用すればいいことが分かりました。

COPY  (SELECT * FROM sample_table WHERE id = '123') TO 'sample.dump';

これで出来そうなので同僚に引き渡し他作業をしていたところ、何やら悩んでいるようでした。聞いてみるとダンプファイルが作成できないとのこと。

絶対パス問題

TOに指定できるのは絶対パスのみで相対パスだと「relative path not allowed for COPY to file」エラーとなってしまうのです。
バッチを実行した下位フォルダ配下にダンプファイルを作成したいので絶対パスにはしたくないわけですよね。
また作りとして、バッチファイルとSQLファイルを分けて作成したいのです。

※相対パスはpsql起動時のカレントディレクトリが基点なら指定できるようです。

対応

試行錯誤した結果、psqlのvオプション(変数)を使用して絶対パスを渡すことで解決しました。
変数はSQLのパラメータ化クエリのプレースホルダのように条件にしか使用できないという思い込みがあったのですが、どこでも使用できます。

Sample.bat

@ECHO OFF
cd /d %~dp0

set PGPASSWORD=postgres
set curPath=%~dp0

psql -h localhost -U postgres -d postgres -f "Sample.sql" -v value='123' -v inputpath="'%curPath%dump/sample.dump'"

Sample.sql

SET CLIENT_ENCODING TO 'UTF8';
COPY (SELECT * FROM sample_table WHERE id = :value) TO :inputpath;

writing: Permission denied

実行した際に、writing: Permission deniedエラーが出るようなら、出力フォルダに書き込み権限が必要です。
PostgreSQLのユーザーで実行されるため、出力先がPostgreSQLのユーザーで書き込めるようにします。
フォルダにNETWORK SERVICEユーザーを追加して「フル コントロール」にする。
※Everyoneで「フル コントロール」になっている場合は不要です。

関連調査

この件を調べている際に他にも分かったことがあったのでメモ用に記載しておきます。

COPY コマンドと \COPY コマンドの違い

PostgreSQL は COPY コマンドと \COPY コマンドの2種類がありました。

COPYコマンドではサーバー内でファイルの入出力を行うのに対し、\COPYコマンドはネットワークを介して入出力データのやり取りを行います。よって、データサイズが大きい場合は、COPYコマンドの方がパフォーマンス的に有利となります。

COPY コマンド

PostgreSQL データベースが稼働しているサーバー上のファイルの入出力を行うためのものです。

• 権限：スーパーユーザーしか実行できません。
• ファイル作成場所：サーバ側／絶対パスかpsqlを起動した際のパスからの相対パスで指定します。
• ファイル属性：サーバーを起動しているOSユーザの所有

alter role (ユーザー名) SUPERUSER;

\COPY コマンド

データベース接続を行ったクライアント側でファイルの入出力を行うものです。

• 権限：一般ユーザでも実行できる。
• ファイル作成場所：クライアント側／絶対パスまたは相対パス
• ファイル属性：psqlを起動したOSユーザの所有

変数の指定方法

シングルクォーテーションはSQL側で :'value' のようにも指定できます。

Sample.bat

psql -h localhost -U postgres -d postgres -f "Sample.sql" -v value=123 -v inputpath="'%curPath%dump/sample.dump'"

Sample.sql

SET CLIENT_ENCODING TO 'UTF8';
COPY (SELECT * FROM sample_table WHERE id = :'value') TO :inputpath;

LIKE句を指定する際にバッチ内の%のエスケープとして%%で value='12%%' とします。

Sample.bat

psql -h localhost -U postgres -d postgres -f "Sample.sql" -v value='B%%' -v inputpath="'%curPath%dump/sample.dump'"

Sample.sql

SET CLIENT_ENCODING TO 'UTF8';
COPY (SELECT * FROM sample_table WHERE id LIKE :value) TO :inputpath;

IN句の指定する際に一つの変数 value='123','125' で出来ます。

Sample.bat

psql -h localhost -U postgres -d postgres -f "Sample.sql" -v value='123','125' -v inputpath="'%curPath%dump/sample.dump'"

Sample.sql

SET CLIENT_ENCODING TO 'UTF8';
COPY (SELECT * FROM sample_table WHERE id IN(:value)) TO :inputpath;

変数はsample_:nameのように末尾なら結合して使用できます。
PostgreSQL :: psql で 変数に値を渡す

Sample.bat

psql -h localhost -U postgres -d postgres -f "Sample.sql" -v name=table -v value=123 -v inputpath="'%curPath%dump/sample.dump'"

Sample.sql

SET CLIENT_ENCODING TO 'UTF8';
COPY (SELECT * FROM sample_:name WHERE id = :'value') TO :inputpath;

相対パスの指定方法

\COPY コマンドを使用すれば相対パスで指定できます。
サーバー上で動作させるならこの指定方法であっても動作します。

ただ残念ながら、この方法だと変数を指定することができません。

Sample.sql

SET CLIENT_ENCODING TO 'UTF8';
\COPY (SELECT * FROM sample_table WHERE id = '123') TO 'dump/test.dump';

Bashなら下記の方法で変数を使う案もあるようです。
posgresql \copyのfromに変数(csvのパス)を使いたい

\set copycmd '\\copy hoge from ':inputfile' (format csv, header true);'
:copycmd

【2023/08/09追記】
バッチ内で変数を使用して、psql -cオプションで実行すればいいことに気が付いた。
SET CLIENT_ENCODING TO 'UTF8';は、この方法だとセット出来ないかな。

Sample.bat

@ECHO OFF
cd /d %~dp0

SET PGPASSWORD=postgres

SET value=123
SET inputpath=dump/sample.dump
SET copyCmd=\COPY (SELECT * FROM sample_table WHERE id = '%value%') TO '%inputpath%'

psql -h localhost -U postgres -d postgres -c"%copyCmd%"

最後に

SQLを使用する際には速度向上やインジェクション対策用にパラメータ化クエリとしてプレースホルダを使用しているので、そんなことには使えないんじゃないかという思い込みで試さなかったりします。なんで、ちゃんと調べないと駄目ですね。

psqlのオプションは大文字と小文字で機能が違いますので注意が必要です。
COPY コマンドと \COPY コマンドとか機能が違うってややこしいわい。