Re: Deleted files still open long after droping a database

Vincent de Phily <vincent(dot)dephily(at)mobile-devices(dot)fr> wrote:

> 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