在看 用 PostgreSQL 的 COPY 導(dǎo)入導(dǎo)出 CSV的時(shí)候試了下用COPY導(dǎo)出(導(dǎo)入覺(jué)得用COPY不太方便。。)，但是在dbeaver里寫(xiě)sql的時(shí)候發(fā)現(xiàn)連遠(yuǎn)程數(shù)據(jù)庫(kù)執(zhí)行COPY命令會(huì)報(bào)沒(méi)有權(quán)限的錯(cuò)誤，如圖：
我直接試了試在本地?cái)?shù)據(jù)庫(kù)上做這個(gè)操作后發(fā)現(xiàn)是沒(méi)有問(wèn)題，后來(lái)網(wǎng)上查了查并結(jié)合pg的文檔才知道COPY還有個(gè)對(duì)應(yīng)的命令是\COPY，因?yàn)楣倬W(wǎng)關(guān)于COPY的命令的介紹實(shí)在太長(zhǎng)，下面就簡(jiǎn)單的把兩個(gè)命令的不同之處介紹下。
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ù)庫(kù)在同一個(gè)主機(jī)上
因此我在dbeaver上對(duì)遠(yuǎn)程數(shù)據(jù)庫(kù)做copy to操作時(shí)會(huì)被提示需要權(quán)限，關(guān)于上面提到的第一次報(bào)錯(cuò)的問(wèn)題，我理解是我連接數(shù)據(jù)庫(kù)的賬號(hào)對(duì)于server來(lái)說(shuō)并沒(méi)有寫(xiě)入權(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ù)庫(kù)賬號(hào)都可以用，并且可以從遠(yuǎn)端數(shù)據(jù)庫(kù)將數(shù)據(jù)直接導(dǎo)出到本地
根據(jù)database.yml的配置內(nèi)容登陸遠(yuǎn)程服務(wù)器后做copy to操作即可成功導(dǎo)出數(shù)據(jù)，TO后面填的就是本地的地址
database_name=> \copy roles TO '/Users/dodo/documents/test2.csv' WITH csv;
#=> COPY 32
總結(jié)：
一般來(lái)說(shuō)\COPY命令合適公司普通程序員和測(cè)試去快速的導(dǎo)出數(shù)據(jù)，而COPY命令因?yàn)閷?duì)權(quán)限要求較高則適合數(shù)據(jù)庫(kù)管理員做操作。
參考文獻(xiàn)
https://www.postgresql.org/docs/current/static/sql-copy.html
https://ruby-china.org/topics/32293
