34

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.

Improve this question
2
  • Does the user you're running this command with has access to that file ?
    – mat
    Commented Feb 21, 2010 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
    Commented Feb 21, 2010 at 9:15

6 Answers 6

Reset to default
43

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

pg_restore -c -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
-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.

Improve this answer
2
  • 1
    how do same with tar.gz ?
    – eri
    Commented Mar 5, 2018 at 9:07
  • This did work for me but it seems like the -i and -W did not work anymore and had to be removed. resulting in: pg_restore -c -U postgres -d databaseName -v "/tmp/client03.tar"
    – Stephen Miller
    Commented Apr 12, 2020 at 10:26
3

When using PgAdmin III to do the restore for me, it worked flawlessly by using the following command which it constructed itself:

pg_restore --host localhost --port 5432 --username "my_user_name" --dbname "my_db_name" --role "my_user_name" --no-password  --verbose "/Users/me/Desktop/backup_file.tar"

Note to avoid warnings it's a good idea to have the role of the owner of objects in the backup file already existing in the target server. Also you should already have the target DB created and owned by that role.

Improve this answer
1

For version 9.5, run the following in command line

pg_restore -W -c -U [username] -d [database_name] -v "[path to extracted tar]"

Improve this answer
0

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.

Improve this answer
4
  • 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
    Commented Feb 21, 2010 at 10:05
  • 1
    The "-z" would return an error, as it is just a tar, not compressed with gzip.
    – Alex
    Commented Feb 21, 2010 at 15:28
  • Alex is 100% correct.
    – Stephen Thompson
    Commented Feb 21, 2010 at 21:01
  • I use unp which does unpackung always right
    – rubo77
    Commented Feb 13, 2023 at 6:48
0

Below was generated for me from pgAdmin iii by clicking on the database and right clicking and selecting restore. I navigated to the .tar file I had and it did it on its own.

/usr/bin/pg_restore --host localhost --port 5434 --username "postgres" \
--dbname "dvdrental" --no-password  --schema public --verbose \
"/home/npena/Desktop/dvd/dvdrental.tar"
Improve this answer
0

GNU/LINUX CONSOLE:

pg_restore -h 127.0.0.1 -p 5432 -U "postgres" -d "dvdrental_tpl" -v "/var/backups/dvdrental.tar";

-- Detail Help

pg_restore --help

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .