Skip to content
Announcing BigAnimal: Fully managed PostgreSQL in the Cloud
Contact usDocsPlans

How to move Tablespces to a different directory in Postgres

Bruce Momjian11/7/2019

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, @ )


Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business value o ...