Setting location of temp files for Postgresql backend (9.3.10)

There is a big problem with a_horse_with_no_name's solution. Not all instances of PostgreSQL even have a $PGDATA/base/pgsql_tmp because that entire path is actually created as needed in those instances. It doesn't even exist until needed, and it is destroyed when it is no longer required, so you can't create a symlink on an entire path that doesn't exist and that gets dynamically created then destroyed. You need to configue the temp tablespace location in the postgresql.conf file using the temp_tablespaces parameter (https://www.postgresql.org/docs/9.3/static/runtime-config-client.html#GUC-TEMP-TABLESPACES). This is because the temp_tablespace parameter not only designates the location of all temporary tables created by the CREATE command that do not have an explicit tablespace named at the time they are created, but also designates the location of all temporary files used for joins, sorts, and other overhead processing (https://www.postgresql.org/message-id/490ABE1D.3060700%40deriva.de).

As documented in the manual Postgres creates temporary files in $PGDATA/base/pgsql_tmp.

It is safe to make that directory a symlink to point to /mnt.

Try:

ln -s /mnt/pgsql_tmp $PGDATA/base/pgsql_tmp
chown -R postgres /mnt/pgsql_tmp
chmod o+x /mnt