Thread: Deleted files still open long after droping a database
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
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
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
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
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
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