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

You might need to be logged in as postgres in order to have full privileges on databases.

su - postgres
psql -l                      # will list all databases on Postgres cluster

pg_dump/pg_restore

  pg_dump -U username -f backup.dump database_name -Fc 

switch -F specify format of backup file:

• c will use custom PostgreSQL format which is compressed and results in smallest backup file size
• d for directory where each file is one table
• t for TAR archive (bigger than custom format)
• -h/--host Specifies the host name of the machine on which the server is running
• -W/--password Force pg_dump to prompt for a password before connecting to a database

restore backup:

   pg_restore -d database_name -U username -C backup.dump

Parameter -C should create database before importing data. If it doesn't work you can always create database eg. with command (as user postgres or other account that has rights to create databases) createdb db_name -O owner

pg_dump/psql

In case that you didn't specify the argument -F default plain text SQL format was used (or with -F p). Then you can't use pg_restore. You can import data with psql.

backup:

pg_dump -U username -f backup.sql database_name

restore:

psql -d database_name -f backup.sql

Answer 2

If you create a backup using pg_dump you can easily restore it in the following way:

1. Open command line window
2. Go to Postgres bin folder. For example: cd "C:\ProgramFiles\PostgreSQL\9.5\bin"
3. Enter the command to restore your database. For example: psql.exe -U postgres -d YourDatabase -f D:\Backup\.sql
4. Type password for your postgres user
5. Check the restore process

Answer 3

POSTGRESQL 9.1.12

DUMP:

pg_dump -U user db_name > archive_name.sql

put the user password and press enter.

RESTORE:

psql -U user db_name < /directory/archive.sql

put the user password and press enter.

Answer 4

Restoring a postgres backup file depends on how did you take the backup in the first place.

If you used pg_dump with -F c or -F d you need to use pg_restore otherwise you can just use

psql -h localhost -p 5432 -U postgres < backupfile

9 ways to backup and restore postgres databases

Answer 5

1.open the terminal.

2.backup your database with following command

your postgres bin - /opt/PostgreSQL/9.1/bin/

your source database server - 192.168.1.111

your backup file location and name - /home/dinesh/db/mydb.backup

your source db name - mydatabase

/opt/PostgreSQL/9.1/bin/pg_dump --host '192.168.1.111' --port 5432 --username "postgres" --no-password --format custom --blobs --file "/home/dinesh/db/mydb.backup" "mydatabase"

3.restore mydb.backup file into destination.

your destination server - localhost

your destination database name - mydatabase

create database for restore the backup.

/opt/PostgreSQL/9.1/bin/psql -h 'localhost' -p 5432 -U postgres -c "CREATE DATABASE mydatabase"

restore the backup.

/opt/PostgreSQL/9.1/bin/pg_restore --host 'localhost' --port 5432 --username "postgres" --dbname "mydatabase" --no-password --clean "/home/dinesh/db/mydb.backup"

Answer 6

try this:

psql -U <username> -d <dbname> -f <filename>.sql

Restore DB psql from .sql file