However, it's not infrequently the case that at some point you want to move an existing tablespace to a different mount point, because you're changing storage, re-arranging the filesystem, or making backup easier. There's two different ways to move tablespaces in current PostgreSQL: the slow online way and the fast downtime way.
The slow online way works like this:
- create a new tablespace in the desired new location
- go through all of the tables/indexes stored in the old tablespace, and do ALTER <object> TABLESPACE <new_tablespace> on each of them.
- drop the old tablespace
The second approach requires a database downtime and some understanding of how PostgreSQL stores tablespaces. Note that this technique will not work on versions prior to 9.2, since those versions also store the tablespace path in a system table, which forces you to use the online method.
If you look in your $PGDATA directory, you will find a subdirectory called pg_tblspc. This directory contains entries like this:
lrwxrwxrwx 1 josh josh 26535 -> /home/josh/tblspc
Each listing is a link to a directory location. If you follow that link, you find something like this:
rwx------ 3 josh josh 4096 PG_9.3_201306121
... in the target directory, there is a subdirectory named after the exact version which created it, which actually contains your tablespace data. At this point, a different way to move the tablespace should have occurred to you, and it does work.
- shut down the database system
- move or copy the PG_9* subdirectory from the old tablespace mount to the new one.
- change the link in pg_tblspc to point to the new mount point
- start the database system back up
The reason why this is a "faster" method is that step 2 allows you to use whatever filesystem copying tricks you wish to speed things up, such as a 2-stage rsync. It also allows you to handle cases where both tablespaces won't be online at the same time.
Also note that I'm not sure this works the same way on Windows.
Hope that helps!