New in v21.1: The ALTER DATABASE .. ADD REGION statement adds a region to a multi-region database.
ADD REGION is a subcommand of ALTER DATABASE.
In order to add a region with ADD REGION, you must first set a primary database region. For an example showing how to add a primary region, see Set the primary region.
Synopsis
Parameters
| Parameter | Description |
|---|---|
database_name |
The database to which you are adding a region. |
region_name |
The region being added to this database. Allowed values include any region present in SHOW REGIONS FROM CLUSTER. |
Required privileges
The user must be a member of the admin or owner roles, or have the CREATE privilege on the database.
Examples
Set the primary region
To add the first region, or to set an already-added region as the primary region, use the following statement:
ALTER DATABASE foo PRIMARY REGION "us-east1";
ALTER DATABASE PRIMARY REGION
Given a cluster with multiple regions, any databases in that cluster that have not yet had their primary regions set will have their replicas spread as broadly as possible for resiliency. When a primary region is added to one of these databases:
- All tables will be
REGIONAL BY TABLEin the primary region by default. - This means that all such tables will have all of their voting replicas and leaseholders moved to the primary region. This process is known as rebalancing.
For more information about cluster regions and database regions, see Cluster regions and Database regions.
Add a region to a database
To add another region to a database that already has at least one region, use a statement like the following:
ALTER database foo ADD region "us-east1";
ALTER DATABASE ADD REGION
For more information, see Database regions.
Only regions that are defined at node startup time can be added to a multi-region database. For more information, see Cluster regions.
View a database's regions
To view the regions associated with a multi-region database, use a SHOW REGIONS statement:
SHOW REGIONS FROM DATABASE foo;
database | region | primary | zones
-----------+----------+---------+------------------------------------------------
foo | us-east1 | true | {us-east1-b,us-east1-b,us-east1-b,us-east1-b}
(1 row)
For more information, see Database regions.
Drop a region from a database
To drop a region from a multi-region database, use the following statement:
ALTER DATABASE foo DROP REGION "us-east1";
ALTER DATABASE DROP REGION
You can only drop the primary region from a multi-region database if it's the last remaining region. After that, the database will no longer be a multi-region database.
For more information, see Database regions.
Yes
No