我收到错误“无法写入块....临时文件没有剩余空间..."使用 PostgreSQL [英] I get an error "could not write block .... of temporary file no space left on device ..." using postgresql
问题描述
我正在运行一个非常大的查询，在表中插入了很多行，在一些较小的查询中几乎有 800 万行分开，但在某些时候出现错误:我收到错误"无法写入块.... 临时文件在设备上没有剩余空间...使用 postgresql".我不知道是否需要在每次查询后删除临时文件以及如何执行此操作，或者是否与其他问题有关.
I'm running a really big query, that insert a lot of rows in table, almost 8 million of rows divide in some smaller querys, but in some moment appear that error : "I get an error "could not write block .... of temporary file no space left on device ..." using postgresql". I don't know if i need to delete temporary files after each query and how I can to do that, or if it is related with another issue.
谢谢
推荐答案
好的.由于仍然缺少一些事实，因此尝试回答以澄清问题:
OK. As there are still some facts missing, an attempt to answer to maybe clarify the issue:
您的磁盘空间似乎不足.很可能是因为您的磁盘空间不足.例如，检查 Linux/Unix df -h.
It appears that you are running out of disk space. Most likely because you don't have enough space on your disk. Check on a Linux/Unix df -h for example.
向您展示这是如何发生的:有一个可能包含 3 个整数的表，单独的数据将占用大约 12 字节.您需要为行管理等添加一些开销.在 another answer 上，Erwin 提到了 23Byte 并链接到手册有关的更多信息.也可能需要一些行之间的填充等.所以做一点数学:
To show you, how this could happen: Having a table with maybe 3 integers the data alone will occupy about 12Byte. You need to add some overhead to it for row management etc. On another answer Erwin mentioned about 23Byte and linked to the manual for more information about. Also there might needs some padding betweens rows etc. So doing a little math:
即使是 3 个整数，我们最终也会每行大约 40 字节.考虑到您想要插入 8,000,000，这将总计为 320,000,000Byte 或 ~ 300MB(仅适用于我们的 3 个整数示例，非常粗略).
Even with a 3 integer we will end up at about 40 Byte per row. Having in mind you wanted to insert 8,000,000 this will sum up to 320,000,000Byte or ~ 300MB (for our 3 integer example only and very roughly).
现在，你在这个表上有几个索引，索引也会在插入过程中增长.另一个方面可能是表和索引膨胀，这些索引可能会被真空清除.
Now giving, you have a couple of indexes on this table, the indexes will also grow during the inserts. Also another aspect might could be bloat on the table and indexes which might can be cleared with a vacuum.
那么解决方案是什么:
- 为您的数据库提供更多磁盘空间
- 将您的插件分开一点，确保它们之间存在真空
这篇关于我收到错误“无法写入块....临时文件没有剩余空间..."使用 PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
