1

Trying to import a csv file as follows:

psql -h 127.0.0.1 -d jira_statistics -U admin -p 5432 -c "COPY bugs FROM '/home/centos/bugs.csv' delimiter '|' csv;"
Password for user admin: 
ERROR:  could not open file "/home/centos/bugs.csv" for reading: No such file or directory
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.

However both the file and the table exist in jira_statistics db

[root@jira-statistics ~]# ls /home/centos/bugs.csv
/home/centos/bugs.csv


[root@jira-statistics ~]# psql -h 127.0.0.1 -p 5432 -U admin jira_statistics
Password for user admin: 
psql (9.2.24, server 11.1 (Debian 11.1-3.pgdg90+1))
WARNING: psql version 9.2, server version 11.0.
         Some psql features might not work.
Type "help" for help.

jira_statistics=# \dt
         List of relations
 Schema |   Name   | Type  | Owner 
--------+----------+-------+-------
 public | bugs     | table | admin
 public | devtasks | table | admin
 public | releases | table | admin
 public | stories  | table | admin
1

The most common reason for COPY failing is what is suggested in the hint, but then the error is a "Permission denied".

Since you get "No such file or directory", and a simple ls shows that the file exists, there are first a few plausible explanations that you want to check:

  1. /home/centos/bugs.csv is a soft link pointing to an non-existing file. Use ls -l to be sure an check the destination if it's a soft link.

  2. 127.0.0.1:5432 is redirected at the network level to a different machine. This technique is widely used to connect to remote services through SSH tunnels.

  3. Your OS uses fancy security/isolation features that forbids postgres to read /home/centos/bugs.csv without issuing a Permission denied error. Try ls -l /home/centos/bugs.csv under the postgres OS account.

  4. Your server and client don't run under the same MOUNT namespace, due to containerization.

In cases #2 #3 or #4, just use \copy as suggested in the HINT part of the error message.

  • I am on Centos7, i disabled SELINUX and changed the file to 777 but still the same error. – pkaramol Feb 14 at 18:11
  • I also moved the file to /tmp/bugs.csv so that the entire path is 777 – pkaramol Feb 14 at 18:14
  • About being on CentOS: the signature of your server is server 11.1 (Debian 11.1-3.pgdg90+1) which tends to show that the postgres service is on a different machine running Debian. See my point #2 – Daniel Vérité Feb 14 at 18:18
  • Yes postgres is containerized. However I am running the psql command from host, that's why I tried disabling selinux and chmodded to 777 – pkaramol Feb 15 at 6:45
  • @pkaramol: containerization explains it. The server and client filesystems are insulated from each other with namespaces. – Daniel Vérité Feb 15 at 11:33
0

This could be either a database permissions issue, a filesystem permissions issue, or perhaps an issue with the file's encoding type. (It could also be none of the above). Here are my suggestions:

For database permissions: only a superuser or a specifically-roled user can use COPY. See the postgresql documentation on the COPY command:

COPY naming a file or command is only allowed to database superusers or users who are granted one of the default roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program, since it allows reading or writing any file or running a program that the server has privileges to access.

For filesystem permissions: check that the service has read permission on the file.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.