I’ll demonstrate how to relocate a tablespace directory without the reconstruction of databases.
I have a tablespace tblspc
located at /home/postgres/tblspc
and want to relocate to /home/postgres/new_tblspc
.
[1] Get Oid of the tablespace
testdb=# SELECT oid,spcname FROM pg_tablespace WHERE spcname = 'tblspc';
oid | spcname
-------+----------
24580 | tblspc
(1 row)
[2] Stop postgres
[postgres]$ pg_ctl -D $PGDATA stop
[3] Relocate the tablespace directory
[postgres]$ cp -r /home/postgres/tblspc /home/postgres/new_tblspc
[postgres]$ rm -rf /home/postgres/tblspc
[4] Change the link of tablespace to new directory
[postgres]$ cd $PGDATA/pg_tblspc
[postgres]$ rm 24580
[postgres]$ ln -s /home/postgres/new_tblspc 24580
[5] Start postgres
[postgres]$ pg_ctl -D $PGDATA start
Then, the tablespace’s directory has changed.
testdb=# SELECT pg_tablespace_location(24580);
pg_tablespace_location
--------------------------
/home/postgres/new_tblspc
(1 row)