Postgresql – pg_dump fails, no space left, need to delete partial backup file to free space

Postgres is running out of space in its temporary dumping ground while trying to complete your request -- The way to fix this is to either run a simpler query (which probably isn't helpful) or to free up more space on the drive that holds PGDATA/base/pgsql_tmp/ (If you haven't done a VACUUM FULL in a while now may be a good time :-)

You can also put pgsql_tmp on its own partition (mind the permissions as Postgres tends to get snippy about those things)

Note that I believe pgsql_tmp is per-tablespace these days, so if this isn't the main (base) tablespace substitute appropriately :-)

I suggest to change dump format to custom (-Fc, --format c) and avoid tar format at all. AFAIK there are no advantages of using tar format instead of custom (both works with pg_restore).

Reading between the lines, I suspect you are trying to use 'tar' output format, which does have a need to make temp files that can be large. If I guessed right, I'd suggest using 'custom' format instead. There really is no advantage to tar format, and several disadvantages besides this one.

from http://postgresql.1045698.n5.nabble.com/Out-of-space-making-backup-td1904089.html

If your installation comes from package, then probably you have custom format (zlib) support "out of the box". You can control compression level with -Z option (default value is 6) from 0 (no compression) to 9.

BTW check about your -c option. According to http://www.postgresql.org/docs/9.0/static/app-pgdump.html

Output commands to clean (drop) database objects prior to (the commands for) creating them.

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

BTW2 For convenience you can use automatic PostgreSQL environment variables such as PGHOST, PGUSER, PGDATABASE, PGPORT.