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

User · Answer

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

User · Answer

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  create database for restore the backup    opt PostgreSQL 9 1 bin psql -h  localhost  -p 5432 -U postgres -c  CREATE DATABASE mydatabase   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

User · Answer

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

User · Answer

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 gt 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 gt psql exe -U postgres -f c  TEMP try1 sql CREATE TABLE  C  Program Files PostgreSQL 9 2 bin gt psql exe -U postgres -f c  TEMP try1 sql CREATE TABLE  C  Program Files PostgreSQL 9 2 bin gt

User · Answer

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

User · Answer

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

User · 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   lt  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

[sql] How to restore PostgreSQL dump file into Postgres databases?

Examples related to sql

Examples related to postgresql

Examples related to dump

Examples related to database-restore

Examples related to database-dump