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.

• n0099: dba.stackexchange.com/questions/170661/…

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

• Hassan Baig: Ye I'm cognizant of the location of temporary pg files. So to confirm, you mean I symlink via ln -s $PGDATA/base/pgsql_tmp /mnt/pgsql_tmp, and then as per how soft links work, the OS will simply access the data in the file named by the data in the symlink (i.e. save and manipulate pg temp files at /mnt/pgsql_tmp). Correct?
• Hassan Baig: Secondly, could there be any reliability issues with this kind of arrangement? For instance, the OP here tried a symlink, but ran into issues (as per their comment): stackoverflow.com/a/18819514/4936905
• user1822: Yes, the symlink looks correct. Note that it would be safer to shut down Postgres before doing that. And the linked server is about permission problems - of course you need to make sure that the Linux user postgres is allowed to write into the temp directory.
• Hassan Baig: How critical is shutting down postgresql for this? My $PGDATA/base/pgsql_tmp location has always been empty - it's only after running some special big analytical queries that temporary files were created.
• user1822: If the temp dir is not used, I think it should be safe without shutting down.

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

• n0099: dba.stackexchange.com/questions/170661/…

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.