在看 用 PostgreSQL 的 COPY 導(dǎo)入導(dǎo)出 CSV的時候試了下用COPY導(dǎo)出(導(dǎo)入覺得用COPY不太方便。。)疟游，但是在dbeaver里寫sql的時候發(fā)現(xiàn)連遠程數(shù)據(jù)庫執(zhí)行COPY命令會報沒有權(quán)限的錯誤呼畸，如圖：
我直接試了試在本地數(shù)據(jù)庫上做這個操作后發(fā)現(xiàn)是沒有問題，后來網(wǎng)上查了查并結(jié)合pg的文檔才知道COPY還有個對應(yīng)的命令是\COPY颁虐，因為官網(wǎng)關(guān)于COPY的命令的介紹實在太長蛮原，下面就簡單的把兩個命令的不同之處介紹下。
Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client. Similarly, the command specified with PROGRAM is executed directly by the server, not by the client application, must be executable by the PostgreSQL user. COPY naming a file or command is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.
- COPY：只能管理員用戶使用另绩，并且導(dǎo)出的文件要和數(shù)據(jù)庫在同一個主機上
因此我在dbeaver上對遠程數(shù)據(jù)庫做copy to操作時會被提示需要權(quán)限儒陨，關(guān)于上面提到的第一次報錯的問題，我理解是我連接數(shù)據(jù)庫的賬號對于server來說并沒有寫入權(quán)限(不太確定笋籽。蹦漠。)
Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.
- \COPY：普通數(shù)據(jù)庫賬號都可以用，并且可以從遠端數(shù)據(jù)庫將數(shù)據(jù)直接導(dǎo)出到本地
根據(jù)database.yml的配置內(nèi)容登陸遠程服務(wù)器后做copy to操作即可成功導(dǎo)出數(shù)據(jù)车海，TO后面填的就是本地的地址
database_name=> \copy roles TO '/Users/dodo/documents/test2.csv' WITH csv;
#=> COPY 32
總結(jié)：
一般來說\COPY命令合適公司普通程序員和測試去快速的導(dǎo)出數(shù)據(jù)笛园，而COPY命令因為對權(quán)限要求較高則適合數(shù)據(jù)庫管理員做操作。
參考文獻
https://www.postgresql.org/docs/current/static/sql-copy.html
https://ruby-china.org/topics/32293
