CREATE DATABASE

The CREATE DATABASE creates a new CockroachDB database.

The “ statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Required privileges

To create a database, the user must be a member of the admin role or must have the parameter set.

Synopsis

Parameters

Parameter	Description
IF NOT EXISTS	Create a new database only if a database of the same name does not already exist; if one does exist, do not return an error.
name	The name of the database to create, which must be unique and follow these . Cockroach Labs recommends against starting your database name with the string cluster:. If your database name begins with this string, you must append the following to the URI connection string to : &options=-ccluster=system
encoding	The CREATE DATABASE statement accepts an optional ENCODING clause for compatibility with PostgreSQL, but UTF-8 is the only supported encoding. The aliases UTF8 and UNICODE are also accepted. Values should be enclosed in single quotes and are case-insensitive. Example: CREATE DATABASE bank ENCODING = 'UTF-8'.
CONNECTION LIMIT	Supported for compatibility with PostgreSQL. A value of -1 indicates no connection limit. Values other than -1 are currently not supported. By default, CONNECTION LIMIT = -1. (*)
PRIMARY REGION region_name	Create a with region_name as . Allowed values include any region returned by .
REGIONS region_name_list	Create a with region_name_list as . Allowed values include any region returned by . To set database regions at database creation, a primary region must be specified in the same CREATE DATABASE statement.
SURVIVE ZONE FAILURE (Default) SURVIVE REGION FAILURE	Create a with regional failure or zone failure . To set the regional failure survival goal, the database must have at least 3 . Surviving zone failures is the default setting for multi-region databases.

To control the maximum number of non-superuser ( user or other ) connections a can have open at one time, use the server.max_connections_per_gateway . If a new non-superuser connection would exceed this limit, the error message "sorry, too many clients already" is returned, along with error code 53300. This setting may be useful until the CONNECTION LIMIT syntax is fully supported.

Example

Create a database

> CREATE DATABASE bank;

CREATE DATABASE

> SHOW DATABASES;

  database_name | owner | primary_region | regions | survival_goal
----------------+-------+----------------+---------+----------------
  bank          | demo  | NULL           | {}      | NULL
  defaultdb     | root  | NULL           | {}      | NULL
  postgres      | root  | NULL           | {}      | NULL
  system        | node  | NULL           | {}      | NULL
(4 rows)

Create fails (name already in use)

> CREATE DATABASE bank;

ERROR: database "bank" already exists
SQLSTATE: 42P04

> CREATE DATABASE IF NOT EXISTS bank;

CREATE DATABASE

SQL does not generate an error, but instead responds CREATE DATABASE even though a new database wasn’t created.

> SHOW DATABASES;

  database_name | owner | primary_region | regions | survival_goal
----------------+-------+----------------+---------+----------------
  bank          | demo  | NULL           | {}      | NULL
  defaultdb     | root  | NULL           | {}      | NULL
  postgres      | root  | NULL           | {}      | NULL
  system        | node  | NULL           | {}      | NULL
(4 rows)

Create a multi-region database

Suppose you start a cluster with region and zone .

For this example, let’s use a , with the to simulate a multi-region cluster:

cockroach demo --nodes=6 --demo-locality=region=us-east1,zone=us-east1-a:region=us-east1,zone=us-east1-b:region=us-central1,zone=us-central1-a:region=us-central1,zone=us-central1-b:region=us-west1,zone=us-west1-a:region=us-west1,zone=us-west1-b --no-example-database

> SHOW REGIONS;

    region    |             zones             | database_names | primary_region_of
--------------+-------------------------------+----------------+--------------------
  us-central1 | {us-central1-a,us-central1-b} | {}             | {}
  us-east1    | {us-east1-a,us-east1-b}       | {}             | {}
  us-west1    | {us-west1-a,us-west1-b}       | {}             | {}
(3 rows)

If regions are set at cluster start-up, you can create multi-region databases in the cluster that use the cluster regions.

Use the following command to specify regions and survival goals at database creation:

> CREATE DATABASE bank PRIMARY REGION "us-east1" REGIONS "us-east1", "us-central1", "us-west1" SURVIVE REGION FAILURE;

> SHOW DATABASES;

  database_name | owner | primary_region |             regions             | survival_goal
----------------+-------+----------------+---------------------------------+----------------
  bank          | demo  | us-east1       | {us-central1,us-east1,us-west1} | region
  defaultdb     | root  | NULL           | {}                              | NULL
  postgres      | root  | NULL           | {}                              | NULL
  system        | node  | NULL           | {}                              | NULL
(4 rows)

> SHOW REGIONS FROM DATABASE bank;

  database |   region    | primary |             zones
-----------+-------------+---------+--------------------------------
  bank     | us-east1    |  true   | {us-east1-a,us-east1-b}
  bank     | us-central1 |  false  | {us-central1-a,us-central1-b}
  bank     | us-west1    |  false  | {us-west1-a,us-west1-b}
(3 rows)

Create a multi-region database with a secondary region

You can add a to a for failover purposes. If the fails, the secondary region becomes the new primary region.

To add a secondary region during database creation, use the following steps:

1. Start a cockroach demo cluster as described in the example Create a multi-region database.

2. Issue a CREATE DATABASE statement like the following. It is the same as in the Create a multi-region database example, except that it adds a SECONDARY REGION {region} clause:

CREATE DATABASE bank PRIMARY REGION "us-east1" REGIONS "us-east1", "us-central1", "us-west1" SURVIVE REGION FAILURE SECONDARY REGION "us-west1";

CREATE DATABASE

For more information about secondary regions, see .

are not compatible with databases containing tables. CockroachDB does not prevent you from defining secondary regions on databases with regional by row tables, but the interaction of these features is not supported.

Therefore, Cockroach Labs recommends that you avoid defining secondary regions on databases that use regional by row table configurations.

See also

• .