0

This may be a futile excersise, but one of my client totally insisted that he needs whole database dump to perform analytics in Excel.

There are many answers to how to dump single table to csv (like this: Export to CSV and Compress with GZIP in postgres, Save PL/pgSQL output from PostgreSQL to a CSV file, Export Postgres table to CSV file with headings). There is even a closed question on this subject: https://stackoverflow.com/questions/9226229/how-to-take-whole-database-dump-in-csv-format-for-postgres. But there are no answers on how to dump whole database in single command.

Community
  • 1
  • 1
jb.
  • 23,300
  • 18
  • 98
  • 136

1 Answers1

1

Anyways, here is my script: 

DO $DO$
DECLARE 
r record;
BEGIN
   FOR r IN select tablename from pg_tables where NOT (tablename LIKE 'pg%' OR tablename LIKE 'sql%') LOOP
       EXECUTE 'copy (select * from "'||  r.tablename || '" ) to ''/tmp/dump/' || r.tablename || '.csv'' with csv header';
   END LOOP; 
END;

$DO$;

Some fine points:

  1. It can be pasted into psql command and it will dump all tables in current schema to /tmp/dump directory (please create this directory first)
  2. Query in the for loop (that is: select tablename from pg_tables where NOT (tablename LIKE 'pg%' OR tablename LIKE 'sql%') select all table names in current schema except for ones starting with pg and sql that will most likely be reserved names for postgres and SQL stuff. There most probably is a better way, but hell, who cares?
jb.
  • 23,300
  • 18
  • 98
  • 136