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!

Answer 2

The problem with your attempt at the psql command line is the direction of the slashes:

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

To be clear, psql commands start with a backslash, so you should have put \i instead. What happened as a result of your typo is that psql ignored everything until finding the first \, which happened to be followed by db, and \db happens to be the psql command for listing table spaces, hence why the output was a List of tablespaces. It was not a listing of "default tables of PostgreSQL" as you said.

Further, it seems that psql expects the filepath argument to delimit directories using the forward slash regardless of OS (thus on Windows this would be counter-intuitive).

It is worth noting that your attempt at "elevating permissions" had no relation to the outcome of the command you attempted to execute. Also, you did not say what caused the supposed "Permission Denied" error.

Finally, the extension on the dump file does not matter, in fact you don't even need an extension. Indeed, pgAdmin suggests a .backup extension when selecting a backup filename, but you can actually make it whatever you want, again, including having no extension at all. The problem is that pgAdmin seems to only allow a "Restore" of "Custom or tar" or "Directory" dumps (at least this is the case in the MAC OS X version of the app), so just use the psql \i command as shown above.