Export and import table dump (.sql) using pgAdmin

Question

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.

Subadar

If anyone want for whole DB then look Export and Import of PostgresSQL with pgAdmin III – Marmoreal

Answer 1

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

Hallmark

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; ..... – Subadar

OH, sorry for that, I did not realize that dump will use COPY and not inserts. The first option should however work. – Hallmark

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. – Motta

@OO7, Thank you, but however, my comment about that "something not works". Really, @Tomas's answer is excellent and it works for me :) – Subadar

Windows example: If you have a PostgreSQL backup file at C:\database.backup, then you can start the import with \i /database.backup . – Delldella

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? – Maritsa

@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. – Maritsa

@Delldella only if postgresql is also installed on C I believe. – Featherbedding

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 – Manvel

if you are facing the problem with copy statements, you can change it to insert statements in dump options window @Oto Shavadze. – Indrawn

When trying pgAdmin responses: permission denied for C. \i C:\Program Files\PostgreSQL\14\bin\osakeekstra_2022_16_05.sql C:: Permission denied – Marlyn

@ManikantaB, how to replace copy with insert statements? – Gilroy

Answer 2

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

Geyserite

This is import. – Culottes

Just use opposite arrows, ... db_name < ... path import, ... db_name > path... export – Geyserite

Answer 3

Using PgAdmin step 1: select schema and right click and go to Backup..

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

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

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.

Midvictorian

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. – Edition

Answer 4

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/

Bosworth

Not sure why this was down voted. English is not great but it is understandable and works. – Death

Answer 5

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

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

Nysa

You'll also need to hit [Execute] – Schoening

This gives me the error "File type not supported". – Edition

Answer 6

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

Defeasible