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.

Accepted Answer

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!

How to restore PostgreSQL dump file into Postgres databases?

Solution

Problem