INDEX
概要（愚痴）
PostgresのCOPY TOでCSV吐きたかったんだけど、しゃあないからSTDOUTとリダイレクトで出した。もうアホかと。#社畜ちゃん台詞メーカー https://t.co/rHdsYHelTL pic.twitter.com/0aJDuizle5
— まあにい (@mahny_b) 2017年6月2日
自分にはPostgreSQLの知識は無いと共有はしてたんだけど、DBからCSVでデータが欲しいと頼まれて、でもセキュリティーポリシーから管理者権限は付与できないとか、無茶振りされたけど何とかなったので、その方法を書きます。
サックリやり方だけ見たい場合は、【まとめ】を見てください。
追記（2017/06/05）：【\copy】で出来るのでご注意。
試行錯誤やらはせっかくの黒歴史（）なので残しておきます。
目的
- 管理者権限がないユーザを使って、PostgreSQLのデータをCSV出力する
環境
mahny@mahny-VirtualBox:/tmp/20170604-postgres-dump$ psql -c "select version();" qiita
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)
（※OSはLubuntu16）
前提条件
アカウント情報
qiita=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
mahny | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
データベース情報
qiita=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
qiita | mahny | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
テーブル情報
qiita=> \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+-------
public | t_m_users | table | mahny
(1 row)
qiita=> select * from t_m_users;
id | name | updated_date
----+-------+--------------
1 | mahny | 2017-06-04
2 | cia | 2017-06-04
3 | mary | 2017-06-04
4 | kite | 2017-06-04
5 | lucia | 2017-06-04
(5 rows)
試行錯誤
まずは、ググッてすぐに見つかる「COPY TO」で出す方法を試す。
qiita=> copy t_m_users to '20170604.csv' with csv delimiter ',' force quote *;
ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
スーパーユーザーじゃないとダメとか言ってる。
あ、でもなんかHINTって出てんね。
公式ドキュメントのCOPYを見ると、STDINとかSTDOUTを使うといいらしいので、試してみる。
qiita=> copy t_m_users TO stdout with csv delimiter ',' force quote *;
"1","mahny","2017-06-04"
"2","cia","2017-06-04"
"3","mary","2017-06-04"
"4","kite","2017-06-04"
"5","lucia","2017-06-04"
あ、出た。
ってことは、対話ツールの外に持っていければいいんかな。
$ psql -c "copy t_m_users to stdout with csv delimiter ',' force quote *;" qiita > dump.csv
$ ll
合計 12
drwxrwxr-x 2 mahny mahny 4096 6月 4 21:30 ./
drwxrwxrwt 12 root root 4096 6月 4 21:25 ../
-rw-rw-r-- 1 mahny mahny 121 6月 4 21:30 dump.csv
$ cat dump.csv
"1","mahny","2017-06-04"
"2","cia","2017-06-04"
"3","mary","2017-06-04"
"4","kite","2017-06-04"
"5","lucia","2017-06-04"
おお、うまく行った。
つまり、PostgreSQLから直接ファイルに出すのではなく、一旦画面（STDOUT）に出して、それをリダイレクトすればいいってことかー。
多分、今回試してないけどインポートも同じ考えなんだべな。
まとめ
- COPY TOの結果を画面に標準出力させ
- 画面に出力したものを、リダイレクトでファイルに書き込む
$ psql -c "COPY [テーブル名] TO STDOUT WITH DEMILITER ',' FORCE QUOTE *;" [DB名] > [CSVファイル名]
例)
$ psql -c "copy t_m_users to stdout with csv delimiter ',' force quote *;" qiita > dump.csv
（※区切り文字やらダブルクォート付きとかは適当に読み替えてね）
追記（2017/06/05）
通院している病院の待合室でコメントに気づいてノートでサッと検証したところあっさり出来ました。（knoguchiさん感謝）
$ psql -c "\copy t_m_users to 'dump.csv' with csv delimiter ',' force quote *;" qiita
COPY 5
$ ll
合計 12
drwxrwxr-x 2 mahny mahny 4096 6月 5 09:45 ./
drwxrwxrwt 10 root root 4096 6月 5 09:45 ../
-rw-rw-r-- 1 mahny mahny 121 6月 5 09:45 dump.csv
$ cat dump.csv
"1","mahny","2017-06-04"
"2","cia","2017-06-04"
"3","mary","2017-06-04"
"4","kite","2017-06-04"
"5","lucia","2017-06-04"
コメントにあるクライアント動作の話もドキュメントにちゃんと書いてありました。（ちゃんと読めや自分…）
ドヤ顔で書いたまとめは「\copy」だけで実現可能。違いはクライアント側処理かDBサーバ処理かの違いという事で権限的にもとてもしっくり来た。
COPYをpsqlの\copyと混同しないでください。 \copyはCOPY FROM STDINやCOPY TO STDOUTを呼び出し、psqlクライアントからアクセスできるファイルにデータの書き込み/読み込みを行います。 したがって、\copyコマンドでは、ファイルへのアクセスが可能かどうかと、ファイルに対するアクセス権限の有無は、サーバではなくクライアント側に依存します。
以上。