The CREATE DATABASE
statement creates a new CockroachDB database.
The CREATE DATABASE
statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Required privileges
To create a database, the user must be a member of the admin
role or must have the CREATEDB
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 identifier rules. 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 connect to the cluster: &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 multi-region database with region_name as the primary region.Allowed values include any region returned by SHOW REGIONS FROM CLUSTER . |
REGIONS region_name_list |
Create a multi-region database with region_name_list as database regions.Allowed values include any region returned by SHOW REGIONS FROM CLUSTER .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 multi-region database with regional failure or zone failure survival goals. To set the regional failure survival goal, the database must have at least 3 database regions. Surviving zone failures is the default setting for multi-region databases. |
*
To control the maximum number of non-superuser (root
user or other admin
role) connections a gateway node can have open at one time, use the server.max_connections_per_gateway
cluster setting. 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
This is an enterprise-only feature. Request a 30-day trial license to try it out.
Suppose you start a cluster with region and zone localities specified at startup.
For this example, let's use a demo cluster, with the --demo-locality
flag 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
This is an enterprise-only feature. Request a 30-day trial license to try it out.
You can add a secondary region to a multi-region database for failover purposes. If the primary region fails, the secondary region becomes the new primary region.
To add a secondary region during database creation, use the following steps:
Start a
cockroach demo
cluster as described in the example Create a multi-region database.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 aSECONDARY 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 Secondary regions.