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?
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 :)
For Postrgres 9.2
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U [user] -d [db] [filename].dump
Have a look at the pg_restore command.
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:
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.
psql is for plain text dumps, use pg_restore.
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/)
