PostgreSQL Dump – How to Change Temporary Directory for pg_dump?

dumppostgresql

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.

Best Answer

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.

Related Solutions

Postgresql – What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table

In more recent versions of Postgres (I think since 8.3) you can assign a special tablespace for temporary tables which might help you. This is documented here:

http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-TEMP-TABLESPACES

Given the fact that 8.2 will be de-supported at the end of the year it might be a good idea to upgrade. There have been numerous enhancements to VACUUM and the handling of temporary files since 8.2 so you might benefit from those.

Edit:
The reason why I think this (separate tablespace) could help you, is that you can simply drop and recreate the tablespace (files) to reclaim the spaces occupied.

But then I'd assume that due to all the improvements that were implemented in the last 5 years the current version might release the space without any further action from your side (especially because VACUUM FULL has been completely rewritten in 9.0)

Postgresql – postgres backup / restore: restored database much smaller

Maybe it's just index bloat. VACUUM FULL does not help with index bloat, on the contrary, as said in the doc for 8.4:

The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM would. The FULL option does not shrink indexes; a periodic REINDEX is still recommended. In fact, it is often faster to drop all indexes, VACUUM FULL, and recreate the indexes.

(in more recent versions, this advice has disappeared because VACUUM FULL has been reimplemented differently).

See Routine reindeing and the REINDEX command.

The easiest way to reindex is to connect to the database with the db user that owns it and issue:

REINDEX database test_db;

Ideally, it should be done immediately after VACUUM FULL and the database should shrink to its lowest possible size at this point in time.

Best Answer

Related Solutions

Postgresql – What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table

Postgresql – postgres backup / restore: restored database much smaller

Related Question