tablespace directory, as the name implies, is a filesystem directory associated with a particular tablespace, where all data associated with the tablespace is stored.
tablespace directory is specified when executing
CREATE TABLESPACE, e.g.:
CREATE TABLESPACE foo LOCATION '/tmp/tblspc_test'
The tablespace directory must be owned by the system user under which the PostgreSQL process itself runs (group write access is not sufficient), and must not be already used by another tablespace from the same PostgreSQL version.
Note that the tablespace directory does not need to be empty, and can contain tablespace directories from other PostgreSQL versions (this may be the case after upgrading a PostgreSQL instance with
pg_upgrade). However it is not recommended to store any files or create any directories within a tablespace directory.
PostgreSQL creates a subdirectory within the specified tablespace directory for storing the actual tablespace data. The subdirectory name is formatted like this:
14" represents the PostgreSQL major version, and "
202010291" represents the catalog version number (not the e.g.date the tablespace was created).
Initially this subdirectory does not contain any data.
The file system path of a tablespace directory can be retrieved with
Caveats and limitations
It is not possible to move a tablespace directory using PostgreSQL (there is no command
ALTER TABLESPACE ... LOCATION ... or similar); instead the tablespace must be moved manually while PostgreSQL is shut down.
It is not possibe to "detatch" or "attach" tablespaces; each directory containing a tablespace must be available for read and write operations at all times while PostgreSQL is running. It is however possible to move individual database objects between tablespaces.
postgres=# CREATE TABLESPACE foo LOCATION '/tmp/tblspc_test'; CREATE TABLESPACE $ ls -l /var/lib/pgsql/data/pg_tblspc/ total 0 lrwxrwxrwx 1 postgres postgres 16 Nov 6 14:38 16495 -> /tmp/tblspc_test $ ls -l /tmp/tblspc_test/ total 4 drwx------ 2 postgres postgres 4096 Nov 6 14:38 PG_14_202010291 $ ls -l /tmp/tblspc_test/PG_14_202010291 total 0 postgres=# CREATE TABLE foo (id INT) TABLESPACE foo; CREATE TABLE $ find /tmp/tblspc_test/PG_14_202010291/ /tmp/tblspc_test/PG_14_202010291/ /tmp/tblspc_test/PG_14_202010291/12869 /tmp/tblspc_test/PG_14_202010291/12869/16452