Pivotal Knowledge Base


How to Change Where Workfiles, Spillfiles, and Transaction Files are Stored?


Pivotal Greenplum Database (GPDB) all versions


This article shows how to change the location where work files or transaction logs are created.


By default work files are created int he default filespace for each database. Though there are mechanisms in place to limit the number of spill files created at any one time it is still possible to consume all of the available drive space resulting in a segment failure or to swamp the device with I/O resulting in poor performance for other queries which need to read or write data files.

This process is fully documented in the Database Administrator Guide for GPDB and relies on using gpfilespace to add and modify the storage for a given database. For more details please see the Administrator Guide Under "Creating and Managing Tablespaces". For instructions on using gpfilespace please review the command documentation in the GPDB Utility Guide.

The default location for work files is: <segment_directory>/base/<tablespace_oid>/<database_oid>/pgsql_tmp

Moving the transaction files will relocate the following directories and their contents:

  • <segment_directory>/pg_clog
  • <segment_directory>/pg_distributedlog
  • <segment_directory>/pg_distributedxidmap
  • <segment_directory>/pg_multixact
  • <segment_directory>/pg_subtrans
  • <segment_directory>/pg_xlog

Process for changing the location of these files is as follows:

  1. Create a new file space config using the command

     gpfilespace -o gpfilespace_config

    on the master.

  2. Verify the configuration file and if no issues are found, create the new filespaces using the command:

     gpfilespace -c gpfilespace_config
  3. To move the location where the work files are created issue the command:

     gpfilespace --movetempfilespace <fs_name>

    where <fs_name> is the name of the newly created filespace.

  4. To move the location of the transaction files uses the command:

     gpfilespace --movetransfilespace <fs_name>

    where <fs_name> is the name of the newly created filespace.

A few notes:

  • running these commands will stop the database, please ensure there is no activity when these commands are run otherwise they will fail
  • the temporary files and transaction files do not need to be stored on the same file space.
  • to revert back to the default locations, re-run the above command with the value default for the filespace name


Powered by Zendesk