doc/src/sgml/ref/create_tablespace.sgml

   1 <!--
   2 doc/src/sgml/ref/create_tablespace.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-createtablespace">
   7  <indexterm zone="sql-createtablespace">
   8   <primary>CREATE TABLESPACE</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>CREATE TABLESPACE</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>CREATE TABLESPACE</refname>
  19   <refpurpose>define a new tablespace</refpurpose>
  20  </refnamediv>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 CREATE TABLESPACE <replaceable class="parameter">tablespace_name</replaceable>
  25     [ OWNER { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ]
  26     LOCATION '<replaceable class="parameter">directory</replaceable>'
  27     [ WITH ( <replaceable class="parameter">tablespace_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) ]
  28 </synopsis>
  29  </refsynopsisdiv>
  30
  31  <refsect1>
  32   <title>Description</title>
  33
  34   <para>
  35    <command>CREATE TABLESPACE</command> registers a new cluster-wide
  36    tablespace.  The tablespace name must be distinct from the name of any
  37    existing tablespace in the database cluster.
  38   </para>
  39
  40   <para>
  41    A tablespace allows superusers to define an alternative location on
  42    the file system where the data files containing database objects
  43    (such as tables and indexes) can reside.
  44   </para>
  45
  46   <para>
  47    A user with appropriate privileges can pass
  48    <replaceable class="parameter">tablespace_name</replaceable> to
  49    <command>CREATE DATABASE</command>, <command>CREATE TABLE</command>,
  50    <command>CREATE INDEX</command> or <command>ADD CONSTRAINT</command> to have the data
  51    files for these objects stored within the specified tablespace.
  52   </para>
  53
  54   <warning>
  55    <para>
  56     A tablespace cannot be used independently of the cluster in which it
  57     is defined;  see <xref linkend="manage-ag-tablespaces"/>.
  58    </para>
  59   </warning>
  60
  61  </refsect1>
  62
  63  <refsect1>
  64   <title>Parameters</title>
  65
  66     <variablelist>
  67      <varlistentry>
  68       <term><replaceable class="parameter">tablespace_name</replaceable></term>
  69       <listitem>
  70        <para>
  71         The name of a tablespace to be created.  The name cannot
  72         begin with <literal>pg_</literal>, as such names
  73         are reserved for system tablespaces.
  74        </para>
  75       </listitem>
  76      </varlistentry>
  77
  78      <varlistentry>
  79       <term><replaceable class="parameter">user_name</replaceable></term>
  80       <listitem>
  81        <para>
  82         The name of the user who will own the tablespace.  If omitted,
  83         defaults to the user executing the command.  Only superusers
  84         can create tablespaces, but they can assign ownership of tablespaces
  85         to non-superusers.
  86        </para>
  87       </listitem>
  88      </varlistentry>
  89
  90      <varlistentry>
  91       <term><replaceable class="parameter">directory</replaceable></term>
  92       <listitem>
  93        <para>
  94         The directory that will be used for the tablespace. The directory
  95         must exist (<command>CREATE TABLESPACE</command> will not create it),
  96         should be empty, and must be owned by the
  97         <productname>PostgreSQL</productname> system user.  The directory must be
  98         specified by an absolute path name.
  99        </para>
 100       </listitem>
 101      </varlistentry>
 102
 103      <varlistentry>
 104       <term><replaceable class="parameter">tablespace_option</replaceable></term>
 105       <listitem>
 106        <para>
 107         A tablespace parameter to be set or reset.  Currently, the only
 108         available parameters are <varname>seq_page_cost</varname>,
 109         <varname>random_page_cost</varname>, <varname>effective_io_concurrency</varname>
 110         and <varname>maintenance_io_concurrency</varname>.
 111         Setting these values for a particular tablespace will override the
 112         planner's usual estimate of the cost of reading pages from tables in
 113         that tablespace, and the executor's prefetching behavior, as established
 114         by the configuration parameters of the
 115         same name (see <xref linkend="guc-seq-page-cost"/>,
 116         <xref linkend="guc-random-page-cost"/>,
 117         <xref linkend="guc-effective-io-concurrency"/>,
 118         <xref linkend="guc-maintenance-io-concurrency"/>).  This may be useful if
 119         one tablespace is located on a disk which is faster or slower than the
 120         remainder of the I/O subsystem.
 121        </para>
 122       </listitem>
 123      </varlistentry>
 124   </variablelist>
 125  </refsect1>
 126
 127  <refsect1>
 128   <title>Notes</title>
 129
 130    <para>
 131     <command>CREATE TABLESPACE</command> cannot be executed inside a transaction
 132     block.
 133    </para>
 134  </refsect1>
 135
 136  <refsect1>
 137   <title>Examples</title>
 138
 139   <para>
 140    To create a tablespace <literal>dbspace</literal> at file system location
 141    <literal>/data/dbs</literal>, first create the directory using operating
 142    system facilities and set the correct ownership:
 143 <programlisting>
 144 mkdir /data/dbs
 145 chown postgres:postgres /data/dbs
 146 </programlisting>
 147    Then issue the tablespace creation command inside
 148    <productname>PostgreSQL</productname>:
 149 <programlisting>
 150 CREATE TABLESPACE dbspace LOCATION '/data/dbs';
 151 </programlisting>
 152   </para>
 153
 154   <para>
 155    To create a tablespace owned by a different database user, use a command
 156    like this:
 157 <programlisting>
 158 CREATE TABLESPACE indexspace OWNER genevieve LOCATION '/data/indexes';
 159 </programlisting></para>
 160  </refsect1>
 161
 162  <refsect1>
 163   <title>Compatibility</title>
 164
 165   <para>
 166    <command>CREATE TABLESPACE</command> is a <productname>PostgreSQL</productname>
 167    extension.
 168   </para>
 169  </refsect1>
 170
 171  <refsect1>
 172   <title>See Also</title>
 173
 174   <simplelist type="inline">
 175    <member><xref linkend="sql-createdatabase"/></member>
 176    <member><xref linkend="sql-createtable"/></member>
 177    <member><xref linkend="sql-createindex"/></member>
 178    <member><xref linkend="sql-droptablespace"/></member>
 179    <member><xref linkend="sql-altertablespace"/></member>
 180   </simplelist>
 181  </refsect1>
 182
 183 </refentry>