I'm running out of space from my hard drive which has a 18GB PostgreSQL database.
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.

  • 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
  • 1
    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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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