Export Postgres Database into CSV file

I made this pl/pgsql function to create one .csv file per table (excluding views, thanks to @tarikki):

CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$declare   tables RECORD;   statement TEXT;beginFOR tables IN    SELECT (table_schema || '.' || table_name) AS schema_table   FROM information_schema.tables t INNER JOIN information_schema.schemata s    ON s.schema_name = t.table_schema    WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema')   AND t.table_type NOT IN ('VIEW')   ORDER BY schema_tableLOOP   statement := 'COPY ' || tables.schema_table || ' TO ''' || path || '/' || tables.schema_table || '.csv' ||''' DELIMITER '';'' CSV HEADER';   EXECUTE statement;END LOOP;return;  end;$$ LANGUAGE plpgsql;

And I use it this way:

SELECT db_to_csv('/home/user/dir');-- this will create one csv file per table, in /home/user/dir/

You can use this at psql console:

\copy (SELECT foo,bar FROM whatever) TO '/tmp/file.csv' DELIMITER ',' CSV HEADER

Or it in bash console:

psql -P format=unaligned -P tuples_only -P fieldsep=\, -c "SELECT foo,bar FROM whatever" > output_file

Modified jlldoras brilliant answer by adding one line to prevent the script from trying to copy views:

CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$declare   tables RECORD;   statement TEXT;beginFOR tables IN    SELECT (table_schema || '.' || table_name) AS schema_table   FROM information_schema.tables t INNER JOIN information_schema.schemata s    ON s.schema_name = t.table_schema    WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema', 'configuration')   AND t.table_type NOT IN ('VIEW')   ORDER BY schema_tableLOOP   statement := 'COPY ' || tables.schema_table || ' TO ''' || path || '/' || tables.schema_table || '.csv' ||''' DELIMITER '';'' CSV HEADER';   EXECUTE statement;END LOOP;return;  end;$$ LANGUAGE plpgsql;