Setting location of temp files for Postgresql backend (9.3.10)

Question

Is it possible to set the location of temp file creation for a Postgresql backend (ver 9.3.10)?

Here's my scenario: my Postgresql DB resides in a dedicated VM with Ubuntu 14.04 OS. My VM comes with 200GB of temporary high-performance SSD storage provided by my infrastructure provider, meant for short-term storage for applications and processes. Here's how it looks like:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sdb1       221G  9.9G  200G   5% /mnt

I'm running low on disk space, and have to run some analytical queries that can take me to disk full due to the temporary files creation.

Apart from taking measures like deleting log entries to release more space or setting temp_file_limit in postgresql conf, I'm also interested in knowing whether I can set the location for temp files. After all, I have 220GB available for these kind of scenarios, so do want to put them to good use (swap space is set here too).

An illustrative example of how I can set this up would be very helpful since I'm a neophyte DBA.

Answer 1

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

Answer 2

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).

Answer 3

I think the response from paul and the suggestion from a horse_with_no_name are both right.

Tempfiles and temp tables are two different processes of temp storage by PostgreSQL. From 12.x I have been seeing an unique phenomenon where PostgreSQL is creating pgsql_tmp in the folder designated for Temp tablespace if any. I think its got to do with which user has write privs to the folders, but am not really sure.

Its important to remember that temp tables or files are generally cleared out or removed from the folder and thus are seldom seen.

I generally create temp tablespace, configure it in conf and grant privs to all users to the temptable space while hosting it in a ssd/Ramdrive. That way all the temp tables are created in the faster disk location, resulting in faster processing sppeds.