Move Non-Default Tablespace

Overview

I moved a user-defined tablespace from its location inside the data directory to another directory.

Note: This procedure is for tablespaces that are not default for a database. For those tablespaces, refer to the procedure in Move Default Tablespace

Initial Plan

Based on my reading of the PostgreSQL documentation, I planned to move the user_ts tablespace by using the following steps:

  1. Rename tablespace user_ts to old_user_ts
  2. Create new directory
  3. Create a new tablespace, user_ts, in the new directory
  4. Connect to user database
  5. Move all tables and indexes using the following commands:
    • ALTER TABLE ALL IN TABLESPACE old_user_ts SET TABLESPACE user_ts
    • ALTER INDEX ALL IN TABLESPACE old_user_ts SET TABLESPACE user_ts
  6. Drop the tablespace, old_user_ts