summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/manage-ag-tablespaces.html
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src/sgml/html/manage-ag-tablespaces.html')
-rw-r--r--doc/src/sgml/html/manage-ag-tablespaces.html132
1 files changed, 132 insertions, 0 deletions
diff --git a/doc/src/sgml/html/manage-ag-tablespaces.html b/doc/src/sgml/html/manage-ag-tablespaces.html
new file mode 100644
index 0000000..735989e
--- /dev/null
+++ b/doc/src/sgml/html/manage-ag-tablespaces.html
@@ -0,0 +1,132 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>23.6. Tablespaces</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="manage-ag-dropdb.html" title="23.5. Destroying a Database" /><link rel="next" href="charset.html" title="Chapter 24. Localization" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">23.6. Tablespaces</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="manage-ag-dropdb.html" title="23.5. Destroying a Database">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="managing-databases.html" title="Chapter 23. Managing Databases">Up</a></td><th width="60%" align="center">Chapter 23. Managing Databases</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="charset.html" title="Chapter 24. Localization">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="MANAGE-AG-TABLESPACES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">23.6. Tablespaces</h2></div></div></div><a id="id-1.6.10.9.2" class="indexterm"></a><p>
+ Tablespaces in <span class="productname">PostgreSQL</span> allow database administrators to
+ define locations in the file system where the files representing
+ database objects can be stored. Once created, a tablespace can be referred
+ to by name when creating database objects.
+ </p><p>
+ By using tablespaces, an administrator can control the disk layout
+ of a <span class="productname">PostgreSQL</span> installation. This is useful in at
+ least two ways. First, if the partition or volume on which the
+ cluster was initialized runs out of space and cannot be extended,
+ a tablespace can be created on a different partition and used
+ until the system can be reconfigured.
+ </p><p>
+ Second, tablespaces allow an administrator to use knowledge of the
+ usage pattern of database objects to optimize performance. For
+ example, an index which is very heavily used can be placed on a
+ very fast, highly available disk, such as an expensive solid state
+ device. At the same time a table storing archived data which is
+ rarely used or not performance critical could be stored on a less
+ expensive, slower disk system.
+ </p><div class="warning"><h3 class="title">Warning</h3><p>
+ Even though located outside the main PostgreSQL data directory,
+ tablespaces are an integral part of the database cluster and
+ <span class="emphasis"><em>cannot</em></span> be treated as an autonomous collection
+ of data files. They are dependent on metadata contained in the main
+ data directory, and therefore cannot be attached to a different
+ database cluster or backed up individually. Similarly, if you lose
+ a tablespace (file deletion, disk failure, etc), the database cluster
+ might become unreadable or unable to start. Placing a tablespace
+ on a temporary file system like a RAM disk risks the reliability of
+ the entire cluster.
+ </p></div><p>
+ To define a tablespace, use the <a class="xref" href="sql-createtablespace.html" title="CREATE TABLESPACE"><span class="refentrytitle">CREATE TABLESPACE</span></a>
+ command, for example:<a id="id-1.6.10.9.7.2" class="indexterm"></a>:
+</p><pre class="programlisting">
+CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';
+</pre><p>
+ The location must be an existing, empty directory that is owned by
+ the <span class="productname">PostgreSQL</span> operating system user. All objects subsequently
+ created within the tablespace will be stored in files underneath this
+ directory. The location must not be on removable or transient storage,
+ as the cluster might fail to function if the tablespace is missing
+ or lost.
+ </p><div class="note"><h3 class="title">Note</h3><p>
+ There is usually not much point in making more than one
+ tablespace per logical file system, since you cannot control the location
+ of individual files within a logical file system. However,
+ <span class="productname">PostgreSQL</span> does not enforce any such limitation, and
+ indeed it is not directly aware of the file system boundaries on your
+ system. It just stores files in the directories you tell it to use.
+ </p></div><p>
+ 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 <code class="literal">CREATE</code> privilege on it.
+ </p><p>
+ Tables, indexes, and entire databases can be assigned to
+ particular tablespaces. To do so, a user with the <code class="literal">CREATE</code>
+ privilege on a given tablespace must pass the tablespace name as a
+ parameter to the relevant command. For example, the following creates
+ a table in the tablespace <code class="literal">space1</code>:
+</p><pre class="programlisting">
+CREATE TABLE foo(i int) TABLESPACE space1;
+</pre><p>
+ </p><p>
+ Alternatively, use the <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TABLESPACE">default_tablespace</a> parameter:
+</p><pre class="programlisting">
+SET default_tablespace = space1;
+CREATE TABLE foo(i int);
+</pre><p>
+ When <code class="varname">default_tablespace</code> is set to anything but an empty
+ string, it supplies an implicit <code class="literal">TABLESPACE</code> clause for
+ <code class="command">CREATE TABLE</code> and <code class="command">CREATE INDEX</code> commands that
+ do not have an explicit one.
+ </p><p>
+ There is also a <a class="xref" href="runtime-config-client.html#GUC-TEMP-TABLESPACES">temp_tablespaces</a> 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. A random member of the list is picked each time
+ a temporary object is to be created.
+ </p><p>
+ The tablespace associated with a database is used to store the system
+ catalogs of that database. Furthermore, it is the default tablespace
+ used for tables, indexes, and temporary files created within the database,
+ if no <code class="literal">TABLESPACE</code> clause is given and no other selection is
+ specified by <code class="varname">default_tablespace</code> or
+ <code class="varname">temp_tablespaces</code> (as appropriate).
+ If a database is created without specifying a tablespace for it,
+ it uses the same tablespace as the template database it is copied from.
+ </p><p>
+ Two tablespaces are automatically created when the database cluster
+ is initialized. The
+ <code class="literal">pg_global</code> tablespace is used for shared system catalogs. The
+ <code class="literal">pg_default</code> tablespace is the default tablespace of the
+ <code class="literal">template1</code> and <code class="literal">template0</code> databases (and, therefore,
+ will be the default tablespace for other databases as well, unless
+ overridden by a <code class="literal">TABLESPACE</code> clause in <code class="command">CREATE
+ DATABASE</code>).
+ </p><p>
+ 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.
+ </p><p>
+ To remove an empty tablespace, use the <a class="xref" href="sql-droptablespace.html" title="DROP TABLESPACE"><span class="refentrytitle">DROP TABLESPACE</span></a>
+ command.
+ </p><p>
+ To determine the set of existing tablespaces, examine the
+ <a class="link" href="catalog-pg-tablespace.html" title="52.54. pg_tablespace"><code class="structname">pg_tablespace</code>
+ </a> system catalog, for example
+</p><pre class="synopsis">
+SELECT spcname FROM pg_tablespace;
+</pre><p>
+ The <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a> program's <code class="literal">\db</code> meta-command
+ is also useful for listing the existing tablespaces.
+ </p><p>
+ <span class="productname">PostgreSQL</span> makes use of symbolic links
+ to simplify the implementation of tablespaces. This
+ means that tablespaces can be used <span class="emphasis"><em>only</em></span> on systems
+ that support symbolic links.
+ </p><p>
+ The directory <code class="filename">$PGDATA/pg_tblspc</code> contains symbolic links that
+ point to each of the non-built-in tablespaces defined in the cluster.
+ Although not recommended, it is possible to adjust the tablespace
+ layout by hand by redefining these links. Under no circumstances perform
+ this operation while the server is running. Note that in PostgreSQL 9.1
+ and earlier you will also need to update the <code class="structname">pg_tablespace</code>
+ catalog with the new locations. (If you do not, <code class="literal">pg_dump</code> will
+ continue to output the old tablespace locations.)
+ </p></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="manage-ag-dropdb.html" title="23.5. Destroying a Database">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="managing-databases.html" title="Chapter 23. Managing Databases">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="charset.html" title="Chapter 24. Localization">Next</a></td></tr><tr><td width="40%" align="left" valign="top">23.5. Destroying a Database </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 24. Localization</td></tr></table></div></body></html> \ No newline at end of file