Postgresql – How to restore PostgreSQL database from .tar file

postgresql

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.

Best Answer

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.

Related Solutions

Postgresql – Recover PostgreSQL database from filesystem backup

Please be aware that in PostgreSQL, your cluster data directory is a self-contained unit that cannot reliably be restored in parts, as per the documentation here.

Your best hope is to use the full data directory of the old server and to start a server on it, then restore from a dump of the database you get from there:

Get a postgresql.conf that fit the configuration on the new host going (orient yourself by the files in the old - which you change here - and new data directories)
Find out the user name of the old installation's PostgreSQL superuser - most likely postgres.
Create this user name (UNIX user) on the new host, unless it's already there.
Give (chown) the old data directory to that user, unless the uid matches the one on the old server.
Configure pg_hba.conf in the old data directory to allow connections from local users to the same postgres user name (this is normally the default, but check).
Run the old database cluster as the UNIX user with the same name as the old data directory's superuser, e.g. for user postgres:
```
sudo -u postgres postgres -D old_data_directory

or with su:

su postgres
postgres -D old_data_directory
```

dump the old database, again with the usual user name, postgres:

sudo -u postgres pg_dump -p /path/to/socket/for/old/directory database_name > dump_file.sql

becoming postgres with su analogous to above

adapt the dump_file.sql to the new server's user names etc; create the empty database with appropriate ownership there

scan the database into the new server:

sudo -u postgres psql -p /path/to/socket/for/new/directory new_database_name

once again, getting postgres privileges using su analogous to above

That should get you set, or at least show a good path to get there.

Postgresql – Can’t restore PostgreSQL database backup

I solved this problem with the following steps:

pg_restore -f db1.sql-v db1.tar

I then removed everything from the db1.sql file except for the table1 copy command. Then ran:

psql -d db1 < db1.sql

This then gave me the exact line number within the file where the error was occurring. I then opened the file and removed the problem character and re-ran the script.

Best Answer

Related Solutions

Postgresql – Recover PostgreSQL database from filesystem backup

or with su:

becoming postgres with su analogous to above

once again, getting postgres privileges using su analogous to above

Postgresql – Can’t restore PostgreSQL database backup

Related Topic