How do I move a PostgreSQL tablespace? 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.


Get Oid of your tablespace



test=# SELECT oid,spcname FROM pg_tablespace WHERE spcname = 'tblspc';
  oid  | spcname 
-------+---------
 24580 | tblspc
(1 row)



Stop postgres



$ pg_ctl -D $PGDATA stop



Move directory from old one to new one



$ mv /old/dir /new/dir


or


$ cp -r /old/dir /new/dir
$ rm -rf /old/dir



Change tablespace's link to new directory



$ cd $PGDATA/pg_tblspc
$ rm 24580
$ ln -s /new/dir 24580



Start postgres



$ 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)