How to move Tablespces to a different directory in Postgres

January 20, 2023

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:

  1. Record the oid of the tablespace you want to move
  2. Shut down the Postgres cluster
  3. Move the tablespace directory, either within the same file system or to a different file system
  4. Update the oid symbolic link that represents the moved tablespace to the new tablespace directory location
  5. Restart the server

Here's an example of moving a tablespace:

$ mkdir /u/postgres/test_tblspc

$ psql test

CREATE TABLESPACE test_tblspc LOCATION '/u/postgres/test_tblspc';

CREATE TABLE test_table (x int) TABLESPACE test_tblspc;

INSERT INTO test_table VALUES (1);

SELECT oid, * FROM pg_tablespace;

  oid  |   spcname   | spcowner | spcacl | spcoptions


  1663 | pg_default  |       10 |        |

  1664 | pg_global   |       10 |        |

 16385 | test_tblspc |       10 |        |

SELECT pg_tablespace_location(16385);





$ pg_ctl stop

$ mv /u/postgres/test_tblspc /u/postgres/test2_tblspc/

$ cd $PGDATA/pg_tblspc/

$ ls -l

lrwxrwxrwx 1 postgres postgres 23 Sep  5 22:20 16385 -> /u/postgres/test_tblspc

$ ln -fs /u/postgres/test2_tblspc 16385

$ ls -l

lrwxrwxrwx 1 root root 24 Sep  5 22:25 16385 -> /u/postgres/test2_tblspc

$ pg_ctl start

$ psql test

SELECT * FROM test_table;




SELECT pg_tablespace_location(16385);





(Article originally published in Bruce's personal blog - Wednesday, October 3, 2018, @ )


Share this

Relevant Blogs

An Overview of PostgreSQL Indexes

SUMMARY: This article describes indexes in PostgreSQL and how they can help retrieve data faster. It covers the types of indexes...
January 24, 2023

Postgres AT TIME ZONE Explained

I saw AT TIME ZONE used in a query, and found it confusing. I read the Postgres documentation and was still confused, so I played with some queries and finally figured it...
January 24, 2023

More Blogs

PostgreSQL and Golang Tutorial

SUMMARY: This article covers how to use PostgreSQL together with the programming language Golang (Go). After showing how to get started with Go’s Object Relational Mapper, it...
January 24, 2023