Home / ubuntu / Questions / 385191
In Process
Joren
Asked: 2013-12-03 15:40:56 +0800 CST

Move PostgreSQL temporary file location

  • 772

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.
postgresql

1 Answers

  1. 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.

    • 2