postgresql- restoring .dump file

Question

I am new for psql. I got from my server data.dump file. I need to restore it in my local. I tried these commands.

i) psql -U postgres dbname -f servicedb.dump

Error:
      psql: warning: extra command-line argument "-f" ignored
      psql: warning: extra command-line argument "servicedb.dump" ignored

ii) psql -U postgres dbname < servicedb.dump

 Error:
              ^
 ERROR:  syntaxe error at or near "☺"
 LINE 1: ☺☺

What is this ".dump" file and how to restore it?

Ophthalmoscope

Answer 1

I got a .dump file from my server (Heroku). As Klaus said, pg_restore is the only way I could restore it in my local.

What I wrote in my terminal was:

pg_restore -c -d [database_name] [dumpfile_name].dump

There are a lot of options you can see in Klaus link of pg_restore :)

Remainderman

Answer 2

psql -f filenamed.dmp db_name

works fine

Zygophyllaceous

Does not work for binary dumps, which was indicated by OP's point ii. – Grapevine

Answer 3

For Postrgres 9.2

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U [user] -d [db] [filename].dump

Goddord

Answer 4

Have a look at the pg_restore command.

Universalism

Answer 5

I found it tricky in windows environment.

pg_restore will not work if its a text format dump. In that case, we need to use psql.

psql -U username -f database.dump databasename

It will prompt for the password of the username and then the restoring process will be initiated.

Madrigalist

Answer 6

pg_restore is far from obvious, this is the command I used to create a new database and restore the dumpfile into it on a remote Postgres instance running on AWS. If your connection is correct, pg_restore should immediately ask you to input your password)

pg_restore -h mypostgresdb.eu-west-1.rds.amazonaws.com -U adminuser --verbose -C -d existingdatabase mydbdump.dm

Where the switches are:

• -h - hostname on aws
• -U - username, this needs to be an admin user with permissions to create a db
• --verbose - get verbose output to screen
• -C - means create a brand new database from the dumpfile (it will be named whatever the db you dumped was called)
• -d - confusingly this needs to be the name of a database that already exists, basically pg_restore needs to connect to an existing DB so it can run the necessary scripts to create the new database
• mydbdump.dmp this is the location of the dumpfile you are attempting to restore.

Pitta

Answer 7

psql is for plain text dumps, use pg_restore.

Negation

Answer 8

If you have pgsql dump file (e.g. pgsql_dump.sql.gz) and want to restore it, then try following the below steps-

sudo su postgres
psql

drop database my_database;

(For dropping the existing database. Provide the name of the database you want to restore in place of my_database, if the database exists)

create database my_database;

(Provide the name of the database you want to restore in place of my_database)

\q

(This is to exit psql)

gunzip <  /tmp/pgsql_dump.sql.gz | psql -Upostgres my_database 

(Provide actual path where the dump is kept in place of /tmp/)

Tedi