2

I have a file /tmp/foo.csv that looks like this:

1,2
10,20
100,200

I have the same exact file in another directory /mydata/foo.csv. Both directories and files have the same exact permissions, owner, and were created by the same user, yet when I run

COPY foo FROM '/tmp/foo.csv' (FORMAT CSV, DELIMITER ',', HEADER TRUE);

I get this error

ERROR:  could not open file "/tmp/foo.csv" for reading: No such file or directory

When I run

COPY foo FROM '/mydata/foo.csv' (FORMAT CSV, DELIMITER ',', HEADER TRUE);

I see COPY 3.

What am I missing here?

Here's the relevant file and directory information:

╭─foo@bar  / ‹py27› 
╰─$ ll -d /mydata /mydata/foo.csv /tmp /tmp/foo.csv                                                                                                                       1 ↵
drwxrwxrwt  2 root root 4.0K Sep  9 08:26  /mydata
drwxrwxrwt 17 root root  420 Sep  9 08:57  /tmp
-rw-r--r--  1 root root   17 Sep  6 12:03  /mydata/foo.csv
-rw-r--r--  1 root root   17 Sep  9 08:36  /tmp/foo.csv
╭─foo@bar  / ‹py27› 
╰─$ diff /mydata/foo.csv /tmp/foo.csv
╭─foo@bar  / ‹py27› 
╰─$ 

I'm running Arch Linux

NOTE: I've also tried this on another Linux machine running CentOS and I was unable to reproduce the problem.

  • COPY reads from the server where the database you connect to is. Can it be that you have foo.csv on the server only in one location, but you show here the listings of your local machine? – dezso Sep 9 '15 at 13:53
  • I'm running the server on my local machine. – Phillip Cloud Sep 9 '15 at 14:03
  • And you can do a cat from both files as user postgres? – dezso Sep 9 '15 at 14:13
  • Yes, I'm able to do that. – Phillip Cloud Sep 9 '15 at 14:17
  • Do you have SELinux enabled in your Arch install? – Kassandry Sep 13 '15 at 20:07
6

On Arch, the systemd script for PostgreSQL (/usr/lib/systemd/system/postgresql.service) enables private /tmp, which means the PostgreSQL server process can't communicate with the user using /tmp.

Two possible solutions:

  1. Create a new folder somewhere else and give user postgres read privileges on it: # sudo mount -t tmpfs -o users,rw tmpfs /data
  2. (NOT RECOMMENDED) Set PrivateTmp option in postgresql.service to false.

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.