The following bug has been logged on the website:
Bug reference: 10054
Logged by: PFudd
Email address: postgresql.org@ch.pkts.ca
PostgreSQL version: 9.2.8
Operating system: CentOS 6.5
Description:
Nope, sorry, I'm completely a doofus. I forgot which
machine was the db server; /tmp/foo.txt is sitting on the
machine named 1.2.3.4.
Thanks for your patience!
> Apparently I'm *partially* a doofus.
>
> The "copy" command does its work on the server, not the
> client. Use "\copy" for the client, without quotes or
> trailing semicolon:
>
> # psql -h 1.2.3.4 -U username -c "\\copy (select * from
> history) to /tmp/foo.txt" dbname
>
> I did check on the server in the /tmp directory, but no file
> was created. This is probably a postgres permissions issue,
> but I can see no way to show or change the relevant
> permission to write to the filesystem; more googling
> required.
>
> I'm only *partially* a doofus because the original command
> should generate an error message if it's not actually
> opening and writing to the specified file on the server.
> Let that be my bug report! :-)
>
> Thanks!
>
> > In psql, this command works:
> > # copy (select * from history) to STDOUT;
> > It prints 957 lines to stdout.
> >
> > However, if I do:
> > # copy (select * from history) to '/tmp/foo.txt';
> > it prints
> > COPY 957
> > but doesn't create the file /tmp/foo.txt.
> >
> > If the filename is changed to 'foo.txt' it says:
> > ERROR: relative path not allowed for COPY to file
> > so we know that it's at least looking at the filename critically.
> >
> > If the filename is changed to /tmp/foo.txt without the single quotes, it
says:
> > ERROR: syntax error at or near "/"
> > as expected.
> >
> > It doesn't matter if I'm doing this as a regular user or as the root
user.
> >
> > Using strace, it can be seen there was no attempt to stat or open that
filename for writing:
> >
> > # env PGPASSWORD=password strace -fo/tmp/strace.log psql -h 1.2.3.4 -U
username -c "copy (select * from history where num=5) to '/tmp/foo.txt'"
analyzer_local
> >
> > # cat /tmp/foo.txt
> > cat: /tmp/foo.txt: No such file or directory
> >
> > # grep -c foo.txt /tmp/strace.log
> > 0
> >
> > This was tried with psql 9.2.8 and psql 9.1.13, connecting to a server
running 9.2.4.
