PostgreSQL – Setting Location of Temp Files for Backend (9.3.10)

disk-spacepostgresqlpostgresql-9.3

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.

Best Answer

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

Related Solutions

Postgresql – Index Generation in Postgres

If you create the index before loading the table, the time taken to load the data will be significantly increased.

pre load:

create table my_table1(val integer);
create index my_index1 on my_table1(val);
insert into my_table1(val) select generate_series(1,100000) order by random();
Time: 31755.858 ms

post load:

create table my_table2(val integer);
insert into my_table2(val) select generate_series(1,100000) order by random();
Time: 15344.130 ms
create index my_index2 on my_table2(val);
Time: 4073.686 ms

If you are ok with that, with pg_restore you can:

Load just the schema using --schema-only
Create the index with --index
Load the data using --data-only

Of course "Buy more storage" may well be the best answer here...

Postgresql – What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table

In more recent versions of Postgres (I think since 8.3) you can assign a special tablespace for temporary tables which might help you. This is documented here:

http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-TEMP-TABLESPACES

Given the fact that 8.2 will be de-supported at the end of the year it might be a good idea to upgrade. There have been numerous enhancements to VACUUM and the handling of temporary files since 8.2 so you might benefit from those.

Edit:
The reason why I think this (separate tablespace) could help you, is that you can simply drop and recreate the tablespace (files) to reclaim the spaces occupied.

But then I'd assume that due to all the improvements that were implemented in the last 5 years the current version might release the space without any further action from your side (especially because VACUUM FULL has been completely rewritten in 9.0)

Related Question