Thread: Deleted files still open long after droping a database

Deleted files still open long after droping a database

From
Vincent de Phily
Date:
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


Re: Deleted files still open long after droping a database

From
Vincent de Phily
Date:
On Monday 17 February 2014 18:46:49 Vincent de Phily wrote:
> 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.


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


--
Vincent de Phily


Re: Deleted files still open long after droping a database

From
Tom Lane
Date:
Vincent de Phily <vincent.dephily@mobile-devices.fr> writes:
> 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


Re: Deleted files still open long after droping a database

From
Kevin Grittner
Date:
Vincent de Phily <vincent.dephily@mobile-devices.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


Re: Deleted files still open long after droping a database

From
Tom Lane
Date:
Kevin Grittner <kgrittn@ymail.com> writes:
> 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


Re: Deleted files still open long after droping a database

From
Vincent de Phily
Date:
On Monday 17 February 2014 21:14:35 Tom Lane wrote:
> Kevin Grittner <kgrittn@ymail.com> writes:
> > 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.

--
Vincent de Phily