How to change temporary directory for pg_dump?

Question

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 09: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 03 '13 at 14:17
`pg_dump -U myusername 'database' --format=tar > /anotherdrive/filetodump.tar` eats free space the same way as with gzip — kissgyorgy, Nov 03 '13 at 14:37

score 6 · Accepted Answer · answered Nov 03 '13 at 23:43

6

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.

answered Nov 03 '13 at 23:43

Erwin Brandstetter

146,376
19
347
493

How to change temporary directory for pg_dump?

1 Answers1

Linked