4

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
0

2 Answers 2

7

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.

3
  • I edited my question to show the restuls of running the symlink option. Despite pointing the directory to a different partition, I still get the "could not write block 1862514 of temporary file: No space left on device" error.
    – Dave
    Apr 10, 2017 at 22:54
  • 1
    Please disregard my comment. The query was causing so much data to be generated, even my other partition wasn't big enough.
    – Dave
    Apr 11, 2017 at 0:05
  • Thank you for including the grant statement. No other guide has it, and it's essential.
    – mlissner
    Oct 9, 2021 at 4:43
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.