3

I have a problem copying CSV files from a network shared drive into postgreSQL, at present the fix is to copy the files to a local drive first, but it's another process in a chain which is becoming too messy.

If I run the following command

  COPY test_schema.test FROM 'X:\_Postgres DBs\Testing\test_file.csv' 
  CSV HEADER;

I get:

  ERROR:  could not open file "X:\_Postgres DBs\Testing\test_file.csv" 
  for reading: No such file or directory

ok, so postgreSQL Server doesn't understand windows mapped drives, fine, but if I run:

COPY test_schema.test FROM '\\ws7.domain.com\share-name\_Postgres
DBs\Testing\test_file2.csv' CSV HEADER;

I get:

  ERROR:  could not open file "\\ws7.domain.com\share-name\_Postgres
  DBs\Testing\test_file2.csv" for reading: Permission denied

the file test_file2.csv can be opened locally on the same server with eg notepad/notepad++/wordpad so it doesn't look like there are any file attributes such as 'file in use'

Anyone have any tips how to use COPY with shared network drives? (Our DB UI is coded in Delphi so I'm looking for ways to do with with postgresql scripts I can call from Delphi otherwise I'll have to write some fiddly Delphi Firedac batchcopy method.)

  • The service that postgres is running as needs permissions on the shares, not your user – Philᵀᴹ Nov 10 '16 at 11:07
  • ah...makes sense, Thanks PhilTM! I'll get onto our it support & see if they can change it on the shares. – Hamish_Fernsby Nov 10 '16 at 12:19
5

COPY runs as the PostgreSQL server, which usually runs as NETWORKSERVICE on Windows. This is a local service account that does not have access to your user account's network login credentials for shared drives.

Use the psql command \copy, which reads the file from the client application, running under your user ID and with access to your shared drives, then streams it over the client/server network connection to the postgres server.

I think PgAdmin-III has a similar CSV import command that uses COPY ... FROM STDIN like psql's \copy does, too.

If you're on a Windows AD Domain and you really need PostgreSQL to have direct access to shares, you can run the PostgreSQL server under a domain account instead. But that's usually unnecessary.

  • Yes I checked in services.msc it was running as a network service. I tried getting it to login with a service account, but when I click ' start' it started and stopped again right away, not sure why? I'll test the psql \copy approach too & see if it works in my configuration. – Hamish_Fernsby Nov 11 '16 at 17:47
  • You can't change the user the service runs as without also changing the ownership and permissions of the data files, otherwise it won't be able to access them. If you try to use a domain account, the domain account must also have login as a service rights. – Craig Ringer Nov 13 '16 at 8:49
  • sorry, delayed response.. I have a service account setup and it has read/write permissions for the relevant drives. I tried changing the default login for the postgresql service to use this service account, but when I stop and start it again I get the following warning message "The postgresql9.3 service started then stopped, some services stop when they are not in use by other programs" I note that postgresql has 'remote procedure call (RPC)' as a dependency, would that also need to be started with the service account as the default login? or woud that create other problems? – Hamish_Fernsby Dec 15 '16 at 21:37

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.