PostgreSQL数据库大小增加 [英] PostgreSQL database size increasing
问题描述
我有一个奇怪的问题。我的postgresql（8.3）的大小正在增加。因此，我进行了转储，然后清理了数据库，然后重新导入了转储。数据库大小减少了大约50％。
I have a strange problem. The size of my postgresql (8.3) is increasing. So I made a dump and then cleaned up the database and then re-imported the dump. The database size was reduced by roughly 50%.
一些信息：
（1）AUTOVACUUM和REINDEX在后台定期运行。
（2）数据库编码为ASCII。
（3）数据库位置：/ database / pgsql / data
（4）系统：Suse-Ent。 10。
Some infomation: (1) AUTOVACUUM and REINDEX are running regularly in background. (2) Database encoding is ASCII. (3) Database location: /database/pgsql/data (4) System: Suse-Ent. 10.
任何提示都会被赞赏
推荐答案
如果死元组超出了 max_fsm_pages 所能解释的范围，常规VACUUM将无法释放所有内容。最终结果是，随着死空间的不断累积，数据库将随着时间的推移变得越来越大。运行VACUUM FULL应该可以解决此问题。不幸的是，在大型数据库上可能会花费很长时间。
If the dead tuples have stacked up beyond what can be accounted for in max_fsm_pages, a regular VACUUM will not be able to free everything. The end result is that the database will grow larger and larger over time as dead space continues to accumulate. Running a VACUUM FULL should fix this problem. Unfortunately it can take a very long time on a large database.
如果您经常遇到此问题，则您需要更频繁地进行清理（autovacuum可在此处提供帮助）或增加 max_fsm_pages 设置。运行VACUUM VERBOSE时，它将告诉您释放了多少页面，并在超出 max_fsm_pages 时发出警告，这可以帮助您确定该值是多少。有关更多信息，请参见手册。 http：//www.postgresql .org / docs / 8.3 / static / runtime-config-resource.html＃RUNTIME-CONFIG-RESOURCE-FSM
If you're running into this problem frequently, you either need to vacuum more often (autovacuum can help here) or increase the max_fsm_pages setting. When running VACUUM VERBOSE it will tell you how many pages were freed and give you a warning if max_fsm_pages was exceeded, this can help you determine what this value should be. See the manual for more information. http://www.postgresql.org/docs/8.3/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM
幸运的是，8.4的可见度图解决了这个问题。像往常一样，戴斯佩斯（Despesz）在这个问题上有个很棒的故事： http://www.depesz.com/index.php/2008/12/08/waiting-for-84-visibility-maps/
Fortunately, 8.4's visibility map resolves this issue. Despesz has a great story on the subject as usual: http://www.depesz.com/index.php/2008/12/08/waiting-for-84-visibility-maps/
这篇关于PostgreSQL数据库大小增加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
