Monitoring and Diagnosis - PostgreSQL 9 Administration - page 201

When temp_tablespaces is empty
If the temp_tablespaces setting is empty, then the temporary tables are stored in the
same directory as ordinary tables, and temp files used for query processing are stored in the
pgsql_tmp directory inside the main database directory.
Look up the clusters home directory as follows:
select current_setting('data_directory') || '/base/pgsql_tmp'
The size of this directory gives the total size of current temporary files for query processing.
The total size of temporary files used by this database can be found by running the following
query:
select sum(pg_total_relation_size(relid))
from pg_stat_all_tables
where schemaname like 'pg_%temp%';
How it works...
Because all the temporary tables and other temporary on-disk data are stored in files, you use
PostgreSQL's internal tables to find the location of these files, and then determine the total
size of these files.
There's more...
While the preceding information about temporary tables is correct, it is not the wholes story.
Finding out if temporary file is in use any more
Because temporary files are not carefully preserved as ordinary tables (this is actually one of
the benefits of temporary tables, as less bookkeeping makes them faster) it may sometimes
happen that a system crash can leave around some temporary files, which can, in worst
cases, take up a significant amount of disk space.
As a rule, you can clean up such files by shutting down the PostgreSQL server, and then
deleting all files from the pgsql_temp directory.
Logging temporary file usage
If you set log_temp_files = 0 or a larger value, then the creation of all temporary files that
are larger than this value in kilobytes are logged to standard PostgreSQL log.