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.
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.
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 13:56.
Reason: sensitive information