Restore a postgres backup file using the command l

Question

I'm new to postgresql, and locally, I use pgadmin3. On the remote server, however, I have no such luxury.

I've already created the backup of the database and copied it over, but, is there a way to restore a backup from the command line? I only see things related to GUI or to pg_dumps, so, if someone can tell me how to go about this, that'd be terrific!

Answer 1

Try to see if the following commands can help you:

sudo su - yourdbuser
psql
\i yourbackupfile

Answer 2

1) Open psql terminal.

2) Unzip/ untar the dump file.

3) Create an empty database.

4) use the following command to restore the .dump file

<database_name>-# \i <path_to_.dump_file>

Answer 3

Backup and restore with GZIP

For larger size database this is very good

backup

pg_dump -U user -d mydb | gzip > mydb.pgsql.gz

restore

gunzip -c mydb.pgsql.gz | psql dbname -U user

https://www.postgresql.org/docs/9.1/static/backup-dump.html

Answer 4

I was having authentication problems running pg_dump, so I moved my dump file

mv database_dump /tmp

into the temp directory and then ran

su -u postgres
cd /tmp
pg_restore database_dump

If you have a large database dump, you may just want to create another directory where your current user and the postgres user can access and putting the database dump file into that.

Answer 5

See below example its working

C:/Program Files/PostgreSQL/9.4/bin\pg_restore.exe --host localhost --port 5432 --username "postgres" --dbname "newDatabase" --no-password --verbose

"C:\Users\Yogesh\Downloads\new Download\DB.backup"

Answer 6

Sorry for the necropost, but these solutions did not work for me. I'm on postgres 10. On Linux:

1. I had to change directory to my pg_hba.conf.
2. I had to edit the file to change method from peer to md5 as stated here
3. Restart the service: service postgresql-10 restart

4. Change directory to where my backup.sql was located and execute:
   psql postgres -d database_name -1 -f backup.sql

   -database_name is the name of my database

   -backup.sql is the name of my .sql backup file.