summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/manage-ag-tablespaces.html
blob: af955f8d4934e3ede16f60136a5704ee6641bbcf (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
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 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 15.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 /></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="53.56. 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 class="navfooter"><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 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 24. Localization</td></tr></table></div></body></html>