Restore a postgres backup file using the command line?

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

There are two tools to look at, depending on how you created the dump file.

Your first source of reference should be the man page pg_dump as that is what creates the dump itself. It says:

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql(1). Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications even on other SQL database products.

The alternative archive file formats must be used with pg_restore(1) to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive file formats are designed to be portable across architectures.

So depends on the way it was dumped out. If using Linux/Unix, you can probably figure it out using the excellent file(1) command - if it mentions ASCII text and/or SQL, it should be restored with psql otherwise you should probably use pg_restore.

Restoring is pretty easy:

psql -U username -d dbname < filename.sql

-- For Postgres versions 9.0 or earlier
psql -U username -d dbname -1 -f filename.sql

or

pg_restore -U username -d dbname -1 filename.dump

Check out their respective manpages - there's quite a few options that affect how the restore works. You may have to clean out your "live" databases or recreate them from template0 (as pointed out in a comment) before restoring, depending on how the dumps were generated.

create backup

pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f 
"/usr/local/backup/10.70.0.61.backup" old_db

-F c is custom format (compressed, and able to do in parallel with -j N) -b is including blobs, -v is verbose, -f is the backup file name.

restore from backup

pg_restore -h localhost -p 5432 -U postgres -d old_db -v 
"/usr/local/backup/10.70.0.61.backup"

important to set -h localhost - option