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.

I'm running out of space from my harddrive which has a 18GB PostgreSQl databse.
When I try pg_dump to a different drive, PostgreSQL creates temporary files on the disk it's dumping from, so I run out of space and the dump fails.

I tried this from Stackoverflow and a small file is created in the new directory, but nothing else, and pg_dump still writes to the original disk.

How do I change temp directory for pg_dump?

Note: My work_mem setting is pretty high already, I can't change that.
My db version is 9.0.13.

share|improve this question
    
Can you mount the temp dir (is it /tmp?) on an other disk? –  dezso Oct 29 '13 at 9:15
    
Yes, I could put it to another disk, but don't know how to safely do that? –  Walkman Oct 29 '13 at 13:42
    
How are you invoking pg_dump? Where is the temp file, what is it called? –  Peter Eisentraut Oct 29 '13 at 20:04
    
pg_dump -U myusername 'database' --format=tar | gzip > /anotherdrive/filetodump.tar.gz What temp file is where? –  Walkman Oct 29 '13 at 20:25
    
I guess that is the temp file that gzip uses, not one from pg_dump –  a_horse_with_no_name Nov 3 '13 at 14:17
show 2 more comments

1 Answer

up vote 3 down vote accepted
+50

The temporary files are a side effect of the --format=tar option.

A backup in default plain format goes through without temporary tables in the local drive. Omit the option to produce a backup in plain format without temporary local files.

share|improve this answer
add comment

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.