A query fills the disk with temporary files: could not write to pgsql_tmp
ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp32058.285": No space left on deviceShort answer
A big sort or hash spilled to temporary files in base/pgsql_tmp and the disk filled up. Log temp files to find the query, tune work_mem or the query, cap runaways with temp_file_limit, and free space on the data disk. A full data disk blocks work but does not corrupt data.
Fix it fast
1Find out which cause you have
See how big the temporary files are right now, and how much room the database itself takes. pg_ls_tmpdir needs a superuser or pg_monitor.
SELECT sum(size) AS temp_bytes FROM pg_ls_tmpdir(); SELECT pg_size_pretty(pg_database_size(current_database()));2Match what you see, then jump to its fix
- 1
pg_ls_tmpdir shows large files, and the error appears with one report or joinOne heavy query spills sorts or hashes to temp filesFix - 2
A single runaway query consumed the whole disk; temp_file_limit is -1Nothing limits how much temp space one process may useFix - 3
pg_ls_tmpdir is small, but the volume is still at 100 percentThe disk is full of real data, not only temp filesFix
- 1
What does the error look like?
ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp32058.285": No space left on deviceSQL state: 53100
Cause 1 of 3
One heavy query spills sorts or hashes to temp files
1Check
A query operation that exceeds work_mem writes to temporary disk files. Turn on logging for every temp file, run the query, then read the log lines with their size.
ALTER SYSTEM SET log_temp_files = 0;
SELECT pg_reload_conf();2Fix
Fix the query first (missing join condition, sorting millions of rows, a cross join). If the operation is legitimate, raise work_mem for that session only rather than globally.
SET work_mem = '256MB';3Verify
Run the query and check the temp file log lines get smaller or disappear, then set logging back to its default.
ALTER SYSTEM RESET log_temp_files;
SELECT pg_reload_conf();Confirmed by postgresql.org
Cause 2 of 3
Nothing limits how much temp space one process may use
1Check
temp_file_limit caps the disk a process can use for temporary files. The default of -1 means no limit. A transaction that tries to exceed it is canceled.
SHOW temp_file_limit;2Fix
Set a limit that leaves room for the rest of the system. Only superusers or roles with SET privilege can change it. It is checked per process, so size it with the number of parallel sessions in mind.
ALTER SYSTEM SET temp_file_limit = '20GB';
SELECT pg_reload_conf();3Verify
The setting shows the new value.
SHOW temp_file_limit;Confirmed by postgresql.org
Cause 3 of 3
The disk is full of real data, not only temp files
1Check
The manual calls keeping the disk from filling up the most important monitoring task. Find the largest tables including indexes and TOAST.
SELECT relname, pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;2Fix
Free space or grow the volume. A full data disk stops useful work but does not corrupt data; a full disk holding WAL files can cause a server panic and shutdown, so act before it is completely full.
-- remove data you no longer need, then confirm the size
SELECT pg_size_pretty(pg_database_size(current_database()));3Verify
Retry the query that failed.
SELECT sum(size) FROM pg_ls_tmpdir();Confirmed by postgresql.org
How do I stop it coming back?
- Alert at 80 percent disk use, because some file systems perform badly when almost full.
- Keep log_temp_files at a sensible threshold so heavy queries show up in the log.
- Set temp_file_limit on shared servers so one bad query cannot take everyone down.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix