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.
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
Geändert von chrispg (14.09.2011 um 15:53 Uhr)
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.
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören
Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler
PostgreSQL Service & Support
hello
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.
Geändert von chrispg (15.09.2011 um 02:37 Uhr)
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
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören
Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler
PostgreSQL Service & Support
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.
Geändert von chrispg (15.09.2011 um 10:50 Uhr)
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 /.
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören
Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler
PostgreSQL Service & Support
> 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 /?
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören
Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler
PostgreSQL Service & Support
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
Geändert von chrispg (15.09.2011 um 11:17 Uhr)
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.
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören
Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler
PostgreSQL Service & Support
Links: Tobias Bauer - IT- und Funkdienstleistungen - Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen - Funkforum
Lesezeichen