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?
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