Deleted files still open long after droping a database - Mailing list pgsql-general

Hi List,

I have droped a database a few hours ago to reclaim some badly-needed space,
but that disk space has not been freed yet. Investigating further, there are
lots of deleted but open files that seem to correspond to the droped db, all
open by some postgres process.

A lot of process were db connections to databases other than the droped one
(I'm not using connection pooling). The other process is the autovacuum
launcher. I have reset the various connection processes, but I hesitate to
kill the autovacuum process.

I'm not sure wether that's relevant, but I moved the database to another
server using slony1-2.2.2 before droping it on the old server. There were
already no more slon processes running when I droped the db.


1) why does this happen at all (keeping files from a droped databse open, even
   by processes that never connected to that specific db) ?

2) Can the autovacuum launcher process be safely killed (there are no long-
   running vacuum queries) ?

3) Is there a generally cleaner way to do all this ?





PostgreSQL 9.1.2 on debian linux squeeze


$ ps aux|grep autovacuum
postgres  5025  0.0  0.2 122992 39124 ?        Ss   Jan27  14:28 postgres:
autovacuum launcher process
$ sudo lsof +aL1 /path/to/pgsql/
postgres 5025 postgres    9u   REG    8,7    4308992     0 14943966
/path/to/pgsql/base/35355/5979724 (deleted)
postgres 5025 postgres   24u   REG    8,7       8192     0 14948153
/path/to/pgsql/base/35355/35460 (deleted)
postgres 5025 postgres   35u   REG    8,7     286720     0 14947766
/path/to/pgsql/base/35355/5979918 (deleted)
postgres 5025 postgres   63u   REG    8,7 1073741824     0 14947390
/path/to/pgsql/base/35355/5979501.1 (deleted)
postgres 5025 postgres   73u   REG    8,7     319488     0 14947627
/path/to/pgsql/base/35355/5979758 (deleted)
postgres 5025 postgres   79u   REG    8,7 1073741824     0 14947428
/path/to/pgsql/base/35355/5979507 (deleted)
postgres 5025 postgres   84u   REG    8,7 1073741824     0 14947317
/path/to/pgsql/base/35355/5979501 (deleted)
postgres 5025 postgres  113u   REG    8,7       8192     0 14948150
/path/to/pgsql/base/35355/35398 (deleted)
postgres 5025 postgres  131u   REG    8,7    2326528     0 14943967
/path/to/pgsql/base/35355/5979727 (deleted)
postgres 5025 postgres  141u   REG    8,7      16384     0 14947008
/path/to/pgsql/base/35355/5979746 (deleted)
postgres 5025 postgres  142u   REG    8,7   28991488     0 14944726
/path/to/pgsql/base/35355/5979728 (deleted)
postgres 5025 postgres  146u   REG    8,7 1073741824     0 14947442
/path/to/pgsql/base/35355/5979508 (deleted)
postgres 5025 postgres  150u   REG    8,7   82337792     0 14947615
/path/to/pgsql/base/35355/5979710 (deleted)
postgres 5025 postgres  169u   REG    8,7   37855232     0 14944962
/path/to/pgsql/base/35355/5979729 (deleted)
postgres 5025 postgres  178u   REG    8,7       8192     0 14948156
/path/to/pgsql/base/35355/35723 (deleted)
postgres 5025 postgres  179u   REG    8,7 1073741824     0 14947429
/path/to/pgsql/base/35355/5979509 (deleted)
postgres 5025 postgres  181u   REG    8,7     450560     0 14947764
/path/to/pgsql/base/35355/5979914 (deleted)
postgres 5025 postgres  185u   REG    8,7  959111168     0 14946595
/path/to/pgsql/base/35355/5979508.1 (deleted)
postgres 5025 postgres  200u   REG    8,7  224239616     0 14947343
/path/to/pgsql/base/35355/5979507.1 (deleted)
postgres 5025 postgres  204u   REG    8,7   74473472     0 14947620
/path/to/pgsql/base/35355/5979713 (deleted)
postgres 5025 postgres  208u   REG    8,7  106536960     0 14947623
/path/to/pgsql/base/35355/5979719 (deleted)
postgres 5025 postgres  210u   REG    8,7  105611264     0 14947619
/path/to/pgsql/base/35355/5979712 (deleted)
postgres 5025 postgres  212u   REG    8,7  446078976     0 14947611
/path/to/pgsql/base/35355/5979704 (deleted)
postgres 5025 postgres  216u   REG    8,7   75988992     0 14947469
/path/to/pgsql/base/35355/5979509.1 (deleted)
postgres 5025 postgres  222u   REG    8,7  513097728     0 14947263
/path/to/pgsql/base/35355/5979501.2 (deleted)
postgres 5025 postgres  239u   REG    8,7  119832576     0 14947621
/path/to/pgsql/base/35355/5979716 (deleted)


--
Vincent de Phily