Export Postgres data without superuser privileges

Question

I'm using Postgres 14 on CentOS 7. I would like to export a single table's worth of data but I don't have superuser privileges. Unsurprisingly, I get this error

myapp=> COPY myapp_currencyprice to '/tmp/prices.csv' DELIMITER ',' CSV HEADER;
ERROR: must be superuser or a member of the pg_write_server_files role to COPY to a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

What's the proper way to export table data (for use with importing later) for a user who doesn't have superuser privs?

• mustaccio: I think the HINT at the end of the error message provides options; have you tried them?
• Dave: Not sure what that hint means. Would it kill them to just list an example that someone could copy and paste? I tried removing " to '/tmp/prices.csv'" but that doesn't work.
• mustaccio: It probably wouldn't kill "them", but equally it wouldn't kill you to look up the suggested commands in the documentation and copy-paste examples from there (where they properly belong).
• Dave: Well, thanks for providing the answer, my griping notwithstanding

Answer 1

COPY ... TO <file> creates the output file on the server, which, in addition to requiring elevated privileges, in many cases is not useful since not everyone has access to the server file system.

The hint shown after the error message suggests two alternatives that create the output file on the client. These alternatives are in fact one, just wrapped differently.

One is to COPY ... TO STDOUT; this will send the query output to the client terminal and, in combination with the \g <file> psql meta-command, redirect it to the specified file. For example:

myapp=> COPY myapp_currencyprice to STDOUT WITH (DELIMITER ',', FORMAT CSV, HEADER) \g /tmp/prices.csv

Another alternative is to use the \copy meta-command, which does the same thing, though options should be specified using the \pset syntax:

\copy myapp_currencyprice to '/tmp/prices.csv' with (DELIMITER ',', FORMAT CSV, HEADER)