Moving Tablespaces in PostgreSQL

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 )