Deleted files still open long after droping a database

[Vincent de Phily]

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]

Post by Vincent de Phily
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) ?

I solved that particular issue by disabling and reenabling autovacuum. So I
finally got my disk space back, but I'm still interested in an answer to those
questions.

Post by Vincent de Phily
3) Is there a generally cleaner way to do all this ?

[Tom Lane]

Post by Vincent de Phily
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.

I believe this is taken care of in recent PG releases. 9.1.2 is, um,
not recent.

regards, tom lane

[Kevin Grittner]

Post by Vincent de Phily
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 ?

I suspect that the other backends needed to flush a page from cache
to free space for a page they wanted to use, and the file was not
closed in case the same backend needed to flush other pages from
the same file.

Perhaps we should arrange for a DROP DATABASE command to somehow
signal all backends to close files from that backend?  Or they
should periodically check?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[Tom Lane]

Post by Kevin Grittner
Perhaps we should arrange for a DROP DATABASE command to somehow
signal all backends to close files from that backend?

See commit ff3f9c8de, which was back-patched into 9.1.x as of 9.1.7.

Unfortunately, the complainant is running 9.1.2.

regards, tom lane

[Vincent de Phily]

Post by Tom Lane

Post by Kevin Grittner
Perhaps we should arrange for a DROP DATABASE command to somehow
signal all backends to close files from that backend?

See commit ff3f9c8de, which was back-patched into 9.1.x as of 9.1.7.
Unfortunately, the complainant is running 9.1.2.

Thanks for the detailed info, great to see it's already fixed. As it happens,
we're in the process of moving to new servers, where we'll strive to get a
better update process going.