postgres - save output to server harddrive

Question

When I execute the following script:

copy (
select agk_p_id Promoter_agk, multiplication_lr_agk_p_k4, agk_lr_rvd, status_agk_p_k4
from patient_agk_p_expr
where  status_agk_p_k4='Preferentially') 
to 'g:\boom.csv' 
With CSV HEADER;

It works just beautifully, and creates the boom.csv file on my g drive. I get:

Query returned successfully: 8486 rows affected, 631 ms execution time.

I should note that my 'g' drive is an external harddrive that is connected to my computer. And my cygwin refers to my g harddrive like this:

blumr04@SRB524YBZ1 /cygdrive/g/
$ pwd
/cygdrive/g

Now, my computer has also access to a server harddrive of my organization. On my windows explorer it refers to as (Z:)

My cygwin refers to the 'Z' drive accordingly (just the same it does to my C: drive):

blumr04@SRB524YBZ1 /cygdrive/z/
$ pwd
/cygdrive/z

But I have troubles when it comes to having postgres recognizing this harddrive - when I attempt to run the following script in order to save my table to the Z harddrive :

copy (
select agk_p_id Promoter_agk, multiplication_lr_agk_p_k4, agk_lr_rvd, status_agk_p_k4
from patient_agk_p_expr
where  status_agk_p_k4='Preferentially') 
to 'z:\boom.csv' 
With CSV HEADER;

I get the following error message:

ERROR:  could not open file "z:\boom.csv" for writing: No such file or directory
********** Error **********

ERROR: could not open file "z:\boom.csv" for writing: No such file or directory
SQL state: 58P01

Does anyone know how can I save (copy to) my files when it comes to a harddrive that is not physically connected to my computer, but rather is a server harddrive? - Is there a command / script in postgres that would be able to show me which harddrives are accessible to postgres? It looks like for some reason the Z harddrive is not accessible for postgres read/write, at least not in the way that I attempt it, while G,J,K, and other harddrive which are external HD - are accessible. I would be glad to know if I could expand postgres accessibility somehow..

Thanks!

@Mike Sherrill 'Cat Recall'

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

My Z drive is referred to in Windows Explorer also as: (\shares.nyumc.org\research)(Z:), therefore I tried also the following:

copy (
select agk_p_id Promoter_agk, multiplication_lr_agk_p_k4, agk_lr_rvd, status_agk_p_k4
from patient_agk_p_expr
where  status_agk_p_k4='Preferentially') 
to '\\shares.nyumc.org\research\boom.csv' 
With CSV HEADER;

This scripts gives me the following error, indeed all about permissions:

ERROR:  could not open file "\\shares.nyumc.org\research\boom.csv" for writing: Permission denied
********** Error **********

ERROR: could not open file "\\shares.nyumc.org\research\boom.csv" for writing: Permission denied
SQL state: 42501

So it looks like the right path is:

\\shares.nyumc.org\research\

And that in this case (Z:) is merely an alias name(?!) as the error message is this time NOT about "No such file or directory", but rather about permissions.

Is there a way I could facilitate the necessary permission to Postgres so it could write to the server drive?

My Postgres is running as a typical Windows machine, and is is installed with all normal default criteria. It works perfectly well with all my external drivers, except Z, which is a server driver. — Roy
– Roy, Commented Jan 16, 2015 at 23:38

Mike Sherrill 'Cat Recall' · Accepted Answer · 2015-01-16 23:40:35Z

1

The most common problem with running COPY tablename to filename is dealing with path and permissions from the point of view of the PostgreSQL server.

Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client. Source

If you try to write to a file that the PostgreSQL server can't "see", you'll get "No such file or directory". If you try to write to a file in a directory for which the PostgreSQL server lacks permissions, you'll get "Permission denied".

So odds are good that the PostgreSQL user (the user ID the server runs as) lacks permissions on "z".

answered Jan 16, 2015 at 23:40

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Roy Over a year ago

Thanks! Is there a way in which I could add the necessary permission for postgres so it'll be able to write to my Z drive?

Mike Sherrill 'Cat Recall' Over a year ago

I'd think the local user "postgres" needs to be known to the server that governs the "z" drive, and needs to be given permissions. How you do that depends on how your network is configured. (When I work on Windows servers, PostgreSQL is running on the Windows server, not on a workstation.) So, yeah, there's a way, but I don't know it off the top of my head.

Roy Over a year ago

If i go to Windows Task Manager -> Services... I see that my postgresql-x64-9.3-PostgreSQL server 9.3 in Log On As 'Network Service'. It would be of a great help if you could refer me to somewhere where I could further investigate how to make my postgres 'talk' to my Z drive. We recently been required to move all our data to this virtual drive and I would be glad if I could read/write from this drive directly rather than keeping copies of all my work on an external drive.

Mike Sherrill 'Cat Recall' Over a year ago

Best I could find. If this doesn't help, post a new question to SuperUser or DBA.

Mike Sherrill 'Cat Recall' Over a year ago

Talk to your network's sysadmin. Sysadmin should know. setspn

|

Collectives™ on Stack Overflow

postgres - save output to server harddrive

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related