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. |
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. |
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)