Two tablespaces are automatically created when the database cluster is initialized.
pg_global - this tablespace is used for shared system catalogs.
pg_default -this tablespace is the default tablespace of the template1 and template0 databases (and, therefore, will be the default tablespace for other databases as well, unless overridden by a TABLESPACE clause in CREATE DATABASE).
Once created, a tablespace can be used from any database, provided the requesting user has sufficient
privilege. This means that a tablespace cannot be dropped until all objects in all databases using the
tablespace have been removed.
Creation of the tablespace itself must be done as a database superuser, but after that you can allow
ordinary database users to use it. To do that, grant them the CREATE privilege on it.
CREATE TABLESPACE fastspace LOCATION ’/mnt/sda1/postgresql/data’;
The location must be an existing, empty directory that is owned by the PostgreSQL operating system user. All objects subsequently created within the tablespace will be stored in files underneath this directory.
There is also a temp_tablespaces parameter, which determines the placement of temporary tables and
indexes, as well as temporary files that are used for purposes such as sorting large data sets. This can
be a list of tablespace names, rather than only one, so that the load associated with temporary objects
can be spread over multiple tablespaces.
To determine the set of existing tablespaces, examine the pg_tablespace system catalog, for example
SELECT spcname FROM pg_tablespace;
The psql program’s \db meta-command is also useful for listing the existing tablespaces.
pg_global - this tablespace is used for shared system catalogs.
pg_default -this tablespace is the default tablespace of the template1 and template0 databases (and, therefore, will be the default tablespace for other databases as well, unless overridden by a TABLESPACE clause in CREATE DATABASE).
Once created, a tablespace can be used from any database, provided the requesting user has sufficient
privilege. This means that a tablespace cannot be dropped until all objects in all databases using the
tablespace have been removed.
Creation of the tablespace itself must be done as a database superuser, but after that you can allow
ordinary database users to use it. To do that, grant them the CREATE privilege on it.
CREATE TABLESPACE fastspace LOCATION ’/mnt/sda1/postgresql/data’;
The location must be an existing, empty directory that is owned by the PostgreSQL operating system user. All objects subsequently created within the tablespace will be stored in files underneath this directory.
There is also a temp_tablespaces parameter, which determines the placement of temporary tables and
indexes, as well as temporary files that are used for purposes such as sorting large data sets. This can
be a list of tablespace names, rather than only one, so that the load associated with temporary objects
can be spread over multiple tablespaces.
To determine the set of existing tablespaces, examine the pg_tablespace system catalog, for example
SELECT spcname FROM pg_tablespace;
The psql program’s \db meta-command is also useful for listing the existing tablespaces.
Comments