codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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>CREATE TABLESPACE</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="sql-createtableas.html" title="CREATE TABLE AS" /><link rel="next" href="sql-createtsconfig.html" title="CREATE TEXT SEARCH CONFIGURATION" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE TABLESPACE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createtableas.html" title="CREATE TABLE AS">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createtsconfig.html" title="CREATE TEXT SEARCH CONFIGURATION">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATETABLESPACE"><div class="titlepage"></div><a id="id-1.9.3.87.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE TABLESPACE</span></h2><p>CREATE TABLESPACE — define a new tablespace</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3CREATE TABLESPACE <em class="replaceable"><code>tablespace_name</code></em>4    [ OWNER { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ]5    LOCATION '<em class="replaceable"><code>directory</code></em>'6    [ WITH ( <em class="replaceable"><code>tablespace_option</code></em> = <em class="replaceable"><code>value</code></em> [, ... ] ) ]7</pre></div><div class="refsect1" id="id-1.9.3.87.5"><h2>Description</h2><p>8   <code class="command">CREATE TABLESPACE</code> registers a new cluster-wide9   tablespace.  The tablespace name must be distinct from the name of any10   existing tablespace in the database cluster.11  </p><p>12   A tablespace allows superusers to define an alternative location on13   the file system where the data files containing database objects14   (such as tables and indexes) can reside.15  </p><p>16   A user with appropriate privileges can pass17   <em class="replaceable"><code>tablespace_name</code></em> to18   <code class="command">CREATE DATABASE</code>, <code class="command">CREATE TABLE</code>,19   <code class="command">CREATE INDEX</code> or <code class="command">ADD CONSTRAINT</code> to have the data20   files for these objects stored within the specified tablespace.21  </p><div class="warning"><h3 class="title">Warning</h3><p>22    A tablespace cannot be used independently of the cluster in which it23    is defined;  see <a class="xref" href="manage-ag-tablespaces.html" title="23.6. Tablespaces">Section 23.6</a>.24   </p></div></div><div class="refsect1" id="id-1.9.3.87.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>tablespace_name</code></em></span></dt><dd><p>25        The name of a tablespace to be created.  The name cannot26        begin with <code class="literal">pg_</code>, as such names27        are reserved for system tablespaces.28       </p></dd><dt><span class="term"><em class="replaceable"><code>user_name</code></em></span></dt><dd><p>29        The name of the user who will own the tablespace.  If omitted,30        defaults to the user executing the command.  Only superusers31        can create tablespaces, but they can assign ownership of tablespaces32        to non-superusers.33       </p></dd><dt><span class="term"><em class="replaceable"><code>directory</code></em></span></dt><dd><p>34        The directory that will be used for the tablespace. The directory35        must exist (<code class="command">CREATE TABLESPACE</code> will not create it),36        should be empty, and must be owned by the37        <span class="productname">PostgreSQL</span> system user.  The directory must be38        specified by an absolute path name.39       </p></dd><dt><span class="term"><em class="replaceable"><code>tablespace_option</code></em></span></dt><dd><p>40        A tablespace parameter to be set or reset.  Currently, the only41        available parameters are <code class="varname">seq_page_cost</code>,42        <code class="varname">random_page_cost</code>, <code class="varname">effective_io_concurrency</code>43        and <code class="varname">maintenance_io_concurrency</code>.44        Setting these values for a particular tablespace will override the45        planner's usual estimate of the cost of reading pages from tables in46        that tablespace, and the executor's prefetching behavior, as established47        by the configuration parameters of the48        same name (see <a class="xref" href="runtime-config-query.html#GUC-SEQ-PAGE-COST">seq_page_cost</a>,49        <a class="xref" href="runtime-config-query.html#GUC-RANDOM-PAGE-COST">random_page_cost</a>,50        <a class="xref" href="runtime-config-resource.html#GUC-EFFECTIVE-IO-CONCURRENCY">effective_io_concurrency</a>,51        <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-IO-CONCURRENCY">maintenance_io_concurrency</a>).  This may be useful if52        one tablespace is located on a disk which is faster or slower than the53        remainder of the I/O subsystem.54       </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.87.7"><h2>Notes</h2><p>55    <code class="command">CREATE TABLESPACE</code> cannot be executed inside a transaction56    block.57   </p></div><div class="refsect1" id="id-1.9.3.87.8"><h2>Examples</h2><p>58   To create a tablespace <code class="literal">dbspace</code> at file system location59   <code class="literal">/data/dbs</code>, first create the directory using operating60   system facilities and set the correct ownership:61</p><pre class="programlisting">62mkdir /data/dbs63chown postgres:postgres /data/dbs64</pre><p>65   Then issue the tablespace creation command inside66   <span class="productname">PostgreSQL</span>:67</p><pre class="programlisting">68CREATE TABLESPACE dbspace LOCATION '/data/dbs';69</pre><p>70  </p><p>71   To create a tablespace owned by a different database user, use a command72   like this:73</p><pre class="programlisting">74CREATE TABLESPACE indexspace OWNER genevieve LOCATION '/data/indexes';75</pre></div><div class="refsect1" id="id-1.9.3.87.9"><h2>Compatibility</h2><p>76   <code class="command">CREATE TABLESPACE</code> is a <span class="productname">PostgreSQL</span>77   extension.78  </p></div><div class="refsect1" id="id-1.9.3.87.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createdatabase.html" title="CREATE DATABASE"><span class="refentrytitle">CREATE DATABASE</span></a>, <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a>, <a class="xref" href="sql-createindex.html" title="CREATE INDEX"><span class="refentrytitle">CREATE INDEX</span></a>, <a class="xref" href="sql-droptablespace.html" title="DROP TABLESPACE"><span class="refentrytitle">DROP TABLESPACE</span></a>, <a class="xref" href="sql-altertablespace.html" title="ALTER TABLESPACE"><span class="refentrytitle">ALTER TABLESPACE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createtableas.html" title="CREATE TABLE AS">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createtsconfig.html" title="CREATE TEXT SEARCH CONFIGURATION">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE TABLE AS </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE TEXT SEARCH CONFIGURATION</td></tr></table></div></body></html>