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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
|
<!--
doc/src/sgml/ref/alter_tablespace.sgml
PostgreSQL documentation
-->
<refentry id="SQL-ALTERTABLESPACE">
<indexterm zone="sql-altertablespace">
<primary>ALTER TABLESPACE</primary>
</indexterm>
<refmeta>
<refentrytitle>ALTER TABLESPACE</refentrytitle>
<manvolnum>7</manvolnum>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>ALTER TABLESPACE</refname>
<refpurpose>change the definition of a tablespace or affect objects of a tablespace</refpurpose>
</refnamediv>
<refsynopsisdiv>
<synopsis>
ALTER TABLESPACE <replaceable>name</replaceable> RENAME TO <replaceable>new_name</replaceable>
ALTER TABLESPACE <replaceable>name</replaceable> OWNER TO <replaceable>new_owner</replaceable>
ALTER TABLESPACE <replaceable>name</replaceable> SET ( <replaceable class="PARAMETER">tablespace_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
ALTER TABLESPACE <replaceable>name</replaceable> RESET ( <replaceable class="PARAMETER">tablespace_option</replaceable> [, ... ] )
ALTER TABLESPACE <replaceable>name</replaceable> MOVE { ALL | TABLES | INDEXES | MATERIALIZED VIEWS } [ OWNED BY <replaceable class="PARAMETER">role_name</replaceable> [, ...] ] TO <replaceable>new_tablespace</replaceable> [ NOWAIT ]
</synopsis>
</refsynopsisdiv>
<refsect1>
<title>Description</title>
<para>
<command>ALTER TABLESPACE</command> can be used to change the definition of
a tablespace or to migrate objects in the current database between
tablespaces.
</para>
<para>
You must own the tablespace to change the definition of a tablespace.
To alter the owner, you must also be a direct or indirect member of the new
owning role.
(Note that superusers have these privileges automatically.)
</para>
<para>
<literal>ALTER TABLESPACE ... MOVE</literal> moves objects between
tablespaces. <literal>ALL</literal> will move all tables, indexes and
materialized views; specifying <literal>TABLES</literal> will move only
tables (but not their indexes), <literal>INDEXES</literal> will only move
indexes (including those underneath materialized views, but not tables),
and <literal>MATERIALIZED VIEWS</literal> will only move the table relation
of the materialized view (but no indexes associated with it). Users can
also specify a list of roles whose objects are to be moved, using
<literal>OWNED BY</literal>.
</para>
<para>
Users must have <literal>CREATE</literal> rights on the new tablespace and
be considered an owner (either directly or indirectly) of all objects to be
moved. Note that the superuser is considered an owner of all objects, and
therefore an <literal>ALTER TABLESPACE ... MOVE ALL</literal> issued by the
superuser will move all objects in the current database that are in the
tablespace. (Attempting to move objects without the required rights will
result in an error. Non-superusers can use <literal>OWNED BY</literal> in
such cases, to restrict the set of objects moved to those with the required
rights.)
</para>
<para>
All objects to be moved will be locked immediately by the command. If the
<literal>NOWAIT</literal> is specified, it will cause the command to fail
if it is unable to acquire the locks.
</para>
<para>
System catalogs will not be moved by this command. To move a whole
database, use <command>ALTER DATABASE</command>, or call <command>ALTER
TABLE</command> on the individual system catalogs. Note that relations in
<literal>information_schema</literal> will be moved, just as any other
normal database objects, if the user is the superuser or considered an
owner of the relations in <literal>information_schema</literal>.
</para>
</refsect1>
<refsect1>
<title>Parameters</title>
<variablelist>
<varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
The name of an existing tablespace.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">new_name</replaceable></term>
<listitem>
<para>
The new name of the tablespace. The new name cannot
begin with <literal>pg_</literal>, as such names
are reserved for system tablespaces.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">new_owner</replaceable></term>
<listitem>
<para>
The new owner of the tablespace.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">tablespace_option</replaceable></term>
<listitem>
<para>
A tablespace parameter to be set or reset. Currently, the only
available parameters are <varname>seq_page_cost</> and
<varname>random_page_cost</>. Setting either value for a particular
tablespace will override the planner's usual estimate of the cost of
reading pages from tables in that tablespace, as established by
the configuration parameters of the same name (see
<xref linkend="guc-seq-page-cost">,
<xref linkend="guc-random-page-cost">). This may be useful if one
tablespace is located on a disk which is faster or slower than the
remainder of the I/O subsystem.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">role_name</replaceable></term>
<listitem>
<para>
Role whose objects are to be moved.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">new_tablespace</replaceable></term>
<listitem>
<para>
The name of the tablespace to move objects into. The user must have
<literal>CREATE</literal> rights on the new tablespace to move objects into that
tablespace, unless the tablespace being moved into is the default
tablespace for the database connected to.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>NOWAIT</term>
<listitem>
<para>
The <literal>NOWAIT</literal> option causes the <command>ALTER TABLESPACE</command> command to fail immediately
if it is unable to acquire the necessary lock on all of the objects being
moved.
</para>
</listitem>
</varlistentry>
</variablelist>
</refsect1>
<refsect1>
<title>Examples</title>
<para>
Rename tablespace <literal>index_space</literal> to <literal>fast_raid</literal>:
<programlisting>
ALTER TABLESPACE index_space RENAME TO fast_raid;
</programlisting>
</para>
<para>
Change the owner of tablespace <literal>index_space</literal>:
<programlisting>
ALTER TABLESPACE index_space OWNER TO mary;
</programlisting></para>
<para>
Move all of the objects from the default tablespace to
the <literal>fast_raid</literal> tablespace:
<programlisting>
ALTER TABLESPACE pg_default MOVE ALL TO fast_raid;
</programlisting></para>
</refsect1>
<refsect1>
<title>Compatibility</title>
<para>
There is no <command>ALTER TABLESPACE</command> statement in
the SQL standard.
</para>
</refsect1>
<refsect1>
<title>See Also</title>
<simplelist type="inline">
<member><xref linkend="sql-createtablespace"></member>
<member><xref linkend="sql-droptablespace"></member>
</simplelist>
</refsect1>
</refentry>
|
