Tablespaces are designed to allow Postgres clusters to be spread across multiple storage devices. Create tablespace creates a symbolic link in the pg_tblspc directory in the cluster's data directory pointing to the newly-created tablespace directory.
Unfortunately, though there is a command to move tables and indexes between tablespaces, there is no command to move tablespaces to different directories. However, since Postgres 9.2, the process of moving tablespaces is quite simple:
- Record the oid of the tablespace you want to move
- Shut down the Postgres cluster
- Move the tablespace directory, either within the same file system or to a different file system
- Update the oid symbolic link that represents the moved tablespace to the new tablespace directory location
- Restart the server
See an example of moving a tablespace on Postgres Rocks. >>
Every #TechTuesday, EnterpriseDB shares a how-to post authored by a Postgres contributor and expert for Postgres Gems, the PostgresRocks community forum. PostgresRocks is a community to discuss all things Postgres. Join us at PostgresRocks and be part of the conversation.
