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.

Answer 1

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.