Import SQL dump into PostgreSQL database

Question

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?

I had a similar error to your first one: `ERROR: syntax error at or near "t"`. It turned out that I had only imported a partial schema and thus a `CREATE TABLE` statement earlier in the script had failed. Look through the full output of the import to find it. — owensmartin, Aug 10 '16 at 23:26

score 914 · Accepted Answer · edited Jan 27 '19 at 10:30

914

psql 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.

edited Jan 27 '19 at 10:30

Matthias Braun

28,341
18
134
157

answered Jul 27 '11 at 09:57

Jacob

39,723
6
76
81

2

I've tried that psql mydatabase < C:\database\db-backup.sql but i get the error Invalid command \database. I also tried with " " around it. – dazz Jul 27 '11 at 10:05
Have you tried cd'ing to C:\database und calling psql mydatabase < db-backup.sql? – Frank Schmitt Jul 27 '11 at 10:45
28

@Dazz You have to do this command from your command prompt (Start -> Run -> cmd) , not from the postgres prompt. – Jacob Jul 27 '11 at 10:48
3

if i run it from cmd i get 'The ' – dazz Jul 27 '11 at 10:54
4

@Dazz:You could use `-f` switch (or `--file`) too – Grzegorz Szpetkowski Jul 27 '11 at 10:56
2

Can't be. The error you are quoting is from PowerShell, not from the Windows Command Prompt. You are typing the command into the wrong shell. – Jacob Jul 27 '11 at 10:56
Yes PowerShell but that works to. I got it working: psql -f sdb-backup.sql mydatabase; – dazz Jul 27 '11 at 11:24
this document is a good reference for messing around with posgres databases http://www.postgresql.org/docs/9.1/static/backup-dump.html "pg_dump dbname > outfile" "gunzip -c filename.gz | psql dbname" – Stephen Nguyen Jun 13 '13 at 18:01
63

psql --username=postgres databasename < data_base_dump.sql – Maxence Jun 18 '13 at 18:27
1

ownerit says peer authentication failed. even if i input correct password :( – user151496 Dec 15 '16 at 10:44
this solution worked for me, but only after i dropped my database (using `rake db:drop`) and created it (using `rake db:create`) first (as @jacob mentions above). ran a migration afterwards as well then good to go. – Kyle Kwon Feb 23 '17 at 17:22
@user151496 You have to change your current user. If you use peer authentication, Postgre will assume that your user is your OS current user. Export a PGUSER env var (set it as your preferred db username) and setup a ~/.pgpass file as well. That should sort your problems. – Milan Velebit Oct 03 '17 at 22:54
for finding current database, use `select current_database();` in psql – Vikas Prasad Mar 06 '18 at 11:22
getting this "The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database." – Wajdan Ali Sep 10 '18 at 13:31
1

If you are importing a huge dump, don't forget to wrap the dump in BEGIN TRANSACTION and COMMIT to keep psql from committing after every row. – tensojka Oct 09 '18 at 21:15
Hi, is possible exclude a specific table in existing 'data_base_dump' file – Alexander Vidaurre Arroyo Apr 28 '21 at 16:03
Shorter form: `psql -U postgres databasename < data_base_dump.sql` – testing_22 Oct 03 '21 at 21:25

score 459 · Answer 2 · answered Oct 03 '13 at 18:55

459

Here is the command you are looking for.

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

answered Oct 03 '13 at 18:55

Gabriel Ramirez

4,790
1
12
5

42

Good, but better append also the "-L logfile.log" param to log the output on file. – zerologiko Feb 12 '14 at 09:29
2

getting this "The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database." – Wajdan Ali Sep 10 '18 at 13:34
10

You can also: `pg_restore -h hostname -d dbname -U username filename.sql` – Fábio Araújo Aug 07 '19 at 18:20
How to deal with `ERROR: duplicate key value violates unique constraint`? I would like `psql` to ignore when there's a duplicate key – gogofan Jul 10 '20 at 14:16

score 140 · Answer 3 · edited Aug 20 '12 at 09:46

140

I believe that you want to run in psql:

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

edited Aug 20 '12 at 09:46

j0k

22,303
28
77
86

answered Aug 20 '12 at 05:27

Arran Ubels

1,534
1
9
8

Had to use this because Emacs' eshell doesn't support input redirection. Thanks. – duma Mar 30 '13 at 03:11
7

Be sure to change to the database first with \c – hkong Aug 18 '16 at 00:54
use \ir if you want to give it a relative path. – Yordan Grigorov Jan 08 '21 at 22:34
I don't know why but I had to add quotes it worked. test_db=# \i 'C:/Users/paude/Downloads/event_logic.dump' – Badri Paudel Mar 01 '22 at 09:29

score 64 · Answer 4 · edited Mar 02 '15 at 13:33

64

That worked for me:

sudo -u postgres psql db_name < 'file_path'

edited Mar 02 '15 at 13:33

kenorb

137,499
74
643
694

answered Oct 28 '14 at 13:56

ivanacorovic

2,415
3
26
41

score 59 · Answer 5 · answered May 17 '15 at 14:09

59

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.

answered May 17 '15 at 14:09

rockusbacchus

1,167
11
10

2

This solution worked for me while the most voted solution yielded "stdin is not a tty" error. – 김민준 Nov 25 '16 at 07:56
Just what I was looking for - a way to execute the script from inside the `psql` command line. Thanks! – vatsug Feb 10 '17 at 09:43
Just wanted to thank you for this one. Should be included in the right answer. – PKHunter Aug 15 '17 at 20:46
this is correct, do not use back-slashes in the path in windows! – Erdinc Ay Apr 18 '18 at 18:54
also for Windows, you need double-forward slashes – CMAS Aug 17 '18 at 13:56
This repeats a three years old answer of @ArranUbels. – questionto42standswithUkraine Jul 24 '21 at 12:30

Feuda · Answer 6 · 2017-01-04T14:16:47.007

49

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

edited Jan 04 '17 at 14:16

answered Dec 16 '16 at 11:05

Feuda

2,085
27
25

1

After several tries this solution worked for me, pointing all the information necessary to execute the job: where to, database name, user, password and of course the file. – eduardosufan May 13 '22 at 17:43

score 26 · Answer 7 · edited Oct 19 '21 at 19:27

26

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.

edited Oct 19 '21 at 19:27

Nemo

2,414
2
28
59

answered May 27 '15 at 18:26

alan

2,950
1
31
34

score 16 · Answer 8 · answered Oct 31 '19 at 03:19

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"

2. Give the file a name.

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

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

4. Find the location of downloaded file

In this case, it's /users/vincenttang

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"

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:

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

8. Restore said file

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

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"

Execute this code block:

DROP SCHEMA public CASCADE; CREATE SCHEMA public;

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

score 13 · Answer 9 · answered Dec 30 '19 at 12:24

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;"

AConsumer · Answer 10 · 2021-08-31T05:24:29.173

10

Follow the steps:

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

edited Aug 31 '21 at 05:24

answered Jun 30 '20 at 04:55

AConsumer

1,921
2
18
30

score 9 · Answer 11 · answered May 25 '15 at 23:18

9

I use:

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

Hope this will help someone.

answered May 25 '15 at 23:18

Vajira Lasantha

2,335
3
21
35

score 8 · Answer 12 · answered Jan 07 '21 at 17:32

8

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

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

answered Jan 07 '21 at 17:32

davidauza.engineer

199
2
5

score 7 · Answer 13 · answered Sep 14 '18 at 10:53

7

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

answered Sep 14 '18 at 10:53

Kiryl Plyashkevich

1,966
17
15

score 5 · Answer 14 · answered Apr 16 '14 at 17:50

5

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.

answered Apr 16 '14 at 17:50

Vanessa MacDougal

974
6
14

2

But the 'restore' button is not click-able even after selecting the .sql file. This software seems to want some other kind of file - one with *.backup format. Clicking 'help' on that import-box refers to pg_restore - "...a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats." The sql file the OP refers to is a plain-text format. – JosephK Mar 05 '17 at 12:28

score 1 · Answer 15 · answered Jul 13 '20 at 09:07

1

Postgresql12

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

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

answered Jul 13 '20 at 09:07

burney

736
11
14

score 0 · Answer 16 · answered Jul 29 '20 at 18:53

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