Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    6

    Question Unanswered: postgresql reastore with a backup file in windows8

    How we do Restore an existing database 'students' with a backup file 'studentdb.backup' on postgresql via commamd prompt???
    Backup file path is C:\Program Files\PostgreSQL\pg94\bin

    thanks inadvance/////

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    This depends on how the backup was taken.

    If the backup file is a plain SQL script, then you need to run with psql, e.g.

    Code:
    psql -U postgres -d target_database -f "C:\Program Files\PostgreSQL\pg94\bin\studentdb.backup"
    If the backup file is a custom format you need to use pg_restore:
    Code:
    pg_restore -U postgres -d target_database "C:\Program Files\PostgreSQL\pg94\bin\studentdb.backup"
    In both cases you need to replace "target_database" with the name of the database into which you want to restore the dump.

    For more details please see the manual: https://www.postgresql.org/docs/curr...ckup-dump.html

    Unrelated, but: storing any kind of user data in "C:\Program Files\" is a really, really bad idea.
    Last edited by shammat; 11-08-16 at 08:25.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Feb 2009
    Posts
    6

    ERROR -pg_restore:options -d/--dname and -f/--file cannot be used together

    pg_restore -U postgres -d newdb -f -f "C:\Program Files\PostgreSQL\pg94\bin\studentdb.backup"

    ERROR displayed

    pg_restoreptions -d/--dname and -f/--file cannot be used together
    Last edited by fernandodonster; 11-08-16 at 06:20. Reason: spelling error

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Sorry, copy and paste error. -f shouldn't be used for pg_restore
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Feb 2009
    Posts
    6

    Exclamation

    Again error displayed(the backup file was generated with GUI pgAdmin3)
    pg_restore :[archeiver]input file apperars to be a text format dump.Please use psql


    then i user with psql command.
    psql -U postgres -d newdb -f "C:\Program Files\PostgreSQL\pg94\bin\olddb.backup"

    BUT displayed bulk list of errors that "ERROR constraint relation already exists.............etc" repeating many lines
    Is any problem with foreign key constraint........


    thanks in advance

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by fernandodonster View Post
    Again error displayed(the backup file was generated with GUI pgAdmin3)
    pg_restore :[archeiver]input file apperars to be a text format dump.Please use psql
    That's why I said it's important to know what kind of file it is.

    psql -U postgres -d newdb -f "C:\Program Files\PostgreSQL\pg94\bin\olddb.backup"

    BUT displayed bulk list of errors that "ERROR constraint relation already exists.............etc" repeating many lines
    Is any problem with foreign key constraint.
    That means that the tables (and all their constraints) already exist in the target database.

    You need to drop them first if you want to run the dump script.

    That's one of the reasons I prefer the custom format over the SQL script dump: you can decide when you import it if you also want to drop everything that is contained in the the dump.

    With the SQL dump you need to include the --clean option when generating the dump to make that happen.

    As you already have the SQL dump, the only thing you can do is to drop everything from the target database.

    And again: please do not store user data in "c:\Program Files". That directory is protected for a good reason. If you can store files in there it either means you are working as an administrator (which you shouldn't do) or you opened up the privileges on the directory (which you shouldn't do either).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •