由于linux系统的文件访问权限控制,我们是使用postgres用户进行相关的操作的,因此,执行导出文件之前,先确定导出文件的文件访问权限是postgres,如果不是,那么会报Permission denied
正常执行成功的例子:
postgres=# \copy (select * from pg_settings) to '/var/lib/postgresql/system_pg_settings.csv' with delimiter E',' csv header;
COPY 271
不带表头,后面就不带header字段。header关键字只支持csv格式。
\copy (select * from pg_settings) to '/var/lib/postgresql/system_pg_settings.csv' with delimiter E',' csv;
没有访问权限例子以及解决方法:
postgres-# \copy (select * from pg_settings) to '/mytest/system_pg_settings.csv' with delimiter E',' csv header;
/mytest/system_pg_settings.csv: Permission denied
解决方法如下:
# ls -al
total 8
drwxr-xr-x. 1 root root 53 Oct 7 19:31 .
drwxr-xr-x. 1 root root 53 Oct 7 19:31 ..
drwxr-xr-x. 2 root root 4096 Jul 21 00:00 bin
drwxr-xr-x. 2 root root 6 Jul 10 2020 boot
drwxr-xr-x. 5 root root 340 Oct 7 17:36 dev
drwxr-xr-x. 2 root root 6 Jul 22 14:31 docker-entrypoint-initdb.d
lrwxrwxrwx. 1 root root 34 Jul 22 14:33 docker-entrypoint.sh -> usr/local/bin/docker-entrypoint.sh
-rwxr-xr-x. 1 root root 0 Oct 7 17:36 .dockerenv
drwxr-xr-x. 1 root root 66 Oct 7 17:36 etc
drwxr-xr-x. 2 root root 6 Jul 10 2020 home
drwxr-xr-x. 1 root root 45 Jul 21 00:00 lib
drwxr-xr-x. 2 root root 34 Jul 21 00:00 lib64
drwxr-xr-x. 2 root root 6 Jul 21 00:00 media
drwxr-xr-x. 2 root root 6 Jul 21 00:00 mnt
drwxr-xr-x. 2 root root 6 Oct 7 18:08 mytest
drwxr-xr-x. 2 root root 6 Jul 21 00:00 opt
dr-xr-xr-x. 261 root root 0 Oct 7 17:36 proc
drwx------. 1 root root 24 Jul 22 14:31 root
drwxr-xr-x. 1 root root 24 Jul 22 14:33 run
drwxr-xr-x. 2 root root 4096 Jul 21 00:00 sbin
drwxr-xr-x. 2 root root 6 Jul 21 00:00 srv
dr-xr-xr-x. 13 root root 0 Oct 7 17:26 sys
drwxrwxrwt. 1 root root 6 Oct 7 17:36 tmp
drwxr-xr-x. 1 root root 19 Jul 21 00:00 usr
drwxr-xr-x. 1 root root 17 Jul 21 00:00 var
# chown -R postgres:postgres /mytest
# ls -al
total 8
drwxr-xr-x. 1 root root 53 Oct 7 19:31 .
drwxr-xr-x. 1 root root 53 Oct 7 19:31 ..
drwxr-xr-x. 2 root root 4096 Jul 21 00:00 bin
drwxr-xr-x. 2 root root 6 Jul 10 2020 boot
drwxr-xr-x. 5 root root 340 Oct 7 17:36 dev
drwxr-xr-x. 2 root root 6 Jul 22 14:31 docker-entrypoint-initdb.d
lrwxrwxrwx. 1 root root 34 Jul 22 14:33 docker-entrypoint.sh -> usr/local/bin/docker-entrypoint.sh
-rwxr-xr-x. 1 root root 0 Oct 7 17:36 .dockerenv
drwxr-xr-x. 1 root root 66 Oct 7 17:36 etc
drwxr-xr-x. 2 root root 6 Jul 10 2020 home
drwxr-xr-x. 1 root root 45 Jul 21 00:00 lib
drwxr-xr-x. 2 root root 34 Jul 21 00:00 lib64
drwxr-xr-x. 2 root root 6 Jul 21 00:00 media
drwxr-xr-x. 2 root root 6 Jul 21 00:00 mnt
drwxr-xr-x. 2 postgres postgres 6 Oct 7 18:08 mytest
drwxr-xr-x. 2 root root 6 Jul 21 00:00 opt
dr-xr-xr-x. 262 root root 0 Oct 7 17:36 proc
drwx------. 1 root root 24 Jul 22 14:31 root
drwxr-xr-x. 1 root root 24 Jul 22 14:33 run
drwxr-xr-x. 2 root root 4096 Jul 21 00:00 sbin
drwxr-xr-x. 2 root root 6 Jul 21 00:00 srv
dr-xr-xr-x. 13 root root 0 Oct 7 17:26 sys
drwxrwxrwt. 1 root root 6 Oct 7 17:36 tmp
drwxr-xr-x. 1 root root 19 Jul 21 00:00 usr
drwxr-xr-x. 1 root root 17 Jul 21 00:00 var
#
重新执行之前的导出文件命令,此时能够成功。