postgres COPY (...) TO PROGRAM on Windows giving error

Question

I am trying to dump PostgreSQL tables using a COPY statement. I am using COPY instead of pg_dump because I need to perform a transformation on one of the columns as it comes out.

The statement I would like to execute is essentially this

COPY ( SELECT 'punt' ) TO PROGRAM '7z a -si C:/Users/Public/Documents/punt';

but I am getting the generic error

ERROR: program "7z a -si C:/Users/Public/Documents/punt" failed
DETAIL: child process exited with exit code 1

I am trying to pass the COPY output through 7zip similar to the fourth example in the COPY documentation in order to compress the output as the database is about 130 GB and I'm ideally working with SSDs. The following command works fine:

COPY ( SELECT 'punt' ) TO 'C:/Users/Public/Documents/punt';

with result COPY 1. I have executed the following command on the psql prompt and it also works which makes me fairly confident about syntax and permissions:

\! echo 'punt' | 7z a -si C:/Users/Public/Documents/punt

Am I missing something about the cmd pipeline or the 7zip executable permissions?

EDIT: Postgres version is 9.3.3

I'm still getting the same error after changing the dir permissions to NETWORK SERVICE based on Craig Ringer's answer. This, however, did not fix the problem and the error is identical. Even after changing the target directory's permissions giving full control to Everyone, I still got the same error. I also tried changing the permission on the 7zip executable and the 7zip Program Files Directory to no avail.

In experimentation, I ran the following, taking the input from copy out of the equation:

COPY ( select '1' ) TO PROGRAM 'echo punt | 7z a -si C:/Users/Public/Documents/punt';

and curiously got the failure message, but instead with exit code 255, which for 7zip has the meaning "User stopped the process". Can't seem to find a reason for this myself.

\! does not report or care about the exit status of the command, while COPY...PROGRAM does. Are you sure it actually works in your last example? — jjanes
– jjanes, Commented Mar 26, 2014 at 21:30
@jjanes It definitely does. Before the command, punt.7z does not exist and after the command, it does. In fact, I do see the output when running via \! identical to the output when running straight from the command line. — FLGMwt
– FLGMwt, Commented Mar 27, 2014 at 19:35

Craig Ringer · Accepted Answer · 2014-03-27 02:05:02Z

4

It'll be a permissions issue.

The PostgreSQL server runs as NETWORKSERVICE for 9.2 and above, or user postgres for 9.1 and below, by default. COPY runs server-side as the user PostgreSQL its self runs as.

Neither of these are likely to have the right to write to C:\Users\Public\Documents.

You'll need to grant the PostgreSQL service user the right to write there using the Windows security properties dialog, or choose another location it can write to.

It works with \! because psql is running as your user, which will have permissions to write there, and \! runs the command client-side as the user running psql.

answered Mar 27, 2014 at 2:05

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

2 Comments

dschulz Over a year ago

Once again, Craig Ringer to the rescue :-)

FLGMwt Over a year ago

Aha! I was worried about that. Unfortunately, it didn't work still. I'll update my question.

FLGMwt · Accepted Answer · 2014-03-27 20:29:13Z

2

Strikethrough the 7zip bit in my edit, the 255 error code is actually from cmd not finding the 7zip exe. I plopped 7zip.exe on C:/ (couldn't figure out escaping the space in Program Files) and ran

COPY ( SELECT 'punt' ) TO PROGRAM 'C:/7z a -si C:/Users/Public/Documents/punt';

and this worked. I'm guessing the NETWORKSERVICE user doesn't have the path environment variable loaded, but I'm not sure about that.

I set the permissions on C:/Users/Public/Documents back to its default and the COPY ... TO PROGRAM still worked so file permission wasn't an issue in this specific case. I would still look to Craig Ringer's suggestion first if I encountered a problem like this again.

answered Mar 27, 2014 at 20:29

FLGMwt

7265 silver badges18 bronze badges

1 Comment

Craig Ringer Over a year ago

Argh, headdesk. I should've thought of that, but was thrown by the permission denied.

Adrian W · Accepted Answer · 2019-05-07 17:16:57Z

1

CMD doesn't recognize a path with spaces.
COPY FROM PROGRAM doesn't have access to PATH variables.

Just wrap your PROGRAM_NAME like RESOURCE_NAME in double apostrophes and you are done:

COPY ( SELECT 'punt' ) TO PROGRAM '"C:/Program Files/bla/bla/7z.exe" a -si "C:/Users/Public/Documents/punt"';

edited May 7, 2019 at 17:16

Adrian W

5,11411 gold badges42 silver badges54 bronze badges

answered May 7, 2019 at 16:54

Marat Ayaibergenov

112 bronze badges

Collectives™ on Stack Overflow

postgres COPY (...) TO PROGRAM on Windows giving error

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related