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
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>19.6.Tablespaces</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="managing-databases.html" title="Chapter19.Managing Databases">
<link rel="prev" href="manage-ag-dropdb.html" title="19.5.Destroying a Database">
<link rel="next" href="client-authentication.html" title="Chapter20.Client Authentication">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="manage-ag-tablespaces"></a>19.6.Tablespaces</h2></div></div></div>
<a name="id659871"></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>
<p> To define a tablespace, use the <a href="sql-createtablespace.html">CREATE TABLESPACE</a>
command, for example:<a name="id659939"></a>
</p>
<pre class="programlisting">CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';</pre>
<p>
The location must be an existing, empty directory that is owned by
the <span class="productname">PostgreSQL</span> system user. All objects subsequently
created within the tablespace will be stored in files underneath this
directory.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<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 make use of 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 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> The tablespace associated with a database is used to store the system
catalogs of that database, as well as any temporary files created by
server processes using that database. Furthermore, it is the default
tablespace selected for tables and indexes created within the database,
if no <code class="literal">TABLESPACE</code> clause is given (either explicitly or via
<code class="varname">default_tablespace</code>) when the objects are created.
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 by <code class="literal">initdb</code>. 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 href="sql-droptablespace.html">DROP TABLESPACE</a>
command.
</p>
<p> To determine the set of existing tablespaces, examine the
<code class="structname">pg_tablespace</code> system catalog, for example
</p>
<pre class="synopsis">SELECT spcname FROM pg_tablespace;</pre>
<p>
The <a href="app-psql.html" title="psql"><span class="refentrytitle"><a name="app-psql-title"></a><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 extensive 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. Two warnings: do not do so
while the postmaster is running; and after you restart the postmaster,
update the <code class="structname">pg_tablespace</code> catalog to show the new
locations. (If you do not, <code class="literal">pg_dump</code> will continue to show
the old tablespace locations.)
</p>
</div></body>
</html>
|
