53100 database resource_error ai_generated true

psycopg2.OperationalError: ERROR: could not create temporary file: No space left on device HINT: You might need to increase the max_files_per_process limit.

ID: database/temp-file-limit-exceeded

Also available as: JSON · Markdown · 中文
80%Fix Rate
85%Confidence
1Evidence
2023-06-15First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
PostgreSQL 12 active — — —
PostgreSQL 13 active — — —
PostgreSQL 14 active — — —
PostgreSQL 15 active — — —
PostgreSQL 16 active — — —

Root Cause

PostgreSQL has exhausted either disk space in the temporary file directory or the kernel's file descriptor limit, preventing the creation of temporary files for query execution.

generic

中文

PostgreSQL 已耗尽了临时文件目录中的磁盘空间或内核的文件描述符限制，导致无法为查询执行创建临时文件。

Official Documentation

https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-FILES-PER-PROCESS

Workarounds

  1. 80% success Check disk usage with `df -h` and `df -i` on the partition containing pg_stat_tmp and base directory. Free space by removing old WAL files or archiving logs: `pg_archivecleanup /path/to/archive $(ls -t /path/to/archive | tail -1)` 
    Check disk usage with `df -h` and `df -i` on the partition containing pg_stat_tmp and base directory. Free space by removing old WAL files or archiving logs: `pg_archivecleanup /path/to/archive $(ls -t /path/to/archive | tail -1)`
  2. 85% success Add more disk space or mount a larger volume to the temporary directory. Then, restart PostgreSQL or set temp_tablespaces to a tablespace on a volume with sufficient space: `ALTER SYSTEM SET temp_tablespaces = 'fast_disk'; SELECT pg_reload_conf();` 
    Add more disk space or mount a larger volume to the temporary directory. Then, restart PostgreSQL or set temp_tablespaces to a tablespace on a volume with sufficient space: `ALTER SYSTEM SET temp_tablespaces = 'fast_disk'; SELECT pg_reload_conf();`
  3. 70% success If the issue is file descriptor limits, increase max_files_per_process in postgresql.conf and restart: `ALTER SYSTEM SET max_files_per_process = 2000;` then restart the server 
    If the issue is file descriptor limits, increase max_files_per_process in postgresql.conf and restart: `ALTER SYSTEM SET max_files_per_process = 2000;` then restart the server

中文步骤

  1. 使用 `df -h` 和 `df -i` 检查包含 pg_stat_tmp 和 base 目录的分区磁盘使用情况。通过删除旧的 WAL 文件或归档日志来释放空间：`pg_archivecleanup /path/to/archive $(ls -t /path/to/archive | tail -1)`
  2. 向临时目录添加更多磁盘空间或挂载更大的卷。然后重启 PostgreSQL，或将 temp_tablespaces 设置为空间充足的卷上的表空间：`ALTER SYSTEM SET temp_tablespaces = 'fast_disk'; SELECT pg_reload_conf();`
  3. 如果问题是文件描述符限制，在 postgresql.conf 中增加 max_files_per_process 并重启：`ALTER SYSTEM SET max_files_per_process = 2000;` 然后重启服务器

Dead Ends

Common approaches that don't work:

  1. Increase max_files_per_process in postgresql.conf without checking disk space 60% fail

    The error is usually caused by full disk, not file descriptor limit; raising max_files_per_process won't free disk space

  2. Delete random files from pg_tblspc thinking they are temporary 80% fail

    pg_tblspc contains symbolic links to tablespaces; deleting them can corrupt the database

  3. Restart PostgreSQL without clearing temp files 70% fail

    Restarting doesn't reclaim disk space; the underlying issue of full disk or exhausted inodes remains