Sql error 58p01 error could not open file

I’m trying to import a CSV file into postgresql database.
First of all I tried following query:

copy temporaryData
from '/home/milad/Desktop/myFolder/csvFiles/test.csv'
delimiter ',' csv;

But it gave me below Error:

SQL Error [42501]: ERROR: could not open file "/home/milad/Desktop/myFolder/test.csv" for reading: Permission denied

This error is reasonable as suggested by this thread and also by postgresql tutorial:

Notice that the file must be read directly by the PostgreSQL server, not by the client application. Therefore, it must be accessible by the PostgreSQL server machine. Also, you need to have superuser access in order to execute the COPY statement successfully.

Since I don’t like to change my system permissions just to import a file, To solve this issue I decided to move my CSV file to /tmp/ directory which is suggested by this answer.

Then I tried to run below script:

create or replace procedure loadInstructorOfCourses()
language plpgsql
as $$
begin 
    create table temporaryData(
        courseSectionID varchar(10),
        instructorID    varchar(25),
        courseTitle     varchar(50)
    );

    copy temporaryData
    from '/tmp/test.csv'
    delimiter ',' csv;

    drop table temporaryData;
end;$$;

call loadInstructorOfCourses();

And now it gives me below error:

SQL Error [58P01]: ERROR: could not open file "/tmp/test.csv" for reading: No such file or directory

But I’m sure the file has read permission for others and also it’s available:

> ls -l -d /tmp
drwxrwxrwt 25 root root 700 Oct 20 09:53 /tmp
> ls -l /tmp/test.csv
-rw-r--r-- 1 milad milad 6700 Oct 20 09:53 /tmp/test.csv

Why does this problem happen and how to resolve it?

INFO:
My OS is Manjaro KDE:

> uname -r
5.4.150-1-MANJARO

And also I use DBeaver to connect to postgresql server and run my scripts.

UPDATES: Using psql I have the same issue too. When I connect to db by psql:

psql -U postgres -d IUTPlanning

and run my procedure:

call loadInstructorOfCourses();

I’ll get the same error:

ERROR:  could not open file "/tmp/test.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.
CONTEXT:  SQL statement "copy temporaryData
        from '/tmp/test.csv'
        delimiter ',' csv"
PL/pgSQL function loadinstructorofcourses() line 9 at SQL statement

[{«Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Product»:{«code»:»SSTRGZ»,»label»:»InfoSphere Data Replication»},»Component»:»»,»Platform»:[{«code»:»PF016″,»label»:»Linux»}],»Version»:»11.4″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

Перейти к содержимому

8

ответов

vCenter crashed and down with errors like the following. At this time, no one has deleted anything manually from the base directory. It just happened out of the blue. This is an incident happened of vCenter Appliance with Postgresql and not uncommon. If indexes were the only corruption, they can be identified and recreated and perhaps reindex will reconstruct the corrupted indexes (I have not personally tried this, just a logical thinking). My guess is, this works similar if index pg_toast missing. If tables involved in the corruptions and disappeared, it is not worth the time and would likely have data integrity issues. Work on a plan to restore up to the point where corruptions no longer showing. There are chances that the backup may inherit the same corruption. This corruption might be detectable with vacuum full.

2015-05-14T10:40:00.011-04:00 warning vpxd[08940] [Originator@6876 sub=Default] [VdbStatement] Execution elapsed time: 4 ms

2015-05-14T10:40:00.011-04:00 warning vpxd[08940] [Originator@6876 sub=Default] [VdbStatement] Statement diagnostic data from driver is 58P01:0:7:ERROR: could not open file «base/16384/26465»: No such file or directory;

—> Error while executing the query

2015-05-14T10:40:00.011-04:00 error vpxd[08940] [Originator@6876 sub=Default] [Vdb::IsRecoverableErrorCode] Unable to recover from 58P01:7

2015-05-14T10:40:00.011-04:00 error vpxd[08940] [Originator@6876 sub=Default] [VdbStatement] SQLError was thrown: «ODBC error: (58P01) — ERROR: could not open file «base/16384/26465»: No such file or directory;

—> Error while executing the query» is returned when executing SQL statement «select rule_topn1_proc()»

To confirm with missing objects, use can locate it with ‘ls’ command or the following query.