On a postgres 9.1.3 system we are seeing repeated problems with vanishng disk space. The problem is resolved by restarting the postgres cluster. This cluster is replicated to another server (which does not show the same problem). It has several databases -- one very low activity currently ( all tables unlogged), one with lots of inserts and some reads (all logged), and one database which 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 conjured up. Our monitors show that from inside postgres the database size regularly rises as the transient database is created and drops again when the database is dropped. But monitors that look as disk space as reported by system utilities see a steadily dropping 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
