In Postgres, how do I adjust the "pgsql_tmp" setting?

Question

I"m using Postgres 9.5.4 on Ubuntu 14.04. I have my Postgres data on a separate disk partiion, which is getting pretty full.

myuser@myproject:~$ df -h /mnt/volume-nyc1-01/
Filesystem Size Used Avail Use% Mounted on
/dev/sda 99G 93G 413M 100% /mnt/volume-nyc1-01

I want to delete some data from my partition, but this has become challenging. In particular, when I run some queries, i get results like this

myproject_production=> select count(*) FROM my_object_times rt1, my_object_times rt2 where rt1.my_object_id = rt2.my_object_id and rt1.name = rt2.name and rt1.time_in_ms = rt2.time_in_ms and rt1.id > rt2.id;;
ERROR:  could not write block 52782 of temporary file: No space left on device

I want to free up some temp space so I can run queries and identify what data I need to delete. I have some other free space on another partition. How do I point my pgsql_tmp variable there so that I can run the queries I need?

Edit:

As the symlink option seemed to be the least invasive, I gave it a go, setting up things like this

myuser@myproject:~$ sudo ls -al /mnt/volume-nyc1-01/postgresql/9.5/main/base/pgsql_tmp
lrwxrwxrwx 1 root root 14 Apr 10 18:01 /mnt/volume-nyc1-01/postgresql/9.5/main/base/pgsql_tmp -> /opt/pgsql_tmp
myuser@myproject:~$ cd /opt
myuser@myproject:/opt$ df -h /
Filesystem                                              Size  Used Avail Use% Mounted on
/dev/disk/by-uuid/050e1e34-39e6-4072-a03e-ae0bf90ba13a   40G   24G   15G  62% /

You can see that I have over 16GB available on the partition where I pointed it to, but still I get the errors

ERROR:  could not write block 1862514 of temporary file: No space left on device

Answer 1

From the manual

temp_tablespaces (string)

This variable specifies tablespaces in which to create temporary objects (temp tables and indexes on temp tables) when a CREATE command does not explicitly specify a tablespace. Temporary files for purposes such as sorting large data sets are also created in these tablespaces.

In order to be able to use that, you need to create a tablespace on that volume, create a directory there (owned by the postgres user!), then run this:

create tablespace temp_space owner postgres location '/mnt/volume-nyc1-01/pg_temp';

You need to grant the necessary privileges on that tablespace:

grant create on tablespace temp_space to public;

After that you can e.g. use

alter system set temp_tablespaces=temp_space;

(alternatively you could just change that in your session)

Note that for the alter system to have any effect, you need to reload the configuration:

select pg_reload_conf();

---

Alternatively just change the $PGDATA/base/pgsql_tmp to be a symbolic link to the directory on the other partition.

Answer 2

If after the creation and setting for temp_tabespaces you are still getting:

create temp table a(b text);

ERROR: could not create directory "pg_tblspc/{oid of the tablespace temp_space} /PG_16_202307071/16389": No such file or directory

just

mkdir -p /mnt/volume-nyc1-01/pg_temp/PG_16_202307071
chown -R postgres: /mnt/volume-nyc1-01/pg_temp
chmod -R 700 /mnt/volume-nyc1-01/pg_temp

where PG_16_202307071 will be change after upgrade.