131

I have pgAdmin version 1.16.1 installed on my machine.

For exporting a table dump, I do:

Right click on the table => Choose backup => Set Format to Plain => Save the file as some_name.sql

Then I remove the table.

Ok, now I need to import the backup I just created from some_name.sql into the database.

How am I supposed to do this? I can't find any clear instructions on how to import table's .sql dump into database using pgAdmin.

I'd appreciate some guidance.

Arad Alvand
  • 8,607
  • 10
  • 51
  • 71
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
  • 2
    If anyone want for whole DB then look [Export and Import of PostgresSQL with pgAdmin III](http://stackoverflow.com/a/39167526/1045444) – Somnath Muluk Aug 26 '16 at 13:31

6 Answers6

185
  1. In pgAdmin, select the required target schema in object tree (databases ->your_db_name -> schemas -> your_target_schema)
  2. Click on Plugins/PSQL Console (in top-bar)
  3. Write \i /path/to/yourfile.sql
  4. Press enter
Saeed
  • 3,294
  • 5
  • 35
  • 52
Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
  • 9
    Thanks but... `Alternatively, you can open SQL file in query window and simply run it ` When I am trying this, I got error that there is syntax error at line where is writed: `COPY my_table (id, name) FROM stdin; ..... ` – Oto Shavadze Sep 11 '13 at 14:32
  • OH, sorry for that, I did not realize that dump will use COPY and not inserts. The first option should however work. – Tomas Greif Sep 11 '13 at 14:41
  • I tried your answer, but it didn't works for me. Then I go with @OTARIKI comment & it works. Excellent OTARIKI. I'll upvote to your comment. – OO7 Dec 11 '14 at 10:51
  • @OO7, Thank you, but however, my comment about that "something not works". Really, @Tomas's answer is `excellent` and it works for me :) – Oto Shavadze Dec 11 '14 at 11:04
  • 16
    Windows example: If you have a PostgreSQL backup file at `C:\database.backup`, then you can start the import with `\i /database.backup` . – Abdull Dec 23 '15 at 16:39
  • 2
    this doesn't really answer the question. What is a target scheme? Where is the object tree? What if you don't have those already? – szeitlin Mar 09 '16 at 21:48
  • 3
    @TomasGreif All questions about "I can't find how to import table's .sql dump into database using pgAdmin" redirect here, but this doesn't really answer that question. – szeitlin Mar 11 '16 at 23:00
  • @Abdull only if postgresql is also installed on C I believe. – Franck Dernoncourt Mar 18 '16 at 19:19
  • Thank you! I was able to import my sql dump. Just a side node: at least for pgAdmin 1.16.1 on the mac its `pgAdmin3/PSQL Console`. However it seems like the direct terminal command is something like `'/Library/PostgreSQL/9.2/bin/psql' --host 'localhost' --port 5432 --username 'postgres' 'correcthorsebatterystaple' && exit || sleep 5 && exit` – lupz Aug 24 '16 at 09:34
  • if you are facing the problem with copy statements, you can change it to insert statements in dump options window @Oto Shavadze. – Manikanta B Apr 15 '20 at 08:19
  • When trying pgAdmin responses: permission denied for C. \i C:\Program Files\PostgreSQL\14\bin\osakeekstra_2022_16_05.sql C:: Permission denied – Jaana May 19 '22 at 08:46
  • @ManikantaB, how to replace copy with insert statements? – quielfala Aug 30 '22 at 13:01
29

An another way, you can do it easily with CMD on Windows

Put your installed version (mine is 11).

cd C:\Program Files\PostgreSQL\11\bin\

and run simple query

psql -U <postgre_username> -d <db_name> < <C:\path\data_dump.sql>

enter password then wait the final console message.

Note: Make sure to remove <> from the above query except for the < between db_name & file path.

Example: psql -U postgres -d dumb_db < D:\db_dump.sql

Lenzman
  • 1,177
  • 18
  • 30
EgoistDeveloper
  • 775
  • 2
  • 13
  • 37
26

Using PgAdmin step 1: select schema and right click and go to Backup..enter image description here

step 2: Give the file name and click the backup button.

enter image description here

step 3: In detail message copy the backup file path.

enter image description here

step 4:

Go to other schema and right click and go to Restore. (see step 1)

step 5:

In popup menu paste aboved file path to filename category and click Restore button.

enter image description here

Kavindu Gayan
  • 354
  • 6
  • 15
  • This worked for me. I however selected the file from the file picker after clicking on "..." and changing the format to "sql". But it works. Easiest solution in my opinion. – Marcell Apr 06 '21 at 14:15
9

Follow the steps in pgadmin

host-DataBase-Schemas- public (click right) CREATE script- open file -(choose xxx.sql) , then click on the option execute query write result to file -export data file ok- then click in save.its all. it work to me.

note: error in version command script enter image description herede sql over pgadmin can be search, example: http://www.forosdelweb.com/f21/campo-tipo-datetime-postgresql-245389/

enter image description here

Doctor
  • 7,115
  • 4
  • 37
  • 55
alexx ramzzx
  • 101
  • 1
  • 1
9

Click "query tool" button in the list of "tool".

image

And then click the "open file" image button in the tool bar.

image

milaoshutapintou
  • 160
  • 1
  • 5
2

If you have Git bash installed, you can do something like this:

/c/Program\ Files\ \(x86\)/PostgreSQL/9.3/bin/psql -U <pg_role_name> -d <pg_database_name> < <path_to_your>.sql
Humming
  • 453
  • 4
  • 15