Is there a way to physically move a PostgreSQL 9.3 tablespace from /old/dir to /new/dir? I'd like to just mv the directory and tell PostgreSQL that the tablespace is now located at /new/dir. Looks like ALTER TABLESPACE only lets you rename.
I'd like to avoid creating a new tablespace and moving the database to it. I assume that would be a slow copy process that requires a lot of time and disk space. I also want to avoid symlinking /old/dir to /new/dir.
I assume that your tablespace name is tblspc.
test=# SELECT oid,spcname FROM pg_tablespace WHERE spcname = 'tblspc'; oid | spcname -------+--------- 24580 | tblspc (1 row)
$ pg_ctl -D $PGDATA stop
$ mv /old/dir /new/dir
or
$ cp -r /old/dir /new/dir $ rm -rf /old/dir
$ cd $PGDATA/pg_tblspc $ rm 24580 $ ln -s /new/dir 24580
$ pg_ctl -D $PGDATA start
Then, you can see the tablespace's directory has changed.
test=# SELECT pg_tablespace_location(24580); pg_tablespace_location ------------------------ /new/dir (1 row)
