pgAdmin – Import Database Dump Without PSQL Console Plugin

pgadmin

I am trying to import a database dump (.sql) using pgAdmin. I want to do it using only the GUI, not the PSQL Console (where I can import the sql dump using \i /path/to/yourfile.sql on Unix or \i C:\\yourfile.sql on Windows). Is that possible? If so, how?

The new import tool seems to be able to only import data in a table:

enter image description here

The restore function doesn't seem to accept .sql:

enter image description here

Lastly, I cannot load the .sql I'm trying to import in the query builder, as it is too large (25 GB).

Best Answer

I have used this pl/pgsql function to create one .csv file per table:

CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$
declare
  tables RECORD;
  statement TEXT;
begin
  FOR 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')
    ORDER BY schema_table
  LOOP
    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/
Related Question