CODEWITHSUNDEEP
databasepostgresql
dazz
dazz

Reputation: 8732

Import SQL dump into PostgreSQL database

We are switching hosts and the old one provided a SQL dump of the PostgreSQL database of our site.

Now, I'm trying to set this up on a local WAMP server to test this.

The only problem is that I don't have an idea how to import this database in the PostgreSQL 9 that I have set up.

I tried pgAdmin III but I can't seem to find an 'import' function. So I just opened the SQL editor and pasted the contents of the dump there and executed it, it creates the tables but it keeps giving me errors when it tries to put the data in it.

ERROR:  syntax error at or near "t"
LINE 474: t 2011-05-24 16:45:01.768633 2011-05-24 16:45:01.768633 view...

The lines:
COPY tb_abilities (active, creation, modtime, id, lang, title, description) FROM stdin;
t   2011-05-24 16:45:01.768633  2011-05-24 16:45:01.768633  view    nl ...

I've also tried to do this with the command prompt but I can't find the command that I need.

If I do

psql mydatabase < C:/database/db-backup.sql;

I get the error

ERROR:  syntax error at or near "psql"
LINE 1: psql mydatabase < C:/database/db-backu...
        ^

What's the best way to import the database?

Upvotes: 726

Views: 1275325

Answers (22)

Jacob
Jacob

Reputation: 43209

psql --username=<db_user_name> databasename < data_base_dump

That's the command you are looking for.

Beware: databasename must be created before importing. Have a look at the PostgreSQL Docs Chapter 23. Backup and Restore.

Upvotes: 1075

Endogen
Endogen

Reputation: 651

If you have a binary dump in a file then you can restore it with:

pg_restore -f <filename>

Upvotes: 0

Parth
Parth

Reputation: 121

This worked for me

sudo -u postgres psql salesdb < "db (1).sql" -W

  • salesdb -> database name
  • db (1).sql -> SQL file path

Upvotes: 0

mad_lad
mad_lad

Reputation: 702

This worked for me:

psql -U <username> -d <database_name> < dump_file.sql

  • <username>: Replace this with the username of the PostgreSQL user you want to connect as.

  • <database_name>: Replace this with the name of the target database where you want to restore the SQL dump.

  • <dump_file.sql>: Replace this with the path to the SQL dump file that you want to restore into the specified database. If the file is in the current directory, you can directly provide the filename (e.g., my_dump.sql). If it's in another directory, provide the full path (e.g., /path/to/dump_name.sql).

This command can be used either on the host machine (when PostgreSQL is installed locally) or inside a Docker container (using docker exec to run the psql command inside the container).

Upvotes: 3

Usama Munir
Usama Munir

Reputation: 635

If you're on mac, quick one would be:

sudo -u username psql database < /link/to/backup.sql

Upvotes: 1

Taimoor Changaiz
Taimoor Changaiz

Reputation: 10684

Either do this way

pg_restore --dbname=DB_NAME --verbose 2023-05-09T221119Z_pgdump

OR

psql fawkes_development < 2023-05-09T221119Z_pgdump

Upvotes: 0

Yusuf Ganiyu
Yusuf Ganiyu

Reputation: 1087

I used this

psql -d dbName -U username -f /home/sample.sql

Upvotes: 9

alan
alan

Reputation: 3534

Just for funsies, if your dump is compressed you can do something like

gunzip -c filename.gz | psql dbname

As Jacob mentioned, the PostgreSQL docs describe all this quite well.

Upvotes: 30

AConsumer
AConsumer

Reputation: 2771

Follow the steps:

  1. Go to the psql shell
  2. \c db_name
  3. \i path_of_dump [eg:-C:/db_name.pgsql]

Upvotes: 19

davidauza.engineer
davidauza.engineer

Reputation: 299

If you are using a file with .dump extension use:

pg_restore -h hostname -d dbname -U username filename.dump

Upvotes: 12

Mohd Tauovir Khan
Mohd Tauovir Khan

Reputation: 351

I had more than 100MB data, therefore I could not restore database using Pgadmin4.

I used simply postgres client, and write below command.

postgres@khan:/$ pg_restore -d database_name /home/khan/Downloads/dump.sql

It worked fine and took few seconds.You can see below link for more information. https://www.postgresql.org/docs/8.1/app-pgrestore.html

Upvotes: 0

apet
apet

Reputation: 1098

Postgresql12

from sql file: pg_restore -d database < file.sql

from custom format file: pg_restore -Fc database < file.dump

Upvotes: 3

rubo77
rubo77

Reputation: 20817

make sure the database you want to import to is created, then you can import the dump with 

sudo -u postgres -i psql testdatabase < db-structure.sql

If you want to overwrite the whole database, first drop the database

# be sure you drop the right database !!!
#sudo -u postgres -i psql -c "drop database testdatabase;"

and then recreate it with

sudo -u postgres -i psql -c "create database testdatabase;"

Upvotes: 20

Vincent Tang
Vincent Tang

Reputation: 4160

I tried many different solutions for restoring my postgres backup. I ran into permission denied problems on MacOS, no solutions seemed to work.

Here's how I got it to work:

Postgres comes with Pgadmin4. If you use macOS you can press CMD+SPACE and type pgadmin4 to run it. This will open up a browser tab in chrome.

If you run into errors getting pgadmin4 to work, try killall pgAdmin4 in your terminal, then try again.

Steps to getting pgadmin4 + backup/restore

1. Create the backup

Do this by rightclicking the database -> "backup"

enter image description here

2. Give the file a name.

Like test12345. Click backup. This creates a binary file dump, it's not in a .sql format

enter image description here

3. See where it downloaded

There should be a popup at the bottomright of your screen. Click the "more details" page to see where your backup downloaded to

enter image description here

4. Find the location of downloaded file

In this case, it's /users/vincenttang

enter image description here

5. Restore the backup from pgadmin

Assuming you did steps 1 to 4 correctly, you'll have a restore binary file. There might come a time your coworker wants to use your restore file on their local machine. Have said person go to pgadmin and restore

Do this by rightclicking the database -> "restore"

enter image description here

6. Select file finder

Make sure to select the file location manually, DO NOT drag and drop a file onto the uploader fields in pgadmin. Because you will run into error permissions. Instead, find the file you just created:

enter image description here

7. Find said file

You might have to change the filter at bottomright to "All files". Find the file thereafter, from step 4. Now hit the bottomright "Select" button to confirm

enter image description here

8. Restore said file

You'll see this page again, with the location of the file selected. Go ahead and restore it

enter image description here

9. Success

If all is good, the bottom right should popup an indicator showing a successful restore. You can navigate over to your tables to see if the data has been restored propery on each table.

10. If it wasn't successful:

Should step 9 fail, try deleting your old public schema on your database. Go to "Query Tool"

enter image description here

Execute this code block:

DROP SCHEMA public CASCADE; CREATE SCHEMA public;

enter image description here

Now try steps 5 to 9 again, it should work out

Summary

This is how I had to backup/restore my backup on Postgres, when I had error permission issues and could not log in as a superuser. Or set credentials for read/write using chmod for folders. This workflow works for a binary file dump default of "Custom" from pgadmin. I assume .sql is the same way, but I have not yet tested that

Upvotes: 25

Kiryl Plyashkevich
Kiryl Plyashkevich

Reputation: 2267

I noticed that many examples are overcomplicated for localhost where just postgres user without password exist in many cases:

psql -d db_name -f dump.sql

Upvotes: 7

Feuda
Feuda

Reputation: 2365

Works pretty well, in command line, all arguments are required, -W is for password

psql -h localhost -U user -W -d database_name -f path/to/file.sql

Upvotes: 58

Vajira Lasantha
Vajira Lasantha

Reputation: 2513

I use:

cat /home/path/to/dump/file | psql -h localhost -U <user_name> -d <db_name>

Hope this will help someone.

Upvotes: 10

rockusbacchus
rockusbacchus

Reputation: 1337

I'm not sure if this works for the OP's situation, but I found that running the following command in the interactive console was the most flexible solution for me:

\i 'path/to/file.sql'

Just make sure you're already connected to the correct database. This command executes all of the SQL commands in the specified file.

Upvotes: 72

ivanacorovic
ivanacorovic

Reputation: 2867

That worked for me:

sudo -u postgres psql db_name < 'file_path'

Upvotes: 81

Vanessa MacDougal
Vanessa MacDougal

Reputation: 992

You can do it in pgadmin3. Drop the schema(s) that your dump contains. Then right-click on the database and choose Restore. Then you can browse for the dump file.

Upvotes: 5

Gabriel Ramirez
Gabriel Ramirez

Reputation: 5840

Here is the command you are looking for.

psql -h hostname -d databasename -U username -f file.sql

Upvotes: 564

Arran Ubels
Arran Ubels

Reputation: 1834

I believe that you want to run in psql:

\i C:/database/db-backup.sql

Upvotes: 169

Related Questions