2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_database.sgml,v 1.19 2001/09/03 12:57:49 petere Exp $
6 <refentry id="SQL-CREATEDATABASE">
8 <refentrytitle id="sql-createdatabase-title">
11 <refmiscinfo>SQL - Language Statements</refmiscinfo>
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>
149 <term><computeroutput>ERROR: Could not initialize database directory.</computeroutput></term>
152 These are most likely related to insufficient permissions on the data
153 directory, a full disk, or other file system problems. The user under
154 which the database server is running must have access to the location.
164 <refsect1 id="R1-SQL-CREATEDATABASE-1">
166 <date>1999-12-11</date>
172 <command>CREATE DATABASE</command> creates a new
173 <productname>Postgres</productname> database.
174 The creator becomes the owner of the new database.
178 An alternate location can be specified in order to,
179 for example, store the database on a different disk.
180 The path must have been prepared with the
181 <xref linkend="APP-INITLOCATION" endterm="APP-INITLOCATION-title">
185 If the path name does not contain a slash, it is interpreted
186 as an environment variable name, which must be known to the
187 server process. This way the database administrator can
188 exercise control over locations in which databases can be created.
189 (A customary choice is, e.g., '<envar>PGDATA2</envar>'.)
190 If the server is compiled with <literal>ALLOW_ABSOLUTE_DBPATHS</literal>
191 (not so by default), absolute path names, as identified by
193 (e.g., '<filename>/usr/local/pgsql/data</filename>'),
198 By default, the new database will be created by cloning the standard
199 system database <literal>template1</>. A different template can be
200 specified by writing <literal>TEMPLATE =</>
201 <replaceable class="parameter">name</replaceable>. In particular,
202 by writing <literal>TEMPLATE = template0</>, you can create a virgin
203 database containing only the standard objects predefined by your
204 version of Postgres. This is useful if you wish to avoid copying
205 any installation-local objects that may have been added to template1.
209 The optional encoding parameter allows selection of the database encoding,
210 if your server was compiled with multibyte encoding support. When not
211 specified, it defaults to the encoding used by the selected template
216 Optional parameters can be written in any order, not only the order
220 <refsect2 id="R2-SQL-CREATEDATABASE-3">
222 <date>1999-12-11</date>
228 <command>CREATE DATABASE</command> is a <productname>Postgres</productname>
232 Use <xref linkend="SQL-DROPDATABASE" endterm="SQL-DROPDATABASE-title"> to remove a database.
235 The program <xref linkend="APP-CREATEDB" endterm="APP-CREATEDB-title"> is a
236 shell script wrapper around this command, provided for convenience.
240 There are security and data integrity issues
241 involved with using alternate database locations
242 specified with absolute path names, and by default
243 only an environment variable known to the backend may be
244 specified for an alternate location.
245 See the Administrator's Guide for more information.
249 comment from Olly; response from Thomas...
251 initlocation does not create a PG_VERSION file in the specified location.
252 How will Postgres handle the situation if it is upgraded to an
253 incompatible database version?
255 Hmm. This isn't an issue since the upgrade would do
256 a dump/reload from the main database area also.
257 Not sure if the dump/reload would guarantee that
258 the alternate data area gets refreshed though...
262 Although it is possible to copy a database other than template1 by
263 specifying its name as the template, this is not (yet) intended as
264 a general-purpose COPY DATABASE facility. In particular, it is
265 essential that the source database be idle (no data-altering transactions
267 for the duration of the copying operation. CREATE DATABASE will check
268 that no backend processes (other than itself) are connected to
269 the source database at the start of the operation, but this does not
270 guarantee that changes cannot be made while the copy proceeds. Therefore,
271 we recommend that databases used as templates be treated as read-only.
275 Two useful flags exist in <literal>pg_database</literal> for each
276 database: <literal>datistemplate</literal> and
277 <literal>datallowconn</literal>. <literal>datistemplate</literal>
278 may be set to indicate that a database is intended as a template for
279 CREATE DATABASE. If this flag is set, the database may be cloned by
280 any user with CREATEDB privileges; if it is not set, only superusers
281 and the owner of the database may clone it.
282 If <literal>datallowconn</literal> is false, then no new connections
283 to that database will be allowed (but existing sessions are not killed
284 simply by setting the flag false). The <literal>template0</literal>
285 database is normally marked this way to prevent modification of it.
289 After preparing a template database, or making any changes to one,
290 it is a good idea to perform
291 <command>VACUUM FREEZE</> or <command>VACUUM FULL FREEZE</> in that
292 database. If this is done when there are no other open transactions
293 in the same database, then it is guaranteed that all tuples in the
294 database are <quote>frozen</> and will not be subject to transaction
295 ID wraparound problems. This is particularly important for a database
296 that will have <literal>datallowconn</literal> set to false, since it
297 will be impossible to do routine maintenance <command>VACUUM</>s on
299 See the Administrator's Guide for more information.
304 <refsect1 id="R1-SQL-CREATEDATABASE-2">
309 To create a new database:
312 <prompt>olly=></prompt> <userinput>create database lusiadas;</userinput>
317 To create a new database in an alternate area <filename>~/private_db</filename>:
320 <prompt>$</prompt> <userinput>mkdir private_db</userinput>
321 <prompt>$</prompt> <userinput>initlocation ~/private_db</userinput>
322 <computeroutput>Creating Postgres database system directory /home/olly/private_db/base</computeroutput>
324 <prompt>$</prompt> <userinput>psql olly</userinput>
326 Welcome to psql, the PostgreSQL interactive terminal.
328 Type: \copyright for distribution terms
329 \h for help with SQL commands
330 \? for help on internal slash commands
331 \g or terminate with semicolon to execute query
334 <prompt>olly=></prompt></computeroutput> <userinput>CREATE DATABASE elsewhere WITH LOCATION = '/home/olly/private_db';</userinput>
335 <computeroutput>CREATE DATABASE</computeroutput>
340 <refsect1 id="R1-SQL-CREATEDATABASE-4">
345 <refsect2 id="R2-SQL-CREATEDATABASE-4">
347 <date>1998-04-15</date>
353 There is no <command>CREATE DATABASE</command> statement in SQL92.
354 Databases are equivalent to catalogs whose creation is implementation-defined.
360 <!-- Keep this comment at the end of the file
365 sgml-minimize-attributes:nil
366 sgml-always-quote-attributes:t
369 sgml-parent-document:nil
370 sgml-default-dtd-file:"../reference.ced"
371 sgml-exposed-tags:nil
372 sgml-local-catalogs:"/usr/lib/sgml/catalog"
373 sgml-local-ecat-files:nil