On a postgres 9.1.3 system we are seeing repeated problems with vanishng disk space. The problem is resolved by
restartingthe postgres cluster.
This cluster is replicated to another server (which does not show the same problem). It has several databases -- one
verylow activity currently ( all tables unlogged), one with lots of inserts and some reads (all logged), and one
databasewhich is created hourly and then dropped after making a copy.
It is this later operation that I suspect is causing the issues since we did not see this before this database was made
conjuredup.
Our monitors show that from inside postgres the database size regularly rises as the transient database is created and
dropsagain when the database is dropped. But monitors that look as disk space as reported by system utilities see a
steadilydropping amount of disk space.
I can post further information if you give me some pointers as to what to look for.
Thanks!
Greg Williamson
postgres(at)db11:5432=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
(1 row)
Says one of our sysads:
"These are single disk systems. single non-raid sata. Using xfs as the filesystem like the rest of the dbs."
The gritty details of before and after as seen through a couple of commands.
BEFORE
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 67G 60G 7.4G 89% /
none 7.9G 180K 7.9G 1% /dev
none 7.9G 0 7.9G 0% /dev/shm
none 7.9G 56K 7.9G 1% /var/run
none 7.9G 0 7.9G 0% /var/lock
none 7.9G 0 7.9G 0% /lib/init/rw
/dev/sda1 184M 31M 144M 18% /boot
AFTER:
postgres@db11:~$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 67G 38G 30G 57% /
none 7.9G 180K 7.9G 1% /dev
none 7.9G 0 7.9G 0% /dev/shm
none 7.9G 56K 7.9G 1% /var/run
none 7.9G 0 7.9G 0% /var/lock
none 7.9G 0 7.9G 0% /lib/init/rw
/dev/sda1 184M 31M 144M 18% /boot
BEFORE:
postgres@db11:~$ du -h /var/lib/postgresql/9.1/main
480K/var/lib/postgresql/9.1/main/global
28K/var/lib/postgresql/9.1/main/pg_xlog/archive_status
8.0G/var/lib/postgresql/9.1/main/pg_xlog
47M/var/lib/postgresql/9.1/main/pg_clog
8.0K/var/lib/postgresql/9.1/main/pg_notify
0/var/lib/postgresql/9.1/main/pg_serial
16K/var/lib/postgresql/9.1/main/pg_subtrans
0/var/lib/postgresql/9.1/main/pg_twophase
8.0K/var/lib/postgresql/9.1/main/pg_multixact/members
8.0K/var/lib/postgresql/9.1/main/pg_multixact/offsets
16K/var/lib/postgresql/9.1/main/pg_multixact
5.9M/var/lib/postgresql/9.1/main/base/1
5.9M/var/lib/postgresql/9.1/main/base/11938
5.9M/var/lib/postgresql/9.1/main/base/11946
5.0G/var/lib/postgresql/9.1/main/base/16387
5.9M/var/lib/postgresql/9.1/main/base/16392
5.9M/var/lib/postgresql/9.1/main/base/16402
0/var/lib/postgresql/9.1/main/base/pgsql_tmp
520M/var/lib/postgresql/9.1/main/base/16424
5.9M/var/lib/postgresql/9.1/main/base/16449
1.4G/var/lib/postgresql/9.1/main/base/1418400
6.1M/var/lib/postgresql/9.1/main/base/2047839
6.8G/var/lib/postgresql/9.1/main/base
0/var/lib/postgresql/9.1/main/pg_tblspc
216K/var/lib/postgresql/9.1/main/pg_stat_tmp
15G/var/lib/postgresql/9.1/main
AFTER:
postgres@db11:~$ du -h /var/lib/postgresql/9.1/main
480K/var/lib/postgresql/9.1/main/global
28K/var/lib/postgresql/9.1/main/pg_xlog/archive_status
8.0G/var/lib/postgresql/9.1/main/pg_xlog
47M/var/lib/postgresql/9.1/main/pg_clog
8.0K/var/lib/postgresql/9.1/main/pg_notify
0/var/lib/postgresql/9.1/main/pg_serial
64K/var/lib/postgresql/9.1/main/pg_subtrans
0/var/lib/postgresql/9.1/main/pg_twophase
8.0K/var/lib/postgresql/9.1/main/pg_multixact/members
8.0K/var/lib/postgresql/9.1/main/pg_multixact/offsets
16K/var/lib/postgresql/9.1/main/pg_multixact
5.9M/var/lib/postgresql/9.1/main/base/1
5.9M/var/lib/postgresql/9.1/main/base/11938
5.9M/var/lib/postgresql/9.1/main/base/11946
5.0G/var/lib/postgresql/9.1/main/base/16387
5.9M/var/lib/postgresql/9.1/main/base/16392
5.9M/var/lib/postgresql/9.1/main/base/16402
0/var/lib/postgresql/9.1/main/base/pgsql_tmp
520M/var/lib/postgresql/9.1/main/base/16424
5.9M/var/lib/postgresql/9.1/main/base/16449
1.4G/var/lib/postgresql/9.1/main/base/1418400
6.1M/var/lib/postgresql/9.1/main/base/2047839
316M/var/lib/postgresql/9.1/main/base/2297189
7.1G/var/lib/postgresql/9.1/main/base
0/var/lib/postgresql/9.1/main/pg_tblspc
328K/var/lib/postgresql/9.1/main/pg_stat_tmp
BEFORE:
$ sudo lsof -p 31356 -a | more
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
postgres 31356 postgres cwd DIR 8,3 4096 269834294 /var/lib/postgresql/9.1/main
postgres 31356 postgres rtd DIR 8,3 4096 128 /
postgres 31356 postgres txt REG 8,3 5430480 37149754 /usr/lib/postgresql/9.1/bin/postgres
postgres 31356 postgres mem REG 8,3 51712 772341 /lib/libnss_files-2.11.1.so
postgres 31356 postgres DEL REG 0,4 819200 /SYSV0052e2c1
postgres 31356 postgres mem REG 8,3 14344 2282 /lib/libgpg-error.so.0.4.0
postgres 31356 postgres mem REG 8,3 491000 1699 /lib/libgcrypt.so.11.5.2
postgres 31356 postgres mem REG 8,3 67896 268818565 /usr/lib/libtasn1.so.3.1.7
postgres 31356 postgres DEL REG 8,3 273434018 /usr/lib/libgnutls.so.26.14.12
postgres 31356 postgres mem REG 8,3 105232 268940921 /usr/lib/libsasl2.so.2.0.23
postgres 31356 postgres DEL REG 8,3 268574412 /usr/lib/liblber-2.4.so.2.5.4
postgres 31356 postgres mem REG 8,3 135745 772336 /lib/libpthread-2.11.1.so
postgres 31356 postgres mem REG 8,3 93000 772321 /lib/libresolv-2.11.1.so
postgres 31356 postgres mem REG 8,3 10224 2300 /lib/libkeyutils-1.2.so
postgres 31356 postgres mem REG 8,3 31168 268572617 /usr/lib/libkrb5support.so.0.1
postgres 31356 postgres mem REG 8,3 154048 268807360 /usr/lib/libk5crypto.so.3.1
postgres 31356 postgres mem REG 8,3 43296 614766 /lib/libcrypt-2.11.1.so
postgres 31356 postgres mem REG 8,3 92752 79191 /lib/libz.so.1.2.3.3
postgres 31356 postgres mem REG 8,3 1572232 772338 /lib/libc-2.11.1.so
postgres 31356 postgres DEL REG 8,3 268574415 /usr/lib/libldap_r-2.4.so.2.5.4
postgres 31356 postgres mem REG 8,3 534832 60426 /lib/libm-2.11.1.so
postgres 31356 postgres mem REG 8,3 14696 772342 /lib/libdl-2.11.1.so
postgres 31356 postgres mem REG 8,3 213784 268572618 /usr/lib/libgssapi_krb5.so.2.2
postgres 31356 postgres mem REG 8,3 14584 495 /lib/libcom_err.so.2.1
postgres 31356 postgres mem REG 8,3 803192 268807473 /usr/lib/libkrb5.so.3.3
postgres 31356 postgres mem REG 8,3 1626400 772 /lib/libcrypto.so.0.9.8
postgres 31356 postgres mem REG 8,3 333904 2849994 /lib/libssl.so.0.9.8
postgres 31356 postgres mem REG 8,3 51776 1263 /lib/libpam.so.0.82.2
postgres 31356 postgres mem REG 8,3 1372344 268818714 /usr/lib/libxml2.so.2.7.6
postgres 31356 postgres mem REG 8,3 136936 60428 /lib/ld-2.11.1.so
postgres 31356 postgres mem REG 8,3 2459 402716943 /usr/lib/locale/en_US/LC_TIME
postgres 31356 postgres mem REG 8,3 59 402716942 /usr/lib/locale/en_US/LC_NUMERIC
postgres 31356 postgres mem REG 8,3 291 402716945 /usr/lib/locale/en_US/LC_MONETARY
postgres 31356 postgres mem REG 8,3 62 4366
/usr/lib/locale/en_US/LC_MESSAGES/SYS_LC_MESSAGES
postgres 31356 postgres mem REG 8,3 221612 402716941 /usr/lib/locale/en_US/LC_CTYPE
postgres 31356 postgres mem REG 8,3 19459 402716944 /usr/lib/locale/en_US/LC_COLLATE
postgres 31356 postgres 0r CHR 1,3 0t0 1344 /dev/null
postgres 31356 postgres 1u CHR 136,1 0t0 4 /dev/pts/1 (deleted)
postgres 31356 postgres 2u CHR 136,1 0t0 4 /dev/pts/1 (deleted)
postgres 31356 postgres 3u IPv4 392650125 0t0 TCP *:postgresql (LISTEN)
postgres 31356 postgres 4u IPv6 392650126 0t0 TCP *:postgresql (LISTEN)
postgres 31356 postgres 5u unix 0xffff88005142e100 0t0 392650128 /var/run/postgresql/.s.PGSQL.5432
postgres 31356 postgres 6u IPv6 392650136 0t0 UDP localhost:21771->localhost:21771
postgres 31356 postgres 7u unix 0xffff88005142db00 0t0 392679151 socket
AFTER:
$ sudo lsof -p 4382 -a
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
postgres 4382 postgres cwd DIR 8,3 4096 269834294 /var/lib/postgresql/9.1/main
postgres 4382 postgres rtd DIR 8,3 4096 128 /
postgres 4382 postgres txt REG 8,3 5430480 37149754 /usr/lib/postgresql/9.1/bin/postgres
postgres 4382 postgres mem REG 8,3 51712 772341 /lib/libnss_files-2.11.1.so
postgres 4382 postgres DEL REG 0,4 884736 /SYSV0052e2c1
postgres 4382 postgres mem REG 8,3 14344 2282 /lib/libgpg-error.so.0.4.0
postgres 4382 postgres mem REG 8,3 491000 1699 /lib/libgcrypt.so.11.5.2
postgres 4382 postgres mem REG 8,3 67896 268818565 /usr/lib/libtasn1.so.3.1.7
postgres 4382 postgres mem REG 8,3 663816 268573983 /usr/lib/libgnutls.so.26.14.12
postgres 4382 postgres mem REG 8,3 105232 268940921 /usr/lib/libsasl2.so.2.0.23
postgres 4382 postgres mem REG 8,3 55568 268573988 /usr/lib/liblber-2.4.so.2.5.4
postgres 4382 postgres mem REG 8,3 135745 772336 /lib/libpthread-2.11.1.so
postgres 4382 postgres mem REG 8,3 93000 772321 /lib/libresolv-2.11.1.so
postgres 4382 postgres mem REG 8,3 10224 2300 /lib/libkeyutils-1.2.so
postgres 4382 postgres mem REG 8,3 31168 268572617 /usr/lib/libkrb5support.so.0.1
postgres 4382 postgres mem REG 8,3 154048 268807360 /usr/lib/libk5crypto.so.3.1
postgres 4382 postgres mem REG 8,3 43296 614766 /lib/libcrypt-2.11.1.so
postgres 4382 postgres mem REG 8,3 92752 79191 /lib/libz.so.1.2.3.3
postgres 4382 postgres mem REG 8,3 1572232 772338 /lib/libc-2.11.1.so
postgres 4382 postgres mem REG 8,3 302464 268573990 /usr/lib/libldap_r-2.4.so.2.5.4
postgres 4382 postgres mem REG 8,3 534832 60426 /lib/libm-2.11.1.so
postgres 4382 postgres mem REG 8,3 14696 772342 /lib/libdl-2.11.1.so
postgres 4382 postgres mem REG 8,3 213784 268572618 /usr/lib/libgssapi_krb5.so.2.2
postgres 4382 postgres mem REG 8,3 14584 495 /lib/libcom_err.so.2.1
postgres 4382 postgres mem REG 8,3 803192 268807473 /usr/lib/libkrb5.so.3.3
postgres 4382 postgres mem REG 8,3 1626400 772 /lib/libcrypto.so.0.9.8
postgres 4382 postgres mem REG 8,3 333904 2849994 /lib/libssl.so.0.9.8
postgres 4382 postgres mem REG 8,3 51776 1263 /lib/libpam.so.0.82.2
postgres 4382 postgres mem REG 8,3 1372344 268818714 /usr/lib/libxml2.so.2.7.6
postgres 4382 postgres mem REG 8,3 136936 60428 /lib/ld-2.11.1.so
postgres 4382 postgres mem REG 8,3 2459 402716943 /usr/lib/locale/en_US/LC_TIME
postgres 4382 postgres mem REG 8,3 59 402716942 /usr/lib/locale/en_US/LC_NUMERIC
postgres 4382 postgres mem REG 8,3 291 402716945 /usr/lib/locale/en_US/LC_MONETARY
postgres 4382 postgres mem REG 8,3 62 4366
/usr/lib/locale/en_US/LC_MESSAGES/SYS_LC_MESSAGES
postgres 4382 postgres mem REG 8,3 221612 402716941 /usr/lib/locale/en_US/LC_CTYPE
postgres 4382 postgres mem REG 8,3 19459 402716944 /usr/lib/locale/en_US/LC_COLLATE
postgres 4382 postgres 0r CHR 1,3 0t0 1344 /dev/null
postgres 4382 postgres 1u CHR 136,2 0t0 5 /dev/pts/2
postgres 4382 postgres 2u CHR 136,2 0t0 5 /dev/pts/2
postgres 4382 postgres 3u IPv4 502242690 0t0 TCP *:postgresql (LISTEN)
postgres 4382 postgres 4u IPv6 502242691 0t0 TCP *:postgresql (LISTEN)
postgres 4382 postgres 5u unix 0xffff88042b7f8600 0t0 502242693 /var/run/postgresql/.s.PGSQL.5432
postgres 4382 postgres 6u IPv6 502242701 0t0 UDP localhost:18554->localhost:18554
postgres 4382 postgres 7u unix 0xffff88042a250000 0t0 502242789 socket