CODEWITHSUNDEEP
postgresqlstoragedatabase-administration
haitham
haitham

Reputation: 61

PostgreSQL error: could not extend file no space left on device

I'm running a query that duplicates a very large table (92 million rows) on PostgreSQL. After a 3 iterations I got this error message:

Screenshot of the error message

The query was:

CREATE TABLE table_name
AS SELECT * FROM big_table

The issue isn't due to lack of space in the database cluster: at 0.3% of max possible storage at the time of running the query, table size is about 0.01% of max storage including all replicas. I also checked temporary files and it's not that.

Upvotes: 6

Views: 22139

Answers (2)

Tms91
Tms91

Reputation: 4164

In case you are running into this issue on Ubuntu

Open postgresql logs

less /var/log/postgresql/...

and check in detail what happened when the query failed.

You should find something like

ERROR: Writing to file "base/pgsql_tmp/pgsql_tmp26940.121" failed: No space left on device

If this is the case, it could be an issue with the lack of free space in your postgres temporary data directory.

To solve it, just then follow these steps.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246483

You are definitely running out of file system resources.

Make sure you got the size right:

SELECT pg_table_size('big_table');

Don't forget that the files backing the new table are deleted after the error, so it is no surprise that you have lots of free space after the statement has failed.

One possibility is that you are not running out of disk space, but of free i-nodes. How to examine the free resources differs from file system to file system; for ext4 on Linux it would be

sudo dumpe2fs -h /dev/... | grep Free

Upvotes: 2

Related Questions