我使用PostgreSQL时出现错误“无法写入块...临时文件上没有剩余空间...”

Question

我正在运行一个非常大的查询，向表中插入了很多行，几乎有800万行被分成一些较小的查询，但在某个时刻出现了错误：“我遇到了错误“无法写入块……临时文件上没有剩余空间…。”使用PostgreSQL”。我不知道是否需要在每个查询之后删除临时文件以及如何操作，或者它是否与其他问题相关。

谢谢

- jacr1614

---

你的查询是什么样子的？在运行查询之前，设备上还剩多少空间？ - frlan

这是一个插入查询，其中包含一些values()...每个查询几乎都有10万个values子句... - jacr1614

请问表格是如何定义的，您是在哪个表格中进行插入操作？请更新问题。 - frlan

你需要修复“out of space”错误，方法和处理其他类型的空间错误一样吗？需要找到更多的空间吗？还是你认为系统状态和查询性质应该有足够的空间，但实际上使用的空间远多于预期的情况，导致出现了错误？ - IMSoP

更具体地说，此查询是否占用了大量空间（即在运行时，可用空间急剧下降），还是整个数据库（永久性地）占用了大量空间？ - IMSoP

好的..，我需要插入大量数据，几乎有800万行。因此，PHP脚本将以100,000行为一组执行插入。该脚本成功插入了约7,500,000行，然后显示上述错误。 - jacr1614

Answer 1

好的。由于还有一些事实缺失，尝试回答可能澄清这个问题：

看起来您的磁盘空间不足，很可能是因为磁盘上没有足够的空间。例如，在Linux / Unix上运行 df -h 检查。

为了向您展示如何发生这种情况： 假设有一个带有3个整数的表，仅数据就将占用约12个字节。您需要为行管理等添加一些开销。在另一个答案中，Erwin提到了23Byte，并链接到手册以获取更多信息。还可能需要在行之间添加一些填充。因此进行一些计算：

即使只有3个整数，我们也会最终达到每行约40个字节。请记住，您想插入800万行，这将总计达到320,000,000字节或〜300MB（仅针对我们的3个整数示例，非常粗略地计算）。

现在假设，您的这个表上有几个索引，这些索引也会在插入过程中增长。还有另一个方面可能是表和索引上的膨胀，可以使用vacuum将其清除。

那么解决方案是什么：

1. 为您的数据库提供更多磁盘空间
2. 将插入操作分成几个部分，并确保在它们之间运行vacuum

- frlan

---

如果您仅运行插入操作，则在它们之间运行vacuum不会改变任何内容。vacuum只会清除已被删除或更新的过时行。insert永远不会创建符合清理条件的行。 - user330315

我们现在不知道查询集的真实情况，所以我认为这样做不会有坏处。 - frlan

我正在测试使用System('df -h >> disk_usage.log')来了解更多信息。但我不确定在这种情况下是否适用于vaccum。 - jacr1614

+1 用于细粒度插入。OP 可以使用它们来估计他们需要提供多少空间来完成完整的插入。然而，VACUUM 永远不会将存储返回给操作系统（VACUUM FULL 会，但它需要大量的开销空间来工作，并且除非有很多死行，否则它不会有所帮助）。 - Daniel Lyons

我遇到了同样的错误，我的磁盘大约有450GB的空间，尽管表只有几MB... - Eduardo

Answer 2

插入数据或索引（创建）始终需要temp_tablespaces，它确定临时表和索引的位置，以及用于排序大数据集等目的的临时文件。根据您的错误，意味着您的temp_tablespace位置的磁盘空间不足。

要解决此问题，您可以使用以下两种方法：
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;

然后断开会话并重新连接。

- BongSey

Answer 3

错误非常明显。您正在运行一个大查询，但是您没有足够的磁盘空间来运行它。如果postgresql安装在/opt...请检查是否有足够的空间来运行查询。如果没有，请将输出限制以确认您正在获得预期的输出，然后继续运行查询并将输出写入文件。

- Brayoni