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.
Best 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 instructpg_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
:or alternatively from a
psql
session,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 thepg_restore
tool.If you're working on a unix-like, try this:
otherwise, take a look at the html docs. Good luck!