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

You might need to set permissions at the database level that allows your schema owner to restore the dump.

Answer 3

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.

Answer 4

I find that psql.exe is quite picky with the slash direction, at least on windows (which the above looks like).

Here's an example. In a cmd window:

C:\Program Files\PostgreSQL\9.2\bin>psql.exe -U postgres
psql (9.2.4)
Type "help" for help.

postgres=# \i c:\temp\try1.sql    
c:: Permission denied
postgres=# \i c:/temp/try1.sql
CREATE TABLE
postgres=#

You can see it fails when I use "normal" windows slashes in a \i call. However both slash styles work if you pass them as input params to psql.exe, for example:

C:\Program Files\PostgreSQL\9.2\bin>psql.exe -U postgres -f c:\TEMP\try1.sql
CREATE TABLE

C:\Program Files\PostgreSQL\9.2\bin>psql.exe -U postgres -f c:/TEMP/try1.sql
CREATE TABLE

C:\Program Files\PostgreSQL\9.2\bin>

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

4. create database for restore the backup.

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

5. 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

Combining the advice from MartinP and user664833, I was also able to get it to work. Caveat is that entering psql from the pgAdmin GUI tool via choosing Plugins...PSQL Console sets the credentials and permission level for the psql session, so you must have Admin or CRUD permissions on the table and maybe also Admin on the DB (do not know for sure on that). The command then in the psql console would take this form:

postgres=# \i driveletter:/folder_path/backupfilename.backup

where postgres=# is the psql prompt, not part of the command.

The .backup file will include the commands used to create the table, so you may also get things like "ALTER TABLE ..." commands in the file that get executed but reported as errors. I suppose you can always delete these commands before running the restore but you're probably better safe than sorry to keep them in there, as these will not likely cause the restore of data to fail. But always check to be sure the data you wanted to resore actually got there. (Sorry if this seems like patronizing advice to anyone, but it's an oversight that can happen to anyone no matter how long they have been at this stuff -- a moment's distraction from a colleague, a phone call, etc., and it's easy to forget this step. I have done it myself using other databases earlier in my career and wondered "Gee, why am I not seeing any data back from this query?" Answer was the data never actually got restored, and I just wasted 2 hours trying to hunt down suspected possible bugs that didn't exist.)

Answer 7

By using pg_restore command you can restore postgres database

First open terminal type

sudo su postgres

Create new database

createdb [database name] -O [owner]

createdb test_db [-O openerp]

pg_restore -d [Database Name] [path of dump file]

pg_restore -d test_db /home/sagar/Download/sample_dbump

Wait for completion of database restoring.

Remember that dump file should have read, write, execute access, so for that you can apply chmod command

Answer 8

This is what worked for me when restoring Postgres database in Windows. To begin with, right click the command shell and select "Run as administrator" to avoid permission denied issues. Execute the same command but with some changes:

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

If you notice, the command "newTestDB-# \i E:\db-rbl-restore-20120511_Dump-20120514.sql" has been changed to "newTestDB-# \i E:/db-rbl-restore-20120511_Dump-20120514.sql" with the / being used in the file path instead of the \ slash.