How to move Tablespces to a different directory in Postgres

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 )