psql -d appdb -c "\copy users TO '/tmp/users.csv' DELIMITER ',' CSV HEADER"

psql's \copy runs on the client side and writes the CSV to the local filesystem, unlike COPY, which writes on the server. CSV mode handles quoting and HEADER emits a header row. A quick way to hand data to a spreadsheet.