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

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? – kissgyorgy 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? – kissgyorgy 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
up vote 3 down vote accepted

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

Your Answer


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.