pg_dump No space left on device debian lenny 2.6.26-2-amd64 PG_VERSION 8.3

Hi,
trying to perform pg_dump
postgres@host:~$ pg_dump -Ft database > database.tar
I run into “No space left on device”. Surprisingly it is not the postgres own home partition /var/lib/postgresql BUT / root! And for sure it’s not the /tmp directory as part of / which gets filled up. Tried already env TEMP=/var/lib/postgresql but did not show any change. What gets filled up and why? How to avoid that?

The database runs with quite a lot of fairly complex triggers/rules using R,plperl,plperlu,plpgsql.
Can anybody give me a hint please (to whom I might direct my request …) That would be really great!
Chris

postgres@host:~$ env
TERM=xterm
SHELL=/bin/bash
OLDPWD=/root
USER=postgres
PATH=/usr/local/bin:/usr/bin:/bin:/usr/games
MAIL=/var/mail/postgres
PWD=/var/lib/postgresql
LANG=en_US.UTF-8
PS1=${debian_chroot:+($debian_chroot)}\u@\h:\w$
HOME=/var/lib/postgresql
SHLVL=2
LOGNAME=postgres
_=/usr/bin/env

Which partitions do you have in your system?
Before the backup, what is the fill level of these partitions?

You wrote that /tmp is not filled up - so why are you trying to have the temp directory on another partition?
This makes no sense.

hello :astonished:

host:~# df -h
/dev/sda2 9.4G 2.8G 6.6G 30% /
tmpfs 2.0G 0 2.0G 0% /lib/init/rw
udev 10M 676K 9.4M 7% /dev
tmpfs 2.0G 0 2.0G 0% /dev/shm
/dev/sda4 18G 4.2G 14G 24% /home
/dev/sda3 38G 18G 21G 46% /var

host:~# du -h -s /var/lib/postgresql/8.3/main/base/
11G /var/lib/postgresql/8.3/main/base/

btw: database runs also connections to some (~33) listeners via pg_listener

Really does NOT make any sense trying to have the temp directory on another partition … I did read something like that for winOS and just tried it as “last” effort.

Into which directory did you dump the backup?
Based on your initial posting, it seems to be /var/lib/postgresql?
Is this partition filling up during your backup?

If not, what are the biggest files on your / partition during or after the backup?

find / -xdev -type f -size +10M

while running
postgres@host:~/backup$ pg_dump -Ft database >/var/lib/postgresql/backup/database.tar
and shortly before killing this:

host:~# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 9.4G 8.4G 991M 90% /
tmpfs 2.0G 0 2.0G 0% /lib/init/rw
udev 10M 676K 9.4M 7% /dev
tmpfs 2.0G 0 2.0G 0% /dev/shm
/dev/sda4 18G 4.2G 14G 24% /home
/dev/sda3 38G 18G 21G 46% /var

host:~# find / -xdev -type f -size +10M
/usr/lib/gcc/x86_64-linux-gnu/4.3/gnat1
/usr/lib/mysql/libmysqld.a
/usr/lib/libicudata.so.38.1
/usr/lib/libxmlada.a
/usr/lib/liblapack.a
/usr/lib/libgcj.so.90.0.0
/root/.cpan/Metadata
/root/.cpanplus/sourcefiles.2.21.stored

du -s /tmp/
23602 /tmp/

The partition /var/ is filling up during backup; size of dump is then arround 1.5GB.

But you said, that / is getting full?
By the way, it could be that you are already near the limit of / anyway, some percent of the storage are reserved for root only.

In your listing, I don’t see any big files on /.

But you said, that / is getting full?

right: starting from
/dev/sda2 9.4G 2.8G 6.6G 30% /
up to 100% means 6.6G more BUT without showing anything special by
find / -xdev -type f -size +10M

So, why are 6.6G consumed an even that size is not enough to perform the dump???

As I said, you don’t have 6.6 GB, you have less because of the reserved disk space for root.
Can you stop the dump from time to time with Ctrl+S and look what’s going on in your /?

At the time the usage of /dev/sda2 was arround 90% I did Ctrl+S (s lowercase) BUT it did not stop:

postgres@host:~/backup$ pg_dump -Ft database >/var/lib/postgresql/backup/database.tar
^Spg_dump: [tar archiver] could not write to output file: No space left on device
(i-search)`’:

And it seems to be very hard to get a deeper look at the filesystem while pg_dump is running because of the load.
/dev/sda2 is of type reiserfs

Oh, wait … how big are your tables? Might it be, that one of the tables is several GB in size?
I see, that you are using the tar format - please try again with the custom format.

EXCELLENT - Thank you a lot!!!
pg_dump -Fc seems to do the job (/dev/sda2 is quiet now).
But may I ask you, why this kind of problem exists with tar format in combination with bigger tables?

PS may be keyword and subject of thread should be changed to something like … -ft & tar Format & big table … how to do this?

The problem is the tar format, which only allows single objects up to 8 GB.
I missed the format in your initial posting.

Dunno if many people even use the tar format.
Recent documentation contains a warning about this, and pg_dump is clearly giving an incorrect error message.