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.

Fryer

Tables and tablespaces are not the same thing. To see the tables list use \dt+ instead of \db. Normally, restoring a SQL dump doesn't create a new tablespace, unless you've told so. – Syd

To see what tablespaces are look here – Syd

To see the available psql \ commands in a psql session type \?. – Syd

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!

Syd

I executed above both commands, but i don't know there's nothing showing into the myTestDB\Schemas\Tables(0). There 's no table downloaded or restored. Can I upload here the SQL file or somehow you can check? – Fryer

yes, upload it to pastebin or a similar service and put the link here – Syd

pastebin is not allowing me to upload .SQL file or somehow the file of having size 375 MB. any other way? – Fryer

@swasheck : you mean in normal text editor like notepad or WordPad? – Fryer

its a huge file of 363252 KB, adn taking too long time and during this CPU usage is VERY high..I think it will be loaded properly. Taking time.. – Fryer

I will post the update at morning, as it is still in execution and not loaded into WordPad and consumed almost the whole memory. Still Loading..... But I would update the post with latest stuff as soon as it completes opening into WordPad.exe – Fryer

WordPad won't work, it tries to load the whole thing. You need a programmer's text editor like Notepad++ that can handle big files. – Chickabiddy

BTW, I just wrote a giant rant on how bad the user interface/usability experience is for restoring PostgreSQL backups on windows. Heh. blog.ringerc.id.au/2012/05/… . Please add a comment explaining what you found hard, because this really needs to be improved and the more we know about what is confusing, the better. – Chickabiddy

@CraigRinger last week I spent a few days tinkering and sketching a cross-platform GUI (Qt/C++) for pg_dump and pg_restore for this exact reason. The first thing I looked at was pgAdmin3. When I [finally] found the backup dialog.. I almost shed some tears. – Syd

@Craig Ringer & swesheck: Thanks for your Notepad++ Suggetion. Now it opened into Notepad++. DUMP is opened into it. Now tell me what I need to do with this? my objective was to restore the whole database (SQL) form into the PostGreSQL( i.e it must show properly the tables and other things of DB ). What should I do now with PostGres? – Fryer

now I tried alot fter elevating its (PostGres reinstalltion to default directory and giving full rights) security level following commands, newTestDB-# /i E:\db-rbl-restore-20120511_Dump-20120514.sql and first it was giving me the same error and then after that now it is showing me list default postgres tablespaces. Look at edit – Fryer

Answer 2

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

Socialist

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.

Ruction

The slashes in the path are also in the incorrect direction, and will result "invalid argument" errors. I had to change all my slashes in my path to be forward slashes instead of back slashes, like RonZ said below. – Imogeneimojean

@Imogeneimojean - What OS are you on? – Ruction

Windows 2008r2 and Windows 7. It sounds like you are using Mac OS X --- does that mean the slash direction here is OS dependent? – Imogeneimojean

Slashes in the context of file paths are OS dependent (on Windows they are backslashes, and on Unix-like systems they are forward slashes), however it is possible for any given program (in this case psql) to override the typical directory delimiters and impose a custom format. I had assumed that the directory delimiters would be consistent with the underlying OS, however based on other answers it seems psql does its own FILENAME parsing and reassembly. I have updated my answer accordingly, thanks. – Ruction

Answer 4

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"

Concertize

Answer 5

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

Zabrine

Answer 6

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.

Kop

Answer 7

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

Mehta

Answer 8

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>

Reiners