Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How can I resolve the following errors in 9.3.0:

psql mydb < ~/mydb_2013-10-25.backup.tar

results in

ERROR:  could not open file "$$PATH$$/2309.dat" for reading: No such file or directory
(1 row)

I see the file 2309.dat in the tar, so I am slightly confused what can be the reason. I run 9.3.0 on OS X and made backup with 9.1.9 on Ubuntu.

Edit: I managed to restore data with pgAdmin3-1.18.1.

share|improve this question

closed as off-topic by dezso, Max Vernon, Mark Storey-Smith, Michael - sqlbot, Mike Walsh Oct 28 '13 at 23:25

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, Max Vernon, Mark Storey-Smith, Michael - sqlbot, Mike Walsh
If this question can be reworded to fit the rules in the help center, please edit the question.

up vote 2 down vote accepted

Update 10/28

Using pg_dump it is possible to backup a database as a tar archive

pg_dump -v -o -c -Ft -f test_backup.tar testdb

Here are the contents of the test_backup.tar file

-bash-4.1$ tar -tvf test_backup.tar 
-rw------- 2048/1024      1605 2013-10-28 14:50 toc.dat
-rw------- 2048/1024         5 2013-10-28 14:50 1769.dat
-rw------- 2048/1024      1654 2013-10-28 14:50 restore.sql

To restore the backup.tar file, I used this command

pg_restore -c -Ft test_backup.tar -d testdb

-c is to drop objects being restored from the database

-Ft is to use the tar archive format

-d is the name of the database

-v is for verbose

So in conclusion, psql can't be used to restore or create files in the tar format. You must use pg_dump to create them using the -Ft option and then again with pg_restore to restore using the same file.


share|improve this answer
The original name of the file is "mydb_2013-10-25.backup", which is a tar-archive. I have renamed it to make sure that it contains files in the error message. Sorry for the confusion. – Andrei Oct 25 '13 at 16:10
Hello @Andrei. I have updated my answer – Craig Efrein Oct 28 '13 at 7:58
Hi @Craig Efrein! The backup file is a tar-archive containing restore.sql and a number of *.dat files with record data. I believe that all of them are required. I guess restore logic has been changed between 9.1.9 and 9.3.0 so it fails. I should probably check the changelog but have not enough motivation at the moment. Thanks anyway! – Andrei Oct 28 '13 at 13:30
@Andrei - updated my answer. – Craig Efrein Oct 28 '13 at 14:06

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