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 1005 · Accepted Answer · edited Jan 27 '19 at 10:30

1005

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

32,039
22
142
171

answered Jul 27 '11 at 09:57

Jacob

41,721
6
79
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
32

@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
4

if i run it from cmd i get 'The '<' operator is reserved for future use.' – 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
70

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 529 · Answer 2 · answered Oct 03 '13 at 18:55

529

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

5,490
1
13
5

50

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

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
16

You can also: `pg_restore -h hostname -d dbname -U username filename.sql` – Fábio Araújo Aug 07 '19 at 18:20
1

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
In my case I am running postgres on different port, so I have to add -p along with the above command – raj03 Jun 21 '22 at 06:33

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

163

I believe that you want to run in psql:

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

edited Aug 20 '12 at 09:46

j0k

22,600
28
79
90

answered Aug 20 '12 at 05:27

Arran Ubels

1,764
1
10
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 75 · Answer 4 · edited Mar 02 '15 at 13:33

75

That worked for me:

sudo -u postgres psql db_name < 'file_path'

edited Mar 02 '15 at 13:33

kenorb

155,785
88
678
743

answered Oct 28 '14 at 13:56

ivanacorovic

2,669
4
30
46

Always gave Error for all tables after import postgres DB : Unique violation: 7 ERROR: duplicate key value violates unique constraint. Any solution? – N.S Jan 23 '23 at 07:31
Are you sure your records have unique keys? Maybe you already have records with the same keys in the database? – ivanacorovic Jan 24 '23 at 13:52

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

67

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,257
1
12
11

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! – anaotha 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. – questionto42 Jul 24 '21 at 12:30

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

58

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,335
30
28

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,441
2
29
63

answered May 27 '15 at 18:26

alan

3,246
1
32
36

score 20 · 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

that's is not a good solution for large database files. – Bruno Ribeiro Oct 29 '22 at 18:08 — Bruno Ribeiro, Oct 29 '22 at 18:08

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

20

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

answered Dec 30 '19 at 12:24

rubo77

19,527
31
134
226

What's the functionality of `-i` option in this command? – Hamid Rasti Jun 26 '23 at 11:35
see `man sudo` which ist the same as `--login` – rubo77 Jun 30 '23 at 07:14

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

18

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

2,461
2
25
33

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

10

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,435
3
23
39

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

10

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

269
3
6

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

2,157
19
18

score 6 · Answer 14 · answered Sep 16 '22 at 13:29

6

I used this

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

answered Sep 16 '22 at 13:29

Yusuf Ganiyu

842
9
8

score 5 · Answer 15 · 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

982
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 3 · Answer 16 · answered Jul 13 '20 at 09:07

3

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

apet

958
14
16

score 1 · Answer 17 · answered Aug 07 '23 at 09:26

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

score 0 · Answer 18 · 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

score 0 · Answer 19 · answered May 11 '23 at 16:01

0

Either do this way

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

OR

psql fawkes_development < 2023-05-09T221119Z_pgdump

answered May 11 '23 at 16:01

Taimoor Changaiz

10,250
4
49
53

score 0 · Answer 20 · answered Jul 15 '23 at 00:01

0

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

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

answered Jul 15 '23 at 00:01

Usama Munir

589
9
11