Tablespace directory
A tablespace directory
, as the name implies, is a filesystem directory associated with a particular tablespace, where all data associated with the tablespace is stored.
The tablespace directory
is specified when executing CREATE TABLESPACE
, e.g.:
CREATE TABLESPACE foo LOCATION '/tmp/tblspc_test'
Prerequisites
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.
Structure
PostgreSQL creates a subdirectory within the specified tablespace directory for storing the actual tablespace data. The subdirectory name is formatted like this:
/tmp/tblspc_test/PG_14_202010291
"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.
System functions
The file system path of a tablespace directory can be retrieved with pg_tablespace_location()
.
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.
Examples
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