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
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?
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.
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.