current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.5 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 182 down vote favorite
57

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 sever to test this.

The only problem is that i don't have an idea how i can import this database in the postgresql 9 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 i keeps giving me errors when he 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 this with the command prompt but i can't really 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?

share|improve this question
    
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 at 23:26
up vote 322 down vote accepted 
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.

share|improve this answer
1  
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
9  
@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
2  
@Dazz:You could use -f switch (or --file) too – Grzegorz Szpetkowski Jul 27 '11 at 10:56
1  
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
15  
psql --username=postgres databasename < data_base_dump.sql – Maxence Jun 18 '13 at 18:27
up vote 165 down vote

Here is the command you are looking for.

psql -h hostname -d databasename -U username -f file.sql
share|improve this answer
11  
Good, but better append also the "-L logfile.log" param to log the output on file. – zerologiko Feb 12 '14 at 9:29
8  
Actually this have to be the accepted answer! – Alexey Tigarev Aug 12 '14 at 16:03
up vote 52 down vote

I believe that you want to run in psql:

\i C:/database/db-backup.sql
share|improve this answer
    
Had to use this because Emacs' eshell doesn't support input redirection. Thanks. – duma Mar 30 '13 at 3:11
    
Be sure to change to the database first with \c <database_name> – hkong Aug 18 at 0:54
up vote 10 down vote

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.

share|improve this answer
    
This solution worked for me while the most voted solution yielded "stdin is not a tty" error. – 김민준 Nov 25 at 7:56
up vote 10 down vote

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.

share|improve this answer
up vote 9 down vote

That worked for me:

sudo -u postgres psql db_name < 'file_path'
share|improve this answer
up vote 2 down vote

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.

share|improve this answer
up vote 2 down vote

I use:

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

Hope this will help someone.

share|improve this answer
up vote 0 down vote

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

psql -h localhost -U user -W -d database_name -f path/to/file.sql
share|improve this answer

protected by Community♦ Mar 3 '15 at 6:04

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.