Monitoring and Diagnosis - PostgreSQL 9 Administration - page 200

Getting ready
Same as previous—you can do this using any untrusted embedded language, or directly on
the database host. You have to use an untrusted language, because trusted ones run in a
sandbox, which prohibits them from accessing the host file system directly.
How to do it...
First, check if your database defines special tablespaces for temporary files as follows:
select current_setting('temp_tablespaces');
When temp_tablespaces has one or more tablespaces
If it does, then your task is easy, because all temporary files, both for temporary table and
those used for query processing, are inside the directories for these tablespaces—just look
up the corresponding directories from pg_tablespaces as follows:
select spcname,spclocation from pg_tablespace;
Then, a simple du command shows you the space used by temporary files.
A sample session is as follows:
db=# select current_setting('temp_tablespaces');
current_setting
-----------------
temp1, temp2
(1 row)
db=#
db=# select spcname,spclocation from pg_tablespace where spcname in
('temp1', 'temp2');
spcname | spclocation
---------+---------------
temp1 | /test/pg_tmp1
temp2 | /test/pg_tmp2
(2 rows)
db=# \q
user@host:~$
sudo du -s /test/pg_tmp1 /test/pg_tmp2
102136 /test/pg_tmp1
35144 /test/pg_tmp2
Because the amount of temporary disk space used can vary a lot on an active system, you
may want to repeat the last du -s command several times to get a better picture of how the
disk usage changes.