PostgreSQL：DELETE 不会释放内存
我在驱动器空间有限的嵌入式系统上使用 PostgreSQL。现在数据库驱动器已满。当我删除数据时，它似乎没有释放任何空间。我尝试用 VACUUM FULL 进行清理，但这需要空间。删除最后一个剩余索引也是如此。
关于如何释放空间而不随机删除内容有什么想法吗？我可以承受丢失以前的一些数据，但我似乎无法真正做到这一点，因为没有足够的空间来 VACUUM FULL。
I use PostgreSQL on an embedded system with limited drive space. Now the DB-drive is full. When I delete data, it doesn't seem to free up any space. I tried to VACUUM FULL, but that requires space. So does deleting the last remaining index.
Any ideas on how to free up space without randomly deleting stuff? I can afford to lose some of the data from back when, but I can't seem to actually do it, since there isn't enough space to VACUUM FULL.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
PostgreSQL使用MVCC模型，这意味着删除的记录将其空间标记为空闲（在删除它们的事务已提交之后），但它仍然由表保留。在
PostgreSQL 9.0之前，VACUUM FULL用于在表内移动数据，而不需要额外的空间。在
PostgreSQL 9.0中，VACUUM FULL的行为发生了变化，现在它需要额外的空间来存储表的完整副本。您可以尝试从表中删除索引并从最小的索引开始将它们一一清理。
PostgreSQLusesMVCCmodel which means that deleted records mark their space as free (after the transaction which deleted them had been committed) but it is still reserved by the table.Prior to
PostgreSQL 9.0,VACUUM FULLused to move the data inside the table without need for additional space.In
PostgreSQL 9.0, behavior ofVACUUM FULLhad changed and now it requires additional space for the full copy of the table.You may try to drop the indexes from the tables and vacuum them one by one, starting from the least one.
此时最简单的答案是将数据库转储到不同的驱动器/计算机（例如，如果您有多个数据库，则使用 pg_dump 或 pg_dumpall ，并且请记住诸如需要特殊备份/恢复过程的大型对象之类的事情），然后删除并重新创建数据库。
如果还剩下一点点空间，您可以尝试
vacuum fullsmallesttable，这可能能够完成并释放一些空间来清理下一个最小的表，依此类推。如果您最终完全填满驱动器，数据库服务器可能会拒绝启动，并且您将无法执行其中任何一项操作。在这种情况下，您可以将整个数据目录移动到另一台具有相同CPU架构和更多磁盘空间的计算机，然后在那里启动postgresql来执行vacuum。
The easiest answer at this point would be to dump the database to a different drive/computer (for instance, using
pg_dump, orpg_dumpallif you have more than one db, and keeping in mind things like Large Objects that need special backup/restore processes) then drop and recreate the database.If there's a tiny bit of space left, you might try
vacuum full smallesttable, which might be able to finish and free up some space to vacuum the next smallest table, and so on.If you end up filling the drive completely, the database server will probably refuse to start and you won't be able to do either of those. In that case, you could move the entire data directory to another computer with the same CPU architecture and more disk space, then start postgresql there to perform the vacuum.
在某些情况下，VACUUM（未满）可以回收一些磁盘空间。 （我认为它会返回对操作系统完全无效的页面。）这可能会释放足够的空间以开始 VACUUM FULL。但让一张表增长到超过磁盘可用空间量并不是一个好主意。
In certain situations VACUUM (not full) can reclaim some disk space. (I think it will return pages that are totally dead to the OS.) That might free up enough space to begin with the VACUUM FULL. But it's not a good idea to let one table grow to more than the amount of disk free space.