Why does postgresql prompt error 'perhaps out of disk space' while there is enough disk space?
diskspace, postgresql, temporary-files
Solution
It's writing a temporary file. They're written to `temp_tablespaces`, for which which:
The default value is an empty string, which results in all temporary objects being created in the default tablespace of the current database.
meaning it's going to be writing tempfiles into your `data_directory`. To see where that is, run `SHOW data_directory`. I expect you'll get a result like `/var/lib/pgsql/9.3/data/`, indicating that PostgreSQL's data is in `/var`.
(Make sure to check this; if your temp tablespace is on a tempfs, then it's basically a ramdisk, and you should move it elsewhere because that's bad for performance as well as being a problem for big tempfiles.)
On your system, `/var` is part of the `/` filesystem, which is full. So the partition containing your PostgreSQL data is full, and PostgreSQL is correctly reporting this as an error.
Options to free space include:
Making sure old tempfiles in `/tmp` are being removed
Removing old log files from `/var/log`
Removing old PostgreSQL logs. Their location is somewhat variable based on OS/distro, which you didn't mention; they'll usually be in `/var/log/postgresql/` or `/var/lib/pgsql/9.3/data/pg_log`. Important: do not remove anything else in the PostgreSQL data dir; in particular, `pg_xlog` and `pg_clog` are vital parts of the database system and must not be removed, moved, or altered in any way.
Dropping or `TRUNCATE`ing tables in PostgreSQL (destroys data permanently)
`DROP`ping unwanted indexes in PostgreSQL
Uninstalling programs
...
but the best option is:
- Get a bigger disk.
Problem
I running this query in postgresql on ubuntu 12.0.4 : ``` SELECT t2.p AS prop, t2.o AS obj, COUNT(t2.o) AS num FROM "class_Event" AS t1, ((SELECT s,p,o FROM "prop_sliceHasAnomaly") UNION (SELECT s,p,o FROM "prop_eventHasDuration") UNION (SELECT s,p,o FROM "prop_sliceHasEndEvent") UNION (SELECT s,p,o FROM "prop_eventPrecedeInCPU") UNION (SELECT s,p,o FROM "prop_eventFollowInTask") UNION (SELECT s,p,o FROM "prop_topObjectProperty") UNION (SELECT s,p,o FROM "prop_eventDetails") UNION (SELECT s,p,o FROM "prop_switchTo") UNION (SELECT s,p,o FROM "prop_eventIsExecutedOn") UNION (SELECT s,p,o FROM "prop_runningAction") UNION (SELECT s,p,o FROM "prop_anomalyHasSlice") UNION (SELECT s,p,o FROM "prop_eventPrecedeInTrace") UNION (SELECT s,p,o FROM "prop_sliceHasStartEvent") UNION (SELECT s,p,o FROM "prop_eventHasActiveDuration") UNION (SELECT s,p,o FROM "prop_eventFollowInTrace") UNION (SELECT s,p,o FROM "prop_runningTask") UNION (SELECT s,p,o FROM "prop_eventOrdering") UNION (SELECT s,p,o FROM "prop_domain") UNION (SELECT s,p,o FROM "prop_sliceHasFunctionality") UNION (SELECT s,p,o FROM "prop_traceContainsEvent") UNION (SELECT s,p,o FROM "prop_eventHasDurationFromPreviousOccurrence") UNION (SELECT s,p,o FROM "prop_eventPrecedeOccurrence") UNION (SELECT s,p,o FROM "prop_eventPrecedeinTask") UNION (SELECT s,p,o FROM "prop_switchFrom") UNION (SELECT s,p,o FROM "prop_eventEndAt") UNION (SELECT s,p,o FROM "prop_subPropertyOf") UNION (SELECT s,p,o FROM "prop_eventFollowOccurrence") UNION (SELECT s,p,o FROM "prop_eventFollowInCPU") UNION (SELECT s,p,o FROM "prop_subClassOf") UNION (SELECT s,p,o FROM "prop_eventHasDurationToNextOccurrence") UNION (SELECT s,p,o FROM "prop_requestComponent") UNION (SELECT s,p,o FROM "prop_eventStartAt") UNION (SELECT s,p,o FROM "prop_range") UNION (SELECT s,p,o FROM "prop_functionalityHasSlice") ) AS t2 WHERE t1.s = t2.s GROUP BY t2.p, t2.o HAVING (COUNT(t2.o) > 1) ``` But I came out with this error from posgresql : ``` ERROR: could not write block 713 of temporary file: Aucun espace disponible sur le périphérique HINT: Perhaps out of disk space? ********** Erreur ********** ERROR: could not write block 713 of temporary file: Aucun espace disponible sur le périphérique État SQL :53100 Astuce : Perhaps out of disk space? ``` I've checked that there is enough of disk space with the command `df -h` ``` df -h df: «/var/lib/lightdm/.gvfs»: Permission non accordée Sys. de fichiers Taille Utilisé Dispo Uti% Monté sur /dev/sda5 37G 35G 46M 100% / udev 16G 4,0K 16G 1% /dev tmpfs 6,3G 984K 6,3G 1% /run none 5,0M 0 5,0M 0% /run/lock none 16G 216K 16G 1% /run/shm /dev/sda6 37G 411M 35G 2% /opt /dev/sda9 499G 435G 40G 92% /home /dev/sda7 37G 4,7G 30G 14% /usr /dev/sda8 37G 5,1G 30G 15% /usr/local ``` and it appears that there is 40GB free space disk on the `/home`. So I wonder what make this error to raise? is there a disk-space limitation parameter that can be set in postgresql configuration? someone have an idea about this ?