How to export table data to file
When logged into psql:
COPY tablename TO 'filename';
For more details, see this: http://www.postgresql.org/docs/current/static/sql-copy.html
In psql:
\copy tablename to 'filename' csv;
First, log into the PostgreSQL console via the command line with the psql
command.
To export:
\connect database_name;
\copy my_table TO 'my_table.csv' CSV;
\q
To import:
\connect database_name;
\copy my_table FROM 'my_table.csv' DELIMITER ',' CSV;
\q
Done!
Or, from a shell script!
export PGPASSWORD=dbpass
psql --dbname=mydb --username=dbuser --host=127.0.0.1 -c "COPY (SELECT * FROM widget) TO stdout DELIMITER ',' CSV HEADER" > export.csv
Bonus Advice Use pgcli, it's way better than psql