Moving Tablespaces in PostgreSQL

Bruce Momjian October 16, 2018

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_tablespace_location
-------------------------
 /u/postgres/test_tblspc
 
\q
 
$ 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;
 x
---
 1
 
SELECT pg_tablespace_location(16385);
  pg_tablespace_location
--------------------------
 /u/postgres/test2_tblspc

 

(Article originally published in Bruce's personal blog - Wednesday, October 3, 2018, @ https://momjian.us/main/blogs/pgblog/2018.html#October_3_2018 )

Bruce Momjian

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 of Postgres advances and new technology enhancements. He is the author of PostgreSQL: Introduction and Concepts, published by Addison-Wesley.