1

I have pgAdmin4 and the latest version of PostgreSQL installed on my machine. When I tried to insert large amount of data using INSERT statements contained in a .sql file of size 40 MB, 50MB and above, the app crashed. Hence, I am wondering if dumping heavy data using those sql files via command line is a good idea? If yes, then from where can I install that command line utility in pgAdmin4 ?

  • if You have installed PostgreSQL - You are already have all. psql - then select database and use command - \i file.sql – a_vlad Feb 9 '18 at 11:26
  • @a_vlad Could you tell me from where I could access psql? Either from pgAdmin4 or by going into Program Files/PostgreSQL/10/ folder and going again into one of the folder listed there? There are many like bin,data, doc etc – Tan Feb 9 '18 at 18:46
  • CMD windows, if PostgreSQL in PATH - just type psql -u postgres -p , if not - first cd to Program Files/PostgreSQL/10/bin – a_vlad Feb 10 '18 at 0:20
1

Firstly, I have no idea about pgAdmin4

Secondly, It would take a lot of time when executing a large SQL file. Please consider using pg_dump (custom format) & pg_restore to enhance your performance.

Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.

For example:

-----#### Exporting large table with custom format
pg_dump -h your_host -p 5432 -U user_name  --format custom --no-password   --encoding UTF8  --no-security-labels --no-tablespaces --table=your_schema_name.your_table_name --verbose --file your_file_name.backup --schema your_schema_name your_database_name  

-----#### Importing large table with custom format
pg_restore -h your_host -p 5432 -U user_name --dbname=your_database_name --role=user_name  --format custom --no-tablespaces --schema=your_schema_name --verbose your_file_name.backup
  • Thanks. I believe the above commands needs to be executed in the command line. Could you tell me how can I access the plsql so that I could run the command line to run above commands? Also, I am little bit unclear as to what the pg_dump and pg_restore will do to the 50 MB .sql file. Could you please elaborate? TIA – Tan Feb 9 '18 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.