Exporting data from Postgres as a CSV or tab delimited file can be fairly simple using the COPY command. Examples below.

Export an entire table as CSV:

psql -c "COPY my_table TO STDOUT WITH CSV" > output.csv

Export an entire table as CSV with Header:

psql -c "COPY my_table TO STDOUT WITH CSV HEADER" > output.csv

Export query as CSV:

psql -c "COPY (SELECT first_name, last_name FROM my_table) TO STDOUT WITH CSV" > output.csv

Export query as data tab delimited text file:

psql -c "COPY (SELECT first_name, last_name FROM my_table) TO STDOUT" > output.txt

Export data directly to file (requires superuser permissions):

psql -c "COPY (SELECT first_name, last_name FROM my_table) TO '/home/user/file.txt'"