diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:17:33 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:17:33 +0000 |
| commit | 5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch) | |
| tree | 739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/manage-ag-tablespaces.html | |
| parent | Initial commit. (diff) | |
| download | postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip | |
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/manage-ag-tablespaces.html')
| -rw-r--r-- | doc/src/sgml/html/manage-ag-tablespaces.html | 132 |
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..af955f8 --- /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 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>
\ No newline at end of file |