7

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.

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

1 Answer 1

6
+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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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