Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 5 down vote favorite

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.

share|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

2 Answers

up vote 4 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.

share|improve this answer
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.

share|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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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