我使用 postgresql 收到错误“无法写入块.... 临时文件没有剩余空间...”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23227695/
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
I get an error "could not write block .... of temporary file no space left on device ..." using postgresql
提问by jacr1614
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.
我正在运行一个非常大的查询，在表中插入很多行，在一些较小的查询中几乎有 800 万行划分，但在某些时候出现错误：“我收到错误”无法写入块... . 临时文件在设备上没有剩余空间...“使用 postgresql”。我不知道是否需要在每次查询后删除临时文件以及如何删除，或者是否与其他问题有关。
Thank you
谢谢
采纳答案by frlan
OK. As there are still some facts missing, an attempt to answer to maybe clarify the issue:
好的。由于仍然缺少一些事实，因此尝试回答以澄清问题：
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 -hfor example.
看来您的磁盘空间不足。很可能是因为您的磁盘空间不足。例如，检查 Linux/Unix df -h。
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 answerErwin 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 个整数的表，单独的数据将占用大约 12 字节。您需要为行管理等添加一些开销。在另一个答案中，Erwin 提到了 23Byte 并链接到手册以获取更多信息。也可能需要一些行之间的填充等。所以做一点数学：
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).
即使是 3 个整数，我们最终也会每行大约 40 字节。考虑到您想要插入 8,000,000，这将总计为 320,000,000Byte 或 ~ 300MB（仅适用于我们的 3 个整数示例，非常粗略）。
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.
现在给出，你在这个表上有几个索引，索引也会在插入过程中增长。另一个方面可能是表和索引膨胀，这些索引可能会被真空清除。
So what's the solution:
那么有什么解决办法：
- Provide more disk space to your database
- Split your inserts a little more and ensure, vacuum is running between them
- 为您的数据库提供更多磁盘空间
- 将您的插件分开一点，确保它们之间有真空
回答by BongSey
Inserting data or index(create) always needs temp_tablespaces, which determines the placement of temporary tables and indexes, as well as temporary files that are used for purposes such as sorting large data sets.according to your error, it meant that your temp_tablespace location is not enough for disk space .
插入数据或索引（创建）总是需要 temp_tablespaces，它决定了临时表和索引的位置，以及用于排序大数据集等目的的临时文件。根据您的错误，这意味着您的 temp_tablespace 位置是磁盘空间不够。
To resolve this problem you may need these two ways:
1.Re-claim the space of your temp_tablespace located to, default /PG_DATA/base/pgsql_tmp
2. If your temp_tablespace space still not enough for temp storing you can create the other temp tablespace for that database:
要解决此问题，您可能需要以下两种方法： 1.
重新声明您的 temp_tablespace 所在的空间，默认为 /PG_DATA/base/pgsql_tmp
2. 如果您的 temp_tablespace 空间仍然不足以用于临时存储，您可以创建另一个临时表空间对于该数据库：
create tablespace tmp_YOURS location '[your enough space location';
alter database yourDB set temp_tablespaces = tmp_YOURS ;
GRANT ALL ON TABLESPACE tmp_YOURS to USER_OF_DB;
then disconnect the session and reconnect it.
然后断开会话并重新连接。
回答by Brayoni
The error is quite self-explanatory. You are running a big query yet you do not have enough disk space to do so. If postgresql is installed in /opt...check if you have enough space to run the query. If not LIMIT the output to confirm you are getting the expected output and then proceed to run the query and write the output to a file.
该错误是不言自明的。您正在运行一个大查询，但没有足够的磁盘空间来执行此操作。如果 postgresql 安装在 /opt...检查您是否有足够的空间来运行查询。如果不是 LIMIT 输出以确认您获得了预期的输出，然后继续运行查询并将输出写入文件。
