Through the psql client, you can use the following syntax to export to a CSV formatted file on the local filesystem:
\copy (SELECT * FROM table WHERE 1) TO '/tmp/foo.csv' With CSV HEADER
This results in a CSV being written to
/tmp/foo.csv and uses the table field names as headers. If you don’t want headers, omit the
HEADER part of the above query.
Documentation on Postgres COPY command: