TablespacesPostgreSQL implements the tablespaces creating the symbolic links, pointing the tablespace's location, into the pg_tblspc. The links are named like the tablespace's OID. The tablespaces are available only on systems supporting the symbolic links.
Since PostgreSQL 9.1 the tablespace location was stored into the field spclocation of the pg_tablespace system table. The information was used only to dump the tablespace's definition during a pg_dump and was removed in the version 9.2 and the function pg_tablespace_location(tablespace_oid), which returns the tablespace's absolute path from the tablespace oid, was introduced.
For example this query will return the tablespace's location seen in 5.6
postgres=# SELECT pg_tablespace_location(oid), spcname FROM pg_tablespace; pg_tablespace_location | spcname ------------------------------------+------------ | pg_default | pg_global /var/lib/postgresql/pg_tbs/ts_test | ts_test (3 rows)The function returns the empty string for the system tablespaces, pg_default and pg_global, because those locations have an immutable location, relative to the data directory. The data area's absolute path can be retrieved using the function current_settings
postgres=# SELECT current_setting('data_directory'); current_setting ------------------------------ /var/lib/postgresql/9.3/main (1 row)Combining the CASE construct is then possible to build up an useful query to lookout the tablespaces locations.
postgres=# SELECT CASE WHEN pg_tablespace_location(oid)='' AND spcname='pg_default' THEN current_setting('data_directory')||'/base/' WHEN pg_tablespace_location(oid)='' AND spcname='pg_global' THEN current_setting('data_directory')||'/global/' ELSE pg_tablespace_location(oid) END AS spclocation, spcname FROM pg_tablespace; spclocation | spcname --------------------------------------+------------ /var/lib/postgresql/9.3/main/base/ | pg_default /var/lib/postgresql/9.3/main/global/ | pg_global /var/lib/postgresql/pg_tbs/ts_test | ts_test (3 rows)Before the version 8.4 the tablespace location pointed directly to the referenced directory, causing a potential location's clash. The newer versions introduced the usage of a container directory into the tablespace location named after the major version and the system catalogue release.
postgres@tardis:~$ ls -l /var/lib/postgresql/pg_tbs/ts_test total 0 drwx------ 2 postgres postgres 6 Jun 9 13:01 PG_9.3_201306121The tablespace's directory container is structured this way
The major version is the PostgreSQL version truncated to the second cypher and the catalogue's version number is the same shown in the pg_controldata output, a formatted date.
postgres@tardis:~$ export PGDATA=/var/lib/postgresql/9.3/main postgres@tardis:~$ /usr/lib/postgresql/9.3/bin/pg_controldata pg_control version number: 937 Catalog version number: 201306121 Database system identifier: 5992975355079285751 Database cluster state: in production pg_control last modified: Mon 09 Jun 2014 13:05:14 UTC . . . WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value Data page checksum version: 0The container directory have the same structure as base seen in 4.6, with only the subdirectories for the databases having relations on the tablespace.
To get all the databases with objects on the current tablespace it's present the function pg_tablespace_databases(tablespace_oid) which returns the set of the database OID with objects on the specified tablespace. In order to have a better picture a join with the pg_database system table is a good idea.
For example this query combines the CASE construct with the pg_tablespace_databases function.
db_test=# SELECT datname, spcname, CASE WHEN pg_tablespace_location(tbsoid)='' AND spcname='pg_default' THEN current_setting('data_directory')||'/base/' WHEN pg_tablespace_location(tbsoid)='' AND spcname='pg_global' THEN current_setting('data_directory')||'/global/' ELSE pg_tablespace_location(tbsoid) END AS spclocation FROM pg_database dat, ( SELECT oid as tbsoid, pg_tablespace_databases(oid) as datoid, spcname FROM pg_tablespace where spcname='ts_test' ) tbs WHERE dat.oid=tbs.datoid ; datname | spcname | spclocation ---------+---------+------------------------------------ db_test | ts_test | /var/lib/postgresql/pg_tbs/ts_test (1 row)Moving a tablespace to another physical location it's not complicated; the cluster of course has to be shut down, see 4.3 for more informations.
After the cluster is stopped the container directory can be copied to the new location; the directory's access permissions must be the same as the origin; read write for the os user running the postgresql process only,otherwise the cluster will refuse to start.
When the copy is complete and the symbolic link in $PGDATA/pg_tblspc is fixed to point the new location the cluster can be started as shown in 4.2.