删除 postgresql 中的临时文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39198380/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
delete temporary files in postgresql
提问by let_there_be_light
I have a huge database of about 800GB. When I tried to run a query which groups certain variables and aggregates the result, it was stopping after running for a couple of hours. Postgres was throwing a message that disk space is full. After looking at the statistics I realized that the dB has about 400GB of temporary files. I believe these temp files where created while I was running the query. My question is how do I delete these temp files. Also, how do I avoid such problems - use cursors or for-loops to not process all the data at once? Thanks.
我有一个大约 800GB 的巨大数据库。当我尝试运行对某些变量进行分组并聚合结果的查询时，它在运行几个小时后停止了。Postgres 正在抛出一条消息，指出磁盘空间已满。在查看统计数据后，我意识到 dB 有大约 400GB 的临时文件。我相信这些临时文件是在我运行查询时创建的。我的问题是如何删除这些临时文件。另外，如何避免此类问题 - 使用游标或 for 循环不一次处理所有数据？谢谢。
I'm using Postgres 9.2
我正在使用 Postgres 9.2
回答by Laurenz Albe
The temporary files that get created in base/pgsql_tmpduring query execution will get deleted when the query is done. You should not delete them by hand.
These files have nothing to do with temporary tables, they are use to store data for large hash or sort operations that would not fit in work_mem.
base/pgsql_tmp在查询执行期间创建的临时文件将在查询完成后被删除。您不应该手动删除它们。
这些文件与临时表无关，它们用于存储不适合work_mem.
Make sure that the query is finished or canceled, try running CHECKPOINTtwice in a row and see if the files are still there. If yes, that's a bug; did the PostgreSQL server crash when it ran out of disk space?
确保查询完成或取消，尝试连续运行CHECKPOINT两次，看看文件是否仍然存在。如果是，那是一个错误；PostgreSQL 服务器是否在磁盘空间不足时崩溃？
If you really have old files in base/pgsql_tmpthat do not get deleted automatically, I think it is safe to delete them manually. But I'd file a bug with PostgreSQL in that case.
如果您确实有旧文件base/pgsql_tmp不会被自动删除，我认为手动删除它们是安全的。但在这种情况下，我会向 PostgreSQL 提交一个错误。
There is no way to avoid large temporary files if your execution plan needs to sort large result sets or needs to create large hashes. Cursors won't help you there. I guess that with for-loopsyou mean moving processing from the database to application code – doing that is usually a mistake and will only move the problem from the database to another place where processing is less efficient.
如果您的执行计划需要对大型结果集进行排序或需要创建大型散列，则无法避免大型临时文件。游标不会帮助你那里。我想，对于for 循环，你的意思是将处理从数据库转移到应用程序代码——这样做通常是一个错误，只会将问题从数据库转移到另一个处理效率较低的地方。
Change your query so that it doesn't have to sort or hash large result sets (check with EXPLAIN). I know that does not sound very helpful, but there's no better way. You'll probably have to do that anyway, or is a runtime of several hours acceptable for you?
更改您的查询，使其不必对大型结果集进行排序或散列（检查EXPLAIN）。我知道这听起来不是很有帮助，但没有更好的方法。无论如何，您可能必须这样做，或者您可以接受几个小时的运行时间？
