Обсуждение: [GENERAL] PostgreSQL COPY Statement Error On Linux
I am trying to utilize the "COPY" statement below to copy a .CSV file to a table in a PostgreSQL database.:
COPY <schema>.<table_name>(<table_columns>) FROM '\\shared\network\path\to\csv\test.csv' DELIMITER ',' CSV HEADER;
This works with a PostgreSQL database installed in a WINDOWS environment (Windows 7), but fails with the following error with a similar PostgreSQL database in a Linux environment (Centos 7):
org.postgresql.util.PSQLException: ERROR: could not open file "\\shared\network\path\to\csv\test.csv" for reading: No such file or directory
I have granted READ access to EVERYONE on the CSV folder on the network path as depicted in the attached image.
Could someone point me to the reason for the failure in Linux?
Вложения
On Tue, Sep 12, 2017 at 12:30 PM, Osahon Oduware <osahon.gis@gmail.com> wrote: > I am trying to utilize the "COPY" statement below to copy a .CSV file to a > table in a PostgreSQL database.: > COPY <schema>.<table_name>(<table_columns>) FROM > '\\shared\network\path\to\csv\test.csv' DELIMITER ',' CSV HEADER; > > This works with a PostgreSQL database installed in a WINDOWS environment > (Windows 7), but fails with the following error with a similar PostgreSQL > database in a Linux environment (Centos 7): > org.postgresql.util.PSQLException: ERROR: could not open file > "\\shared\network\path\to\csv\test.csv" for reading: No such file or > directory > > I have granted READ access to EVERYONE on the CSV folder on the network path > as depicted in the attached image. > Could someone point me to the reason for the failure in Linux? You are invoking server side copy. This means the SERVER neads to be able to access the file under the name you've given to it. The network path you have given is valid on windows machines ( UNC path? It's been a decade an a half since Iast used windows ), but not on linux. Typically on linux you mount the shared folder /some/where and type the path as /some/where/path/to/csv/test.csv. You may be needing a CLIENT copy. I do not see which client program you are using, it may be some fancy GUI stuff, in which case I cannot help you. If you are using the standard "psql" tool you can just use \copy. As explained in the docs this just does "copy from stdin" ( or to stdout ) on the client side and redirects the file you give in the command line ( or you can issue a [psql ... -c "copy ...from stdin"] in a command line and feed the file via shell redirections, but, IIRC, windows shells are terrible at quoting arguments and redirecting i/o, so it may be better to avoid it). Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Hello
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Osahon Oduware
Sent: Dienstag, 12. September 2017 12:30
To: pgsql-general@postgresql.org
Subject: [GENERAL] PostgreSQL COPY Statement Error On Linux
Hi All,
I am trying to utilize the "COPY" statement below to copy a .CSV file to a table in a PostgreSQL database.:
COPY <schema>.<table_name>(<table_columns>) FROM '\\shared\network\path\to\csv\test.csv' DELIMITER ',' CSV HEADER;
This works with a PostgreSQL database installed in a WINDOWS environment (Windows 7), but fails with the following error with a similar PostgreSQL database in a Linux environment (Centos 7):
org.postgresql.util.PSQLException: ERROR: could not open file "\\shared\network\path\to\csv\test.csv" for reading: No such file or directory
It looks like the share is not visible for the Linux system. You probably need to mount it first using Samba and then access it through the mount point using slashes instead of bakslashes: /
Instructions on how to mount a Windows share in Linux can be found on the internet. Since I am not an expert on this myself, I can’t give you more concrete instructions.
This may help: http://www.serverlab.ca/tutorials/linux/storage-file-systems-linux/mounting-smb-shares-centos-7/
An alternative would be to copy the file to the Linux system using e.g. scp of sftp and the load it locally.
Hope this helps.
Bye
Charles
I have granted READ access to EVERYONE on the CSV folder on the network path as depicted in the attached image.
Could someone point me to the reason for the failure in Linux?
Hello
From: pgsql-general-owner@
postgresql.org [mailto:pgsql-general-owner@ postgresql.org] On Behalf Of Osahon Oduware
Sent: Dienstag, 12. September 2017 12:30
To: pgsql-general@postgresql.org
Subject: [GENERAL] PostgreSQL COPY Statement Error On Linux
Hi All,
I am trying to utilize the "COPY" statement below to copy a .CSV file to a table in a PostgreSQL database.:
COPY <schema>.<table_name>(<table_columns>) FROM '\\shared\network\path\to\csv\ test.csv' DELIMITER ',' CSV HEADER;
This works with a PostgreSQL database installed in a WINDOWS environment (Windows 7), but fails with the following error with a similar PostgreSQL database in a Linux environment (Centos 7):
org.postgresql.util.PSQLException: ERROR: could not open file "\\shared\network\path\to\csv\ test.csv" for reading: No such file or directory It looks like the share is not visible for the Linux system. You probably need to mount it first using Samba and then access it through the mount point using slashes instead of bakslashes: /
Instructions on how to mount a Windows share in Linux can be found on the internet. Since I am not an expert on this myself, I can’t give you more concrete instructions.
This may help: http://www.serverlab.ca/
tutorials/linux/storage-file- systems-linux/mounting-smb- shares-centos-7/
An alternative would be to copy the file to the Linux system using e.g. scp of sftp and the load it locally.
Hope this helps.
Bye
Charles
I have granted READ access to EVERYONE on the CSV folder on the network path as depicted in the attached image.
Could someone point me to the reason for the failure in Linux?
Thanks for your response. I would try this out and give you feedback.
On Tue, Sep 12, 2017 at 12:30 PM, Osahon Oduware <osahon.gis@gmail.com> wrote:
> I am trying to utilize the "COPY" statement below to copy a .CSV file to a
> table in a PostgreSQL database.:
> COPY <schema>.<table_name>(<table_columns>) FROM
> '\\shared\network\path\to\csv\test.csv' DELIMITER ',' CSV HEADER;
>
> This works with a PostgreSQL database installed in a WINDOWS environment
> (Windows 7), but fails with the following error with a similar PostgreSQL
> database in a Linux environment (Centos 7):
> org.postgresql.util.PSQLException: ERROR: could not open file
> "\\shared\network\path\to\csv\test.csv" for reading: No such file or
> directory
>
> I have granted READ access to EVERYONE on the CSV folder on the network path
> as depicted in the attached image.
> Could someone point me to the reason for the failure in Linux?
You are invoking server side copy. This means the SERVER neads to be
able to access the file under the name you've given to it.
The network path you have given is valid on windows machines ( UNC
path? It's been a decade an a half since Iast used windows ), but not
on linux. Typically on linux you mount the shared folder /some/where
and type the path as /some/where/path/to/csv/test.csv.
You may be needing a CLIENT copy. I do not see which client program
you are using, it may be some fancy GUI stuff, in which case I cannot
help you. If you are using the standard "psql" tool you can just use
\copy. As explained in the docs this just does "copy from stdin" ( or
to stdout ) on the client side and redirects the file you give in the
command line ( or you can issue a [psql ... -c "copy ...from stdin"]
in a command line and feed the file via shell redirections, but, IIRC,
windows shells are terrible at quoting arguments and redirecting i/o,
so it may be better to avoid it).
Francisco Olarte.
On Tue, 12 Sep 2017 11:30:02 +0100, Osahon Oduware <osahon.gis@gmail.com> wrote: >I am trying to utilize the "COPY" statement below to copy a .CSV file to a >table in a PostgreSQL database.: > *COPY <schema>.<table_name>(<table_columns>) FROM >'\\shared\network\path\to\csv\test.csv' DELIMITER ',' CSV HEADER;* > >This works with a PostgreSQL database installed in a WINDOWS environment >(Windows 7), but fails with the following error with a similar PostgreSQL >database in a Linux environment (Centos 7): > *org.postgresql.util.PSQLException: ERROR: could not open file >"\\shared\network\path\to\csv\test.csv" for reading: No such file or >directory* > Francisco already pointed out that Linux doesn't understand the backslashes in the file path, however it should be noted that Windows *does* understand forward slashes and that [modulo disk names in Windows] you can use forward slash paths on both systems. George -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
George: On Tue, Sep 12, 2017 at 6:40 PM, George Neuner <gneuner2@comcast.net> wrote: > Francisco already pointed out that Linux doesn't understand the > backslashes in the file path, however it should be noted that Windows > *does* understand forward slashes and that [modulo disk names in > Windows] you can use forward slash paths on both systems. That's not strictly correct. Linus understand backslahes in paths fine, they are just not a directory separator ( there are only two reserved byte values in path names, IIRC, slash for dir sep and nul for string end, C issues ). Windows, OTOH, inherits path separator logic from MSDOS 2.0, and if it hasn't changed in the last fifteen years treats any slash as a separator. But the issue is that windos treats a \\netname\resource prefix as a network request, and transform it internally, while linux does not. In *ix you have to connect to the machine and mount its resources first, similarly to what you do with local disks. Normally //x is treated the same as /x, or as ////x: folarte@n:~$ ls -ldi /tmp //tmp ///tmp 655361 drwxrwxrwt 14 root root 45056 Sep 12 19:17 /tmp 655361 drwxrwxrwt 14 root root 45056 Sep 12 19:17 //tmp 655361 drwxrwxrwt 14 root root 45056 Sep 12 19:17 ///tmp Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general