How to restore PostgreSQL dump file into Postgres databases?

Question

I have a dump file with a .SQL extension (in fact it is a plain-text SQL file). I want to restore it into my created databases. I am using pgAdmin III, and when I use its "Restore Wizard" it does not highlight the button "Restore". Instead it is expecting a .backup file extension.

I tried using shell the commands for restoring the dump, but it still didn't work.

I am a newbie at this. If anybody could help me I would be obliged.

Edit

I used following command to the Shell SQL Pane of PostGres while sitting at the newTestDB.

newTestDB-# \i E:\db-rbl-restore-20120511_Dump-20120514.sql

It still gave the same error ("Permission Denied").

After elevating permissions it just shows me the default tables of PostgreSQL:

      List of tablespaces
Name       |  Owner   | Location
-----------+----------+----------
pg_default | postgres |
pg_global  | postgres |

(2 rows)

I don't know what to do for importing/restoring database from an SQL file.

Answer 1

You didn't mention how your backup was made, so the generic answer is: Usually with the psql tool.

Depending on what pg_dump was instructed to dump, the SQL file can have different sets of SQL commands. For example, if you instruct pg_dump to dump a database using --clean and --schema-only, you can't expect to be able to restore the database from that dump as there will be no SQL commands for COPYing (or INSERTing if --inserts is used ) the actual data in the tables. A dump like that will contain only DDL SQL commands, and will be able to recreate the schema but not the actual data.

A typical SQL dump is restored with psql:

psql (connection options here) database  < yourbackup.sql

or alternatively from a psql session,

psql (connection options here) database
database=# \i /path/to/yourbackup.sql

In the case of backups made with pg_dump -Fc ("custom format"), which is not a plain SQL file but a compressed file, you need to use the pg_restore tool.

If you're working on a unix-like, try this:

man psql
man pg_dump
man pg_restore

otherwise, take a look at the html docs. Good luck!