How do I move the PostgreSQL temporary file / cache location? I have limited space on my system partition so it keeps throwing me errors like this one:

ERROR: could not extend file "base/16384/16563.1": wrote only 4096 of 8192 bytes at block 207279
SQL state: 53100
Hint: Check free disk space.

To control where PostgreSQL puts temporary files, you first create one or more tablespaces to hold the temp files, then you set the temp_tablespaces configuration variable to specify those tablespaces.

It is important the tablespace directory continue to exist after a restart. It's OK for tempfiles to vanish from within it, but the target directory must exist or PostgreSQL will fail to start.

You can even give an explicit tablespace parameter to a CREATE TEMPORARY TABLE ... etc, allowing you to control where things go to distribute I/O around if you know more about the workload than the database server does.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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