Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2014
    Posts
    2

    Unanswered: Failure restoring a postgresql 9.1 database

    I have a backup of my database as a .backup file that I made via pgAdminIII. The backed up database has been accidentally deleted so I have to use this .backup file to restore it. I am only praying that all the settings were correct while doing the original back up.

    After creating the new test database I right click on it and choose restore. I change nothing at all except choose the .backup file. After doing so the "Restore" button is grayed out and wont allow me to select it.

    I am running postgreSQL 9.1

    What am I doing wrong? Thanks in advance!

    Ben

  2. #2
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    OK lets start w/ looking at the backup file. I know you used pgAdmin3 to generate a backup but open the file using a preffered text editing application and what do you see. Generally my backup files are .sql in pgAdmin3 but it really doesn't matter.

    In order to recover in PostgreSQL, you need to create the database exactly as it is called in the backup file and perform identical ownership / grants. Then use 'pg_dump' to restore into the newly created database.

    If this doesn't work, you need to show us your dump file formatting to see what pgAdmin3 did, then verify the newly created database container and permissions, then send us a copy of the error you're getting.

    This should be fairly simple and straight forward. I also recommend you not use that tool for backups.

  3. #3
    Join Date
    Feb 2014
    Posts
    2
    Thanks for the quick reply!

    I've attempted to restore it to a newly created database named exactly as the previous and it still has the "restore" button grayed out.

    If I change the format drop down from "Custom or TAR" to "Directory" and then back to "Custom or TAR" the "Display Objects" button is no longer greyed out. However here is the error code that is displayed once I hit "Display Objects":
    _________________
    C:/Program Files/PostgreSQL/9.1/bin\pg_restore.exe --host 192.168.1.2 --port 5432 --username "admin" --dbname "TruFab" --no-password --list "Z:\Xtuple\Database Backups\TruFab_xtuple_backup_12152014.backup"
    pg_restore: [archiver] input file does not appear to be a valid archive

    Process returned exit code 1.
    _________________

    The "Restore" button always stays greyed out.

    You said I "need to create the database exactly as it is called in the backup file and perform identical ownership / grants. Then use 'pg_dump' to restore into the newly created database."

    How do I go about doing that?

    Also attached the back up file.

    Thanks again for the help!!
    Last edited by benlevicox; 02-19-14 at 14:56. Reason: sensitive information

  4. #4
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    I converted your backup file. Please check your PM's. I don't want to attach your raw SQL dump for your database on the Internet. I can email it to you or we can transfer this safely...
    Last edited by CacheDrive; 02-18-14 at 15:56.

  5. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Here's my version of your backup file loaded into PostgreSQL 9.3 w/ no problems at all:

    http://i.imgur.com/gmdMNNi.png

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
  •