up vote 4 down vote favorite
share [g+] share [fb]

I have all PostgreSQL databases backed up during incremental backups using WHM, which creates a $dbName.tar file.

Data is stored in these .tar files, but I do not know how to restore it back into the individual databases via SSH. In particular the file location.

I have been using:

pg_restore -d client03 /backup/cpbackup/daily/client03/psql/client03.tar

which generates the error 'could not open input file: Permission denied'

Any assistance appreciated.

link|improve this question
Does the user you're running this command with has access to that file ? – mat Feb 21 '10 at 8:15
Hi Mat, you would think so, (running as root) but have just solved the issue after many many combinations, and moving the raw .tar file to tmp.... pg_restore -c -i -U postgres -d client03 -v "/tmp/client03.tar" -W The -W at the end was the main trick which forced the password input as required which enabled everything to be put back together. – Stephen Feb 21 '10 at 9:15
feedback

2 Answers

up vote 3 down vote

Found the correct string of code, in case someone else finds this thread.

pg_restore -c -i -U postgres -d client03 -v "/tmp/client03.tar" -W

The break down was from http://www.postgresql.org/docs/7.3/static/app-pgrestore.html and a bit of trial and error.

Essentially...

-c to clean the database -i to ignore any database version checks -U to force a user -d to select the database -v verbose mode, don't know why "$$" the location of the files to import in tmp to get around permission issues -W to force asking for the password to the user (postgres)

Hope the above assists someone else.

link|improve this answer
feedback
up vote 0 down vote

I'm not certain it can import a .tar file. I would do a 

tar -zxvf client03.tar

to extract whatever was inside the file, and try pg_restore again. I know pg_restore works, as we have the same restore method from bare metal restores.

link|improve this answer
1  
Hi Stephen, yes apparently it can, it just took me a while to get the correct code, and to drop the file into a folder with permission to be accessed :) – Stephen Feb 21 '10 at 10:05
The "-z" would return an error, as it is just a tar, not compressed with gzip. – Alex Feb 21 '10 at 15:28
Alex is 100% correct. – Stephen Thompson Feb 21 '10 at 21:01
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.