Why does postgresql prompt error 'perhaps out of disk space' while there is enough disk space?

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 TRUNCATEing tables in PostgreSQL (destroys data permanently)

• DROPping unwanted indexes in PostgreSQL

• Uninstalling programs

• ...

but the best option is:

• Get a bigger disk.