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
Best Answer
From the manual
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:You need to grant the necessary privileges on that tablespace:
After that you can e.g. use
(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:Alternatively just change the
$PGDATA/base/pgsql_tmp
to be a symbolic link to the directory on the other partition.