| 1 |
<!-- doc/src/sgml/manage-ag.sgml --> |
| 2 |
|
| 3 |
<chapter id="managing-databases"> |
| 4 |
<title>Managing Databases</title> |
| 5 |
|
| 6 |
<indexterm zone="managing-databases"><primary>database</primary></indexterm> |
| 7 |
|
| 8 |
<para> |
| 9 |
Every instance of a running <productname>PostgreSQL</productname> |
| 10 |
server manages one or more databases. Databases are therefore the |
| 11 |
topmost hierarchical level for organizing <acronym>SQL</acronym> |
| 12 |
objects (<quote>database objects</quote>). This chapter describes |
| 13 |
the properties of databases, and how to create, manage, and destroy |
| 14 |
them. |
| 15 |
</para> |
| 16 |
|
| 17 |
<sect1 id="manage-ag-overview"> |
| 18 |
<title>Overview</title> |
| 19 |
|
| 20 |
<indexterm zone="manage-ag-overview"> |
| 21 |
<primary>schema</primary> |
| 22 |
</indexterm> |
| 23 |
|
| 24 |
<para> |
| 25 |
A small number of objects, like role, database, and tablespace |
| 26 |
names, are defined at the cluster level and stored in the |
| 27 |
<literal>pg_global</literal> tablespace. Inside the cluster are |
| 28 |
multiple databases, which are isolated from each other but can access |
| 29 |
cluster-level objects. Inside each database are multiple schemas, |
| 30 |
which contain objects like tables and functions. So the full hierarchy |
| 31 |
is: cluster, database, schema, table (or some other kind of object, |
| 32 |
such as a function). |
| 33 |
</para> |
| 34 |
|
| 35 |
<para> |
| 36 |
When connecting to the database server, a client must specify the |
| 37 |
database name in its connection request. |
| 38 |
It is not possible to access more than one database per |
| 39 |
connection. However, clients can open multiple connections to |
| 40 |
the same database, or different databases. |
| 41 |
Database-level security has two components: access control |
| 42 |
(see <xref linkend="auth-pg-hba-conf"/>), managed at the |
| 43 |
connection level, and authorization control |
| 44 |
(see <xref linkend="ddl-priv"/>), managed via the grant system. |
| 45 |
Foreign data wrappers (see <xref linkend="postgres-fdw"/>) |
| 46 |
allow for objects within one database to act as proxies for objects in |
| 47 |
other database or clusters. |
| 48 |
The older dblink module (see <xref linkend="dblink"/>) provides a similar capability. |
| 49 |
By default, all users can connect to all databases using all connection methods. |
| 50 |
</para> |
| 51 |
|
| 52 |
<para> |
| 53 |
If one <productname>PostgreSQL</productname> server cluster is planned to contain |
| 54 |
unrelated projects or users that should be, for the most part, unaware |
| 55 |
of each other, it is recommended to put them into separate databases and |
| 56 |
adjust authorizations and access controls accordingly. |
| 57 |
If the projects or users are interrelated, and thus should be able to use |
| 58 |
each other's resources, they should be put in the same database but probably |
| 59 |
into separate schemas; this provides a modular structure with namespace |
| 60 |
isolation and authorization control. |
| 61 |
More information about managing schemas is in <xref linkend="ddl-schemas"/>. |
| 62 |
</para> |
| 63 |
|
| 64 |
<para> |
| 65 |
While multiple databases can be created within a single cluster, it is advised |
| 66 |
to consider carefully whether the benefits outweigh the risks and limitations. |
| 67 |
In particular, the impact that having a shared WAL (see <xref linkend="wal"/>) |
| 68 |
has on backup and recovery options. While individual databases in the cluster |
| 69 |
are isolated when considered from the user's perspective, they are closely bound |
| 70 |
from the database administrator's point-of-view. |
| 71 |
</para> |
| 72 |
|
| 73 |
<para> |
| 74 |
Databases are created with the <command>CREATE DATABASE</command> command |
| 75 |
(see <xref linkend="manage-ag-createdb"/>) and destroyed with the |
| 76 |
<command>DROP DATABASE</command> command |
| 77 |
(see <xref linkend="manage-ag-dropdb"/>). |
| 78 |
To determine the set of existing databases, examine the |
| 79 |
<structname>pg_database</structname> system catalog, for example |
| 80 |
<synopsis> |
| 81 |
SELECT datname FROM pg_database; |
| 82 |
</synopsis> |
| 83 |
The <xref linkend="app-psql"/> program's <literal>\l</literal> meta-command |
| 84 |
and <option>-l</option> command-line option are also useful for listing the |
| 85 |
existing databases. |
| 86 |
</para> |
| 87 |
|
| 88 |
<note> |
| 89 |
<para> |
| 90 |
The <acronym>SQL</acronym> standard calls databases <quote>catalogs</quote>, but there |
| 91 |
is no difference in practice. |
| 92 |
</para> |
| 93 |
</note> |
| 94 |
</sect1> |
| 95 |
|
| 96 |
<sect1 id="manage-ag-createdb"> |
| 97 |
<title>Creating a Database</title> |
| 98 |
|
| 99 |
<indexterm><primary>CREATE DATABASE</primary></indexterm> |
| 100 |
|
| 101 |
<para> |
| 102 |
In order to create a database, the <productname>PostgreSQL</productname> |
| 103 |
server must be up and running (see <xref |
| 104 |
linkend="server-start"/>). |
| 105 |
</para> |
| 106 |
|
| 107 |
<para> |
| 108 |
Databases are created with the SQL command |
| 109 |
<xref linkend="sql-createdatabase"/>: |
| 110 |
<synopsis> |
| 111 |
CREATE DATABASE <replaceable>name</replaceable>; |
| 112 |
</synopsis> |
| 113 |
where <replaceable>name</replaceable> follows the usual rules for |
| 114 |
<acronym>SQL</acronym> identifiers. The current role automatically |
| 115 |
becomes the owner of the new database. It is the privilege of the |
| 116 |
owner of a database to remove it later (which also removes all |
| 117 |
the objects in it, even if they have a different owner). |
| 118 |
</para> |
| 119 |
|
| 120 |
<para> |
| 121 |
The creation of databases is a restricted operation. See <xref |
| 122 |
linkend="role-attributes"/> for how to grant permission. |
| 123 |
</para> |
| 124 |
|
| 125 |
<para> |
| 126 |
Since you need to be connected to the database server in order to |
| 127 |
execute the <command>CREATE DATABASE</command> command, the |
| 128 |
question remains how the <emphasis>first</emphasis> database at any given |
| 129 |
site can be created. The first database is always created by the |
| 130 |
<command>initdb</command> command when the data storage area is |
| 131 |
initialized. (See <xref linkend="creating-cluster"/>.) This |
| 132 |
database is called |
| 133 |
<literal>postgres</literal>.<indexterm><primary>postgres</primary></indexterm> So to |
| 134 |
create the first <quote>ordinary</quote> database you can connect to |
| 135 |
<literal>postgres</literal>. |
| 136 |
</para> |
| 137 |
|
| 138 |
<para> |
| 139 |
Two additional databases, |
| 140 |
<literal>template1</literal><indexterm><primary>template1</primary></indexterm> |
| 141 |
and |
| 142 |
<literal>template0</literal>,<indexterm><primary>template0</primary></indexterm> |
| 143 |
are also created during database cluster initialization. Whenever a |
| 144 |
new database is created within the |
| 145 |
cluster, <literal>template1</literal> is essentially cloned. |
| 146 |
This means that any changes you make in <literal>template1</literal> are |
| 147 |
propagated to all subsequently created databases. Because of this, |
| 148 |
avoid creating objects in <literal>template1</literal> unless you want them |
| 149 |
propagated to every newly created database. |
| 150 |
<literal>template0</literal> is meant as a pristine copy of the original |
| 151 |
contents of <literal>template1</literal>. It can be cloned instead |
| 152 |
of <literal>template1</literal> when it is important to make a database |
| 153 |
without any such site-local additions. More details |
| 154 |
appear in <xref linkend="manage-ag-templatedbs"/>. |
| 155 |
</para> |
| 156 |
|
| 157 |
<para> |
| 158 |
As a convenience, there is a program you can |
| 159 |
execute from the shell to create new databases, |
| 160 |
<command>createdb</command>.<indexterm><primary>createdb</primary></indexterm> |
| 161 |
|
| 162 |
<synopsis> |
| 163 |
createdb <replaceable class="parameter">dbname</replaceable> |
| 164 |
</synopsis> |
| 165 |
|
| 166 |
<command>createdb</command> does no magic. It connects to the <literal>postgres</literal> |
| 167 |
database and issues the <command>CREATE DATABASE</command> command, |
| 168 |
exactly as described above. |
| 169 |
The <xref linkend="app-createdb"/> reference page contains the invocation |
| 170 |
details. Note that <command>createdb</command> without any arguments will create |
| 171 |
a database with the current user name. |
| 172 |
</para> |
| 173 |
|
| 174 |
<note> |
| 175 |
<para> |
| 176 |
<xref linkend="client-authentication"/> contains information about |
| 177 |
how to restrict who can connect to a given database. |
| 178 |
</para> |
| 179 |
</note> |
| 180 |
|
| 181 |
<para> |
| 182 |
Sometimes you want to create a database for someone else, and have them |
| 183 |
become the owner of the new database, so they can |
| 184 |
configure and manage it themselves. To achieve that, use one of the |
| 185 |
following commands: |
| 186 |
<programlisting> |
| 187 |
CREATE DATABASE <replaceable>dbname</replaceable> OWNER <replaceable>rolename</replaceable>; |
| 188 |
</programlisting> |
| 189 |
from the SQL environment, or: |
| 190 |
<programlisting> |
| 191 |
createdb -O <replaceable>rolename</replaceable> <replaceable>dbname</replaceable> |
| 192 |
</programlisting> |
| 193 |
from the shell. |
| 194 |
Only the superuser is allowed to create a database for |
| 195 |
someone else (that is, for a role you are not a member of). |
| 196 |
</para> |
| 197 |
</sect1> |
| 198 |
|
| 199 |
<sect1 id="manage-ag-templatedbs"> |
| 200 |
<title>Template Databases</title> |
| 201 |
|
| 202 |
<para> |
| 203 |
<command>CREATE DATABASE</command> actually works by copying an existing |
| 204 |
database. By default, it copies the standard system database named |
| 205 |
<literal>template1</literal>.<indexterm><primary>template1</primary></indexterm> Thus that |
| 206 |
database is the <quote>template</quote> from which new databases are |
| 207 |
made. If you add objects to <literal>template1</literal>, these objects |
| 208 |
will be copied into subsequently created user databases. This |
| 209 |
behavior allows site-local modifications to the standard set of |
| 210 |
objects in databases. For example, if you install the procedural |
| 211 |
language <application>PL/Perl</application> in <literal>template1</literal>, it will |
| 212 |
automatically be available in user databases without any extra |
| 213 |
action being taken when those databases are created. |
| 214 |
</para> |
| 215 |
|
| 216 |
<para> |
| 217 |
However, <command>CREATE DATABASE</command> does not copy database-level |
| 218 |
<command>GRANT</command> permissions attached to the source database. |
| 219 |
The new database has default database-level permissions. |
| 220 |
</para> |
| 221 |
|
| 222 |
<para> |
| 223 |
There is a second standard system database named |
| 224 |
<literal>template0</literal>.<indexterm><primary>template0</primary></indexterm> This |
| 225 |
database contains the same data as the initial contents of |
| 226 |
<literal>template1</literal>, that is, only the standard objects |
| 227 |
predefined by your version of |
| 228 |
<productname>PostgreSQL</productname>. <literal>template0</literal> |
| 229 |
should never be changed after the database cluster has been |
| 230 |
initialized. By instructing |
| 231 |
<command>CREATE DATABASE</command> to copy <literal>template0</literal> instead |
| 232 |
of <literal>template1</literal>, you can create a <quote>pristine</quote> user |
| 233 |
database (one where no user-defined objects exist and where the system |
| 234 |
objects have not been altered) that contains none of the site-local additions in |
| 235 |
<literal>template1</literal>. This is particularly handy when restoring a |
| 236 |
<literal>pg_dump</literal> dump: the dump script should be restored in a |
| 237 |
pristine database to ensure that one recreates the correct contents |
| 238 |
of the dumped database, without conflicting with objects that |
| 239 |
might have been added to <literal>template1</literal> later on. |
| 240 |
</para> |
| 241 |
|
| 242 |
<para> |
| 243 |
Another common reason for copying <literal>template0</literal> instead |
| 244 |
of <literal>template1</literal> is that new encoding and locale settings |
| 245 |
can be specified when copying <literal>template0</literal>, whereas a copy |
| 246 |
of <literal>template1</literal> must use the same settings it does. |
| 247 |
This is because <literal>template1</literal> might contain encoding-specific |
| 248 |
or locale-specific data, while <literal>template0</literal> is known not to. |
| 249 |
</para> |
| 250 |
|
| 251 |
<para> |
| 252 |
To create a database by copying <literal>template0</literal>, use: |
| 253 |
<programlisting> |
| 254 |
CREATE DATABASE <replaceable>dbname</replaceable> TEMPLATE template0; |
| 255 |
</programlisting> |
| 256 |
from the SQL environment, or: |
| 257 |
<programlisting> |
| 258 |
createdb -T template0 <replaceable>dbname</replaceable> |
| 259 |
</programlisting> |
| 260 |
from the shell. |
| 261 |
</para> |
| 262 |
|
| 263 |
<para> |
| 264 |
It is possible to create additional template databases, and indeed |
| 265 |
one can copy any database in a cluster by specifying its name |
| 266 |
as the template for <command>CREATE DATABASE</command>. It is important to |
| 267 |
understand, however, that this is not (yet) intended as |
| 268 |
a general-purpose <quote><command>COPY DATABASE</command></quote> facility. |
| 269 |
The principal limitation is that no other sessions can be connected to |
| 270 |
the source database while it is being copied. <command>CREATE |
| 271 |
DATABASE</command> will fail if any other connection exists when it starts; |
| 272 |
during the copy operation, new connections to the source database |
| 273 |
are prevented. |
| 274 |
</para> |
| 275 |
|
| 276 |
<para> |
| 277 |
Two useful flags exist in <literal>pg_database</literal><indexterm><primary>pg_database</primary></indexterm> for each |
| 278 |
database: the columns <literal>datistemplate</literal> and |
| 279 |
<literal>datallowconn</literal>. <literal>datistemplate</literal> |
| 280 |
can be set to indicate that a database is intended as a template for |
| 281 |
<command>CREATE DATABASE</command>. If this flag is set, the database can be |
| 282 |
cloned by any user with <literal>CREATEDB</literal> privileges; if it is not set, |
| 283 |
only superusers and the owner of the database can clone it. |
| 284 |
If <literal>datallowconn</literal> is false, then no new connections |
| 285 |
to that database will be allowed (but existing sessions are not terminated |
| 286 |
simply by setting the flag false). The <literal>template0</literal> |
| 287 |
database is normally marked <literal>datallowconn = false</literal> to prevent its modification. |
| 288 |
Both <literal>template0</literal> and <literal>template1</literal> |
| 289 |
should always be marked with <literal>datistemplate = true</literal>. |
| 290 |
</para> |
| 291 |
|
| 292 |
<note> |
| 293 |
<para> |
| 294 |
<literal>template1</literal> and <literal>template0</literal> do not have any special |
| 295 |
status beyond the fact that the name <literal>template1</literal> is the default |
| 296 |
source database name for <command>CREATE DATABASE</command>. |
| 297 |
For example, one could drop <literal>template1</literal> and recreate it from |
| 298 |
<literal>template0</literal> without any ill effects. This course of action |
| 299 |
might be advisable if one has carelessly added a bunch of junk in |
| 300 |
<literal>template1</literal>. (To delete <literal>template1</literal>, |
| 301 |
it must have <literal>pg_database.datistemplate = false</literal>.) |
| 302 |
</para> |
| 303 |
|
| 304 |
<para> |
| 305 |
The <literal>postgres</literal> database is also created when a database |
| 306 |
cluster is initialized. This database is meant as a default database for |
| 307 |
users and applications to connect to. It is simply a copy of |
| 308 |
<literal>template1</literal> and can be dropped and recreated if necessary. |
| 309 |
</para> |
| 310 |
</note> |
| 311 |
</sect1> |
| 312 |
|
| 313 |
<sect1 id="manage-ag-config"> |
| 314 |
<title>Database Configuration</title> |
| 315 |
|
| 316 |
<para> |
| 317 |
Recall from <xref linkend="runtime-config"/> that the |
| 318 |
<productname>PostgreSQL</productname> server provides a large number of |
| 319 |
run-time configuration variables. You can set database-specific |
| 320 |
default values for many of these settings. |
| 321 |
</para> |
| 322 |
|
| 323 |
<para> |
| 324 |
For example, if for some reason you want to disable the |
| 325 |
<acronym>GEQO</acronym> optimizer for a given database, you'd |
| 326 |
ordinarily have to either disable it for all databases or make sure |
| 327 |
that every connecting client is careful to issue <literal>SET geqo |
| 328 |
TO off</literal>. To make this setting the default within a particular |
| 329 |
database, you can execute the command: |
| 330 |
<programlisting> |
| 331 |
ALTER DATABASE mydb SET geqo TO off; |
| 332 |
</programlisting> |
| 333 |
This will save the setting (but not set it immediately). In |
| 334 |
subsequent connections to this database it will appear as though |
| 335 |
<literal>SET geqo TO off;</literal> had been executed just before the |
| 336 |
session started. |
| 337 |
Note that users can still alter this setting during their sessions; it |
| 338 |
will only be the default. To undo any such setting, use |
| 339 |
<literal>ALTER DATABASE <replaceable>dbname</replaceable> RESET |
| 340 |
<replaceable>varname</replaceable></literal>. |
| 341 |
</para> |
| 342 |
</sect1> |
| 343 |
|
| 344 |
<sect1 id="manage-ag-dropdb"> |
| 345 |
<title>Destroying a Database</title> |
| 346 |
|
| 347 |
<para> |
| 348 |
Databases are destroyed with the command |
| 349 |
<xref linkend="sql-dropdatabase"/>:<indexterm><primary>DROP DATABASE</primary></indexterm> |
| 350 |
<synopsis> |
| 351 |
DROP DATABASE <replaceable>name</replaceable>; |
| 352 |
</synopsis> |
| 353 |
Only the owner of the database, or |
| 354 |
a superuser, can drop a database. Dropping a database removes all objects |
| 355 |
that were |
| 356 |
contained within the database. The destruction of a database cannot |
| 357 |
be undone. |
| 358 |
</para> |
| 359 |
|
| 360 |
<para> |
| 361 |
You cannot execute the <command>DROP DATABASE</command> command |
| 362 |
while connected to the victim database. You can, however, be |
| 363 |
connected to any other database, including the <literal>template1</literal> |
| 364 |
database. |
| 365 |
<literal>template1</literal> would be the only option for dropping the last user database of a |
| 366 |
given cluster. |
| 367 |
</para> |
| 368 |
|
| 369 |
<para> |
| 370 |
For convenience, there is also a shell program to drop |
| 371 |
databases, <xref linkend="app-dropdb"/>:<indexterm><primary>dropdb</primary></indexterm> |
| 372 |
<synopsis> |
| 373 |
dropdb <replaceable class="parameter">dbname</replaceable> |
| 374 |
</synopsis> |
| 375 |
(Unlike <command>createdb</command>, it is not the default action to drop |
| 376 |
the database with the current user name.) |
| 377 |
</para> |
| 378 |
</sect1> |
| 379 |
|
| 380 |
<sect1 id="manage-ag-tablespaces"> |
| 381 |
<title>Tablespaces</title> |
| 382 |
|
| 383 |
<indexterm zone="manage-ag-tablespaces"> |
| 384 |
<primary>tablespace</primary> |
| 385 |
</indexterm> |
| 386 |
|
| 387 |
<para> |
| 388 |
Tablespaces in <productname>PostgreSQL</productname> allow database administrators to |
| 389 |
define locations in the file system where the files representing |
| 390 |
database objects can be stored. Once created, a tablespace can be referred |
| 391 |
to by name when creating database objects. |
| 392 |
</para> |
| 393 |
|
| 394 |
<para> |
| 395 |
By using tablespaces, an administrator can control the disk layout |
| 396 |
of a <productname>PostgreSQL</productname> installation. This is useful in at |
| 397 |
least two ways. First, if the partition or volume on which the |
| 398 |
cluster was initialized runs out of space and cannot be extended, |
| 399 |
a tablespace can be created on a different partition and used |
| 400 |
until the system can be reconfigured. |
| 401 |
</para> |
| 402 |
|
| 403 |
<para> |
| 404 |
Second, tablespaces allow an administrator to use knowledge of the |
| 405 |
usage pattern of database objects to optimize performance. For |
| 406 |
example, an index which is very heavily used can be placed on a |
| 407 |
very fast, highly available disk, such as an expensive solid state |
| 408 |
device. At the same time a table storing archived data which is |
| 409 |
rarely used or not performance critical could be stored on a less |
| 410 |
expensive, slower disk system. |
| 411 |
</para> |
| 412 |
|
| 413 |
<warning> |
| 414 |
<para> |
| 415 |
Even though located outside the main PostgreSQL data directory, |
| 416 |
tablespaces are an integral part of the database cluster and |
| 417 |
<emphasis>cannot</emphasis> be treated as an autonomous collection |
| 418 |
of data files. They are dependent on metadata contained in the main |
| 419 |
data directory, and therefore cannot be attached to a different |
| 420 |
database cluster or backed up individually. Similarly, if you lose |
| 421 |
a tablespace (file deletion, disk failure, etc.), the database cluster |
| 422 |
might become unreadable or unable to start. Placing a tablespace |
| 423 |
on a temporary file system like a RAM disk risks the reliability of |
| 424 |
the entire cluster. |
| 425 |
</para> |
| 426 |
</warning> |
| 427 |
|
| 428 |
<para> |
| 429 |
To define a tablespace, use the <xref |
| 430 |
linkend="sql-createtablespace"/> |
| 431 |
command, for example:<indexterm><primary>CREATE TABLESPACE</primary></indexterm>: |
| 432 |
<programlisting> |
| 433 |
CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data'; |
| 434 |
</programlisting> |
| 435 |
The location must be an existing, empty directory that is owned by |
| 436 |
the <productname>PostgreSQL</productname> operating system user. All objects subsequently |
| 437 |
created within the tablespace will be stored in files underneath this |
| 438 |
directory. The location must not be on removable or transient storage, |
| 439 |
as the cluster might fail to function if the tablespace is missing |
| 440 |
or lost. |
| 441 |
</para> |
| 442 |
|
| 443 |
<note> |
| 444 |
<para> |
| 445 |
There is usually not much point in making more than one |
| 446 |
tablespace per logical file system, since you cannot control the location |
| 447 |
of individual files within a logical file system. However, |
| 448 |
<productname>PostgreSQL</productname> does not enforce any such limitation, and |
| 449 |
indeed it is not directly aware of the file system boundaries on your |
| 450 |
system. It just stores files in the directories you tell it to use. |
| 451 |
</para> |
| 452 |
</note> |
| 453 |
|
| 454 |
<para> |
| 455 |
Creation of the tablespace itself must be done as a database superuser, |
| 456 |
but after that you can allow ordinary database users to use it. |
| 457 |
To do that, grant them the <literal>CREATE</literal> privilege on it. |
| 458 |
</para> |
| 459 |
|
| 460 |
<para> |
| 461 |
Tables, indexes, and entire databases can be assigned to |
| 462 |
particular tablespaces. To do so, a user with the <literal>CREATE</literal> |
| 463 |
privilege on a given tablespace must pass the tablespace name as a |
| 464 |
parameter to the relevant command. For example, the following creates |
| 465 |
a table in the tablespace <literal>space1</literal>: |
| 466 |
<programlisting> |
| 467 |
CREATE TABLE foo(i int) TABLESPACE space1; |
| 468 |
</programlisting> |
| 469 |
</para> |
| 470 |
|
| 471 |
<para> |
| 472 |
Alternatively, use the <xref linkend="guc-default-tablespace"/> parameter: |
| 473 |
<programlisting> |
| 474 |
SET default_tablespace = space1; |
| 475 |
CREATE TABLE foo(i int); |
| 476 |
</programlisting> |
| 477 |
When <varname>default_tablespace</varname> is set to anything but an empty |
| 478 |
string, it supplies an implicit <literal>TABLESPACE</literal> clause for |
| 479 |
<command>CREATE TABLE</command> and <command>CREATE INDEX</command> commands that |
| 480 |
do not have an explicit one. |
| 481 |
</para> |
| 482 |
|
| 483 |
<para> |
| 484 |
There is also a <xref linkend="guc-temp-tablespaces"/> parameter, which |
| 485 |
determines the placement of temporary tables and indexes, as well as |
| 486 |
temporary files that are used for purposes such as sorting large data |
| 487 |
sets. This can be a list of tablespace names, rather than only one, |
| 488 |
so that the load associated with temporary objects can be spread over |
| 489 |
multiple tablespaces. A random member of the list is picked each time |
| 490 |
a temporary object is to be created. |
| 491 |
</para> |
| 492 |
|
| 493 |
<para> |
| 494 |
The tablespace associated with a database is used to store the system |
| 495 |
catalogs of that database. Furthermore, it is the default tablespace |
| 496 |
used for tables, indexes, and temporary files created within the database, |
| 497 |
if no <literal>TABLESPACE</literal> clause is given and no other selection is |
| 498 |
specified by <varname>default_tablespace</varname> or |
| 499 |
<varname>temp_tablespaces</varname> (as appropriate). |
| 500 |
If a database is created without specifying a tablespace for it, |
| 501 |
it uses the same tablespace as the template database it is copied from. |
| 502 |
</para> |
| 503 |
|
| 504 |
<para> |
| 505 |
Two tablespaces are automatically created when the database cluster |
| 506 |
is initialized. The |
| 507 |
<literal>pg_global</literal> tablespace is used only for shared system catalogs. The |
| 508 |
<literal>pg_default</literal> tablespace is the default tablespace of the |
| 509 |
<literal>template1</literal> and <literal>template0</literal> databases (and, therefore, |
| 510 |
will be the default tablespace for other databases as well, unless |
| 511 |
overridden by a <literal>TABLESPACE</literal> clause in <command>CREATE |
| 512 |
DATABASE</command>). |
| 513 |
</para> |
| 514 |
|
| 515 |
<para> |
| 516 |
Once created, a tablespace can be used from any database, provided |
| 517 |
the requesting user has sufficient privilege. This means that a tablespace |
| 518 |
cannot be dropped until all objects in all databases using the tablespace |
| 519 |
have been removed. |
| 520 |
</para> |
| 521 |
|
| 522 |
<para> |
| 523 |
To remove an empty tablespace, use the <xref |
| 524 |
linkend="sql-droptablespace"/> |
| 525 |
command. |
| 526 |
</para> |
| 527 |
|
| 528 |
<para> |
| 529 |
To determine the set of existing tablespaces, examine the |
| 530 |
<link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname> |
| 531 |
</link> system catalog, for example |
| 532 |
<synopsis> |
| 533 |
SELECT spcname, spcowner::regrole, pg_tablespace_location(oid) FROM pg_tablespace; |
| 534 |
</synopsis> |
| 535 |
It is possible to find which databases use which tablespaces; |
| 536 |
see <xref linkend="functions-info-catalog-table"/>. The <xref |
| 537 |
linkend="app-psql"/> program's <literal>\db</literal> meta-command |
| 538 |
is also useful for listing the existing tablespaces. |
| 539 |
</para> |
| 540 |
|
| 541 |
<para> |
| 542 |
The directory <filename>$PGDATA/pg_tblspc</filename> contains symbolic links that |
| 543 |
point to each of the non-built-in tablespaces defined in the cluster. |
| 544 |
Although not recommended, it is possible to adjust the tablespace |
| 545 |
layout by hand by redefining these links. Under no circumstances perform |
| 546 |
this operation while the server is running. |
| 547 |
</para> |
| 548 |
|
| 549 |
</sect1> |
| 550 |
</chapter> |