Take the 2-minute tour ×
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.

How can I resolve the following errors in 9.3.0:

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

results in

...
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR:  could not open file "$$PATH$$/2309.dat" for reading: No such file or directory
 setval
--------
      1
(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.

1 Answer 1

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

Craig

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
1  
@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.