Tablespace directory

A directory where data for a tablespace is stored

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

Categories

PostgreSQL internals, Tablespaces

See also

tablespace, CREATE TABLESPACE, data directory