Home / server / Questions / 267616
Accepted
Cerin
Asked: 2011-05-09 09:32:54 +0800 CST

PG_Dump Fails Due to Mistaken Low Disk Space

  • 772

I'm trying to use pg_dump on my localhost to dump a 4GB PostgreSQL database on a remote machine. My localhost reports 15GB of space available. I'm piping the output to gzip. However, after about 15 minutes of processing, pg_dump aborts, stating "pg_dump: [tar archiver] could not write to output file: No space left on device". I continually monitor the amount of free disk space on my machine, and it always remains in the ~10GB range. Why is pg_dump failing prematurely due to low disk space, even though there's still plenty of space?

My command looks like:

pg_dump -c --host=${HOST} --username=${DATABASEUSER} --blobs --format=t ${DATABASE} | gzip -c > /tmp/db-backup.tar.gz
database postgresql

2 Answers

  1. Best Answer

    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.

    • 7

  2. The tar pg_dump format in PostgreSQL limits the size of any one table to 8GB, before compression is applied. And a 4GB database is not going to dump into a 4GB file; the text format will be bigger for some data types, and since I see you dumping blobs you could easily be in that category. So another possibility is that you have a table that's, say, 3GB; it expands to >8GB when dumped; and you're hitting the tar limit, which in enforced before the compression kicks in. Dumping in plain text or the custom format instead would eliminate your problem if this is what's happening.

    The other possibility is that your drive is sized such that ~10GB represents about 5% of it. If your partition is around 200GB, you could be hitting the limit where 5% of space is reserved for root on ext4. You can eliminate that using something like this if that's the case:

    sudo tune2fs -m 0 /dev/sda1
    • 2