2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_database.sgml,v 1.16 2000/11/14 18:37:40 tgl Exp $
6 <refentry id="SQL-CREATEDATABASE">
8 <refentrytitle id="sql-createdatabase-title">
11 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 Creates a new database
23 <date>1999-12-11</date>
26 CREATE DATABASE <replaceable class="PARAMETER">name</replaceable>
27 [ WITH [ LOCATION = '<replaceable class="parameter">dbpath</replaceable>' ]
28 [ TEMPLATE = <replaceable class="parameter">template</replaceable> ]
29 [ ENCODING = <replaceable class="parameter">encoding</replaceable> ] ]
32 <refsect2 id="R2-SQL-CREATEDATABASE-1">
34 <date>1999-12-11</date>
43 <term><replaceable class="parameter">name</replaceable></term>
46 The name of a database to create.
51 <term><replaceable class="parameter">dbpath</replaceable></term>
54 An alternate filesystem location in which to store the new database,
55 specified as a string literal;
56 or <literal>DEFAULT</literal> to use the default location.
61 <term><replaceable class="parameter">template</replaceable></term>
64 Name of template from which to create the new database,
65 or <literal>DEFAULT</literal> to use the default template
66 (<literal>template1</literal>).
71 <term><replaceable class="parameter">encoding</replaceable></term>
74 Multibyte encoding method to use in the new database. Specify
75 a string literal name (e.g., <literal>'SQL_ASCII'</literal>),
76 or an integer encoding number, or <literal>DEFAULT</literal>
77 to use the default encoding.
85 <refsect2 id="R2-SQL-CREATEDATABASE-2">
87 <date>1999-12-11</date>
96 <term><computeroutput>CREATE DATABASE</computeroutput></term>
99 Message returned if the command completes successfully.
105 <term><computeroutput>ERROR: user '<replaceable class="parameter">username</replaceable>' is not allowed to create/drop databases</computeroutput></term>
108 You must have the special CREATEDB privilege to create databases.
109 See <xref linkend="SQL-CREATEUSER" endterm="SQL-CREATEUSER-title">.
116 <term><computeroutput>ERROR: createdb: database "<replaceable class="parameter">name</replaceable>" already exists</computeroutput></term>
119 This occurs if a database with the <replaceable class="parameter">name</replaceable>
120 specified already exists.
126 <term><computeroutput>ERROR: database path may not contain single quotes</computeroutput></term>
129 The database location
130 <replaceable class="parameter">dbpath</replaceable> cannot contain
131 single quotes. This is required so that the shell commands that
132 create the database directory can execute safely.
138 <term><computeroutput>ERROR: CREATE DATABASE: may not be called in a transaction block</computeroutput></term>
141 If you have an explicit transaction block in progress you cannot call
142 <command>CREATE DATABASE</command>. You must finish the transaction first.
148 <term><computeroutput>ERROR: Unable to create database directory '<replaceable>path</replaceable>'.</computeroutput></term>
152 <term><computeroutput>ERROR: Could not initialize database directory.</computeroutput></term>
155 These are most likely related to insufficient permissions on the data
156 directory, a full disk, or other file system problems. The user under
157 which the database server is running must have access to the location.
167 <refsect1 id="R1-SQL-CREATEDATABASE-1">
169 <date>1999-12-11</date>
175 <command>CREATE DATABASE</command> creates a new
176 <productname>Postgres</productname> database.
177 The creator becomes the owner of the new database.
181 An alternate location can be specified in order to,
182 for example, store the database on a different disk.
183 The path must have been prepared with the
184 <xref linkend="APP-INITLOCATION" endterm="APP-INITLOCATION-title">
188 If the path name does not contain a slash, it is interpreted
189 as an environment variable name, which must be known to the
190 server process. This way the database administrator can
191 exercise control over locations in which databases can be created.
192 (A customary choice is, e.g., '<envar>PGDATA2</envar>'.)
193 If the server is compiled with <literal>ALLOW_ABSOLUTE_DBPATHS</literal>
194 (not so by default), absolute path names, as identified by
196 (e.g., '<filename>/usr/local/pgsql/data</filename>'),
201 By default, the new database will be created by cloning the standard
202 system database <literal>template1</>. A different template can be
203 specified by writing <literal>TEMPLATE =</>
204 <replaceable class="parameter">name</replaceable>. In particular,
205 by writing <literal>TEMPLATE = template0</>, you can create a virgin
206 database containing only the standard objects predefined by your
207 version of Postgres. This is useful if you wish to avoid copying
208 any installation-local objects that may have been added to template1.
212 The optional encoding parameter allows selection of the database encoding,
213 if your server was compiled with multibyte encoding support. When not
214 specified, it defaults to the encoding used by the selected template
219 Optional parameters can be written in any order, not only the order
223 <refsect2 id="R2-SQL-CREATEDATABASE-3">
225 <date>1999-12-11</date>
231 <command>CREATE DATABASE</command> is a <productname>Postgres</productname>
235 Use <xref linkend="SQL-DROPDATABASE" endterm="SQL-DROPDATABASE-title"> to remove a database.
238 The program <xref linkend="APP-CREATEDB" endterm="APP-CREATEDB-title"> is a
239 shell script wrapper around this command, provided for convenience.
243 There are security and data integrity issues
244 involved with using alternate database locations
245 specified with absolute path names, and by default
246 only an environment variable known to the backend may be
247 specified for an alternate location.
248 See the Administrator's Guide for more information.
252 comment from Olly; response from Thomas...
254 initlocation does not create a PG_VERSION file in the specified location.
255 How will Postgres handle the situation if it is upgraded to an
256 incompatible database version?
258 Hmm. This isn't an issue since the upgrade would do
259 a dump/reload from the main database area also.
260 Not sure if the dump/reload would guarantee that
261 the alternate data area gets refreshed though...
265 Although it is possible to copy a database other than template1 by
266 specifying its name as the template, this is not (yet) intended as
267 a general-purpose COPY DATABASE facility. In particular, it is
268 essential that the source database be idle (no data-altering transactions
270 for the duration of the copying operation. CREATE DATABASE will check
271 that no backend processes (other than itself) are connected to
272 the source database at the start of the operation, but this does not
273 guarantee that changes cannot be made while the copy proceeds. Therefore,
274 we recommend that databases used as templates be treated as read-only.
278 Two useful flags exist in <literal>pg_database</literal> for each
279 database: <literal>datistemplate</literal> and
280 <literal>datallowconn</literal>. <literal>datistemplate</literal>
281 may be set to indicate that a database is intended as a template for
282 CREATE DATABASE. If this flag is set, the database may be cloned by
283 any user with CREATEDB privileges; if it is not set, only superusers
284 and the owner of the database may clone it.
285 If <literal>datallowconn</literal> is false, then no new connections
286 to that database will be allowed (but existing sessions are not killed
287 simply by setting the flag false). The <literal>template0</literal>
288 database is normally marked this way to prevent modification of it.
293 <refsect1 id="R1-SQL-CREATEDATABASE-2">
298 To create a new database:
301 <prompt>olly=></prompt> <userinput>create database lusiadas;</userinput>
306 To create a new database in an alternate area <filename>~/private_db</filename>:
309 <prompt>$</prompt> <userinput>mkdir private_db</userinput>
310 <prompt>$</prompt> <userinput>initlocation ~/private_db</userinput>
311 <computeroutput>Creating Postgres database system directory /home/olly/private_db/base</computeroutput>
313 <prompt>$</prompt> <userinput>psql olly</userinput>
315 Welcome to psql, the PostgreSQL interactive terminal.
317 Type: \copyright for distribution terms
318 \h for help with SQL commands
319 \? for help on internal slash commands
320 \g or terminate with semicolon to execute query
323 <prompt>olly=></prompt></computeroutput> <userinput>CREATE DATABASE elsewhere WITH LOCATION = '/home/olly/private_db';</userinput>
324 <computeroutput>CREATE DATABASE</computeroutput>
329 <refsect1 id="R1-SQL-CREATEDATABASE-4">
334 <refsect2 id="R2-SQL-CREATEDATABASE-4">
336 <date>1998-04-15</date>
342 There is no <command>CREATE DATABASE</command> statement in SQL92.
343 Databases are equivalent to catalogs whose creation is implementation-defined.
349 <!-- Keep this comment at the end of the file
354 sgml-minimize-attributes:nil
355 sgml-always-quote-attributes:t
358 sgml-parent-document:nil
359 sgml-default-dtd-file:"../reference.ced"
360 sgml-exposed-tags:nil
361 sgml-local-catalogs:"/usr/lib/sgml/catalog"
362 sgml-local-ecat-files:nil