Creating a Sharded PostgreSQL cluster

If you do not have the Yandex Cloud CLI yet, install and initialize it.

The folder used by default is the one specified when creating the CLI profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also specify a different folder for any command using --folder-name or --folder-id.

If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

To create a Managed Service for Sharded PostgreSQL cluster:

1. View the description of the CLI command for creating a cluster:

   yc managed-sharded-postgresql cluster create --help
   

2. Specify the cluster settings in the cluster create command. Note that our example does not contain all available settings:

   For a cluster with standard sharding

   yc managed-sharded-postgresql cluster create \
      --name <cluster_name> \
      --environment <environment> \
      --network-name <network_name> \
      --security-group-ids <security_group_IDs> \
      --host type=infra,`
            `zone-id=<availability_zone>,`
            `subnet-id=<subnet_ID>,`
            `assign-public-ip=<allow_public_access_to_host> \
      --infra-resource-preset <INFRA_host_class> \
      --infra-disk-size <storage_size_in_GB> \
      --infra-disk-type <disk_type> \
      --database name=<DB_name> \
      --user name=<username>,`
            `password=<password>,`
            `permission=<DB_name>,`
            `connection-limit=<maximum_number_of_user_connections>,`
            `connection-retries=<maximum_connection_retry_attempts> \
      --deletion-protection=<protect_cluster_from_deletion>
   
   For a cluster with advanced sharding

   yc managed-sharded-postgresql cluster create \
      --name <cluster_name> \
      --environment <environment> \
      --network-name <network_name> \
      --security-group-ids <security_group_IDs> \
      --host type=router,`
            `zone-id=<availability_zone>,`
            `subnet-id=<subnet_ID>,`
            `assign-public-ip=<allow_public_access_to_host> \
      --router-resource-preset <router_host_class> \
      --router-disk-size <storage_size_in_GB> \
      --router-disk-type <disk_type> \
      --host type=coordinator,`
            `zone-id=<availability_zone>,`
            `subnet-id=<subnet_ID>,`
            `assign-public-ip=<allow_public_access_to_host> \
      --coordinator-resource-preset <coordinator_host_class> \
      --coordinator-disk-size <storage_size_in_GB> \
      --coordinator-disk-type <disk_type> \
      --database name=<DB_name> \
      --user name=<username>,`
            `password=<password>,`
            `permission=<DB_name>,`
            `connection-limit=<maximum_number_of_user_connections>,`
            `connection-retries=<maximum_connection_retry_attempts> \
      --deletion-protection=<protect_cluster_from_deletion>
   

   Where:

   • --name: Cluster name.

   • --environment: Cluster environment, production or prestable.

   • --network-name: Name of the network the cluster will be deployed in.

     Warning

     The cloud network selected for cluster deployment cannot be changed later. If you need to migrate your cluster to another cloud network in the future, use the restore from a backup feature and specify the required network for your new cluster copy.

   • --security-group-ids: Security group IDs, comma-separated.

     Warning

     For the router to be able to connect to your shard hosts, the Managed Service for Sharded PostgreSQL cluster and the shards and must be in the same security group that allows incoming and outgoing TCP connections to port 6432.

   • --host: Cluster host settings. This parameter is specified separately for each host and has the following structure:

     • type: Host type. Its possible values are:

       • router: Router in a cluster with advanced sharding.
       • coordinator: Coordinator in a cluster with advanced sharding.
       • infra: INFRA host in a cluster with standard sharding.

     • zone-id: Availability zone.

     • subnet-id: Subnet ID.

     • assign-public-ip: Internet access to the host via a public IP address, true or false.

   • --router-resource-preset: Class of router hosts.

   • --router-disk-size: Router disk size, in GB.

   • --router-disk-type: Router disk type.

   • --coordinator-resource-preset: Class of coordinator hosts.

   • --coordinator-disk-size: Coordinator disk size, in GB.

   • --coordinator-disk-type: Coordinator disk type.

   • --infra-resource-preset: INFRA host class.

   • --infra-disk-size: INFRA disk size, in GB.

   • --infra-disk-type: INFRA disk type.

   • --database: Database settings:

     • name: Database name.

     Specify a separate --database property for each database.

   • --user: User settings. This parameter is specified separately for each user and has the following structure:

     • name: Username.

       The username may contain Latin letters, numbers, hyphens, and underscores. It must start with a letter, number, or underscore, but cannot begin with a pg_ prefix. The name may be up to 63 characters long.

       The following names are reserved: admin, repl, monitor, postgres, mdb_admin, mdb_monitor, mdb_replication. You cannot create users with these names.

     • password: User password. The password must be from 8 to 128 characters long.

     • permission: Name of the database the user can access.

       Provide a separate permission property for each database you want the user to access.

     • connection-limit: Maximum number of concurrent user connections.

     • connection-retries: Maximum number of connection retry attempts.

   • --deletion-protection: Cluster deletion protection, true or false.

     Once cluster deletion protection is enabled, it also applies to all new and existing databases and users for whom deletion protection is not set explicitly. If a database or user has its own deletion protection setting, it will override the cluster setting.

     Even with deletion protection enabled, one can still connect to the cluster manually and delete the data.

   1. To set up the backup window, provide the following parameters:

      yc managed-sharded-postgresql cluster create \
         ...
         --backup-window-start <backup_start_time> \
         --backup-retain-period-days <automatic_backup_retention_period_in_days> \
         ...
      

      Where:

      • --backup-window-start: Cluster backup start time (UTC) in HH:MM:SS format.
      • --backup-retain-period-days: Automatic backup retention period. Possible values: between 7 and 60 days. The default is 7 days.

With Terraform, you can quickly create a cloud infrastructure in Yandex Cloud and manage it using configuration files. These files store the infrastructure description written in HashiCorp Configuration Language (HCL). If you change the configuration files, Terraform automatically detects which part of your configuration is already deployed, and what should be added or removed.

Terraform is distributed under the Business Source License. The Yandex Cloud provider for Terraform is distributed under the MPL-2.0 license.

For more information about the provider resources, see the guides on the Terraform website or its mirror.

If you do not have Terraform yet, install it and configure the Yandex Cloud provider.

To manage infrastructure using Terraform under a service account or user accounts (a Yandex account, a federated account, or a local user), authenticate using the appropriate method.

To create a Managed Service for Sharded PostgreSQL cluster:

1. In the configuration file, describe the properties of the following resources:

   • Managed Service for Sharded PostgreSQL cluster
   • Cluster database
   • Cluster user

   • Network: Description of the cloud network to host the cluster. If you already have a suitable network, you do not need to describe it again.

   • Subnets: Description of the subnets to connect the cluster hosts to. If you already have suitable subnets, you do not need to describe them again.

   Configuration file structure example:

   For a cluster with standard sharding

   resource "yandex_mdb_sharded_postgresql_cluster" "<local_cluster_name>" {
     name                = "<cluster_name>"
     environment         = "<environment>"
     network_id          = yandex_vpc_network.<local_network_name>.id
     security_group_ids  = ["<security_group_IDs>"]
     deletion_protection = <protect_cluster_from_deletion>
   
     config = {
       sharded_postgresql_config = {
         infra = {
           resources = {
             resource_preset_id = "<INFRA_host_class>"
             disk_size          = <storage_size_in_GB>
             disk_type_id       = "<disk_type>"
           }
         }
       }
     }
   
     hosts = {
       <host_name> = {
         type             = "INFRA"
         zone             = "<availability_zone>"
         subnet_id        = yandex_vpc_subnet.<local_subnet_name>.id
         assign_public_ip = <allow_public_access_to_host>
       }
     } 
   }
   
   resource "yandex_mdb_sharded_postgresql_database" "<local_DB_name>" {
     cluster_id = yandex_mdb_sharded_postgresql_cluster.<local_cluster_name>.id
     name       = "<DB_name>"
   }
   
   resource "yandex_mdb_sharded_postgresql_user" "<local_username>" {
     cluster_id = yandex_mdb_sharded_postgresql_cluster.<local_cluster_name>.id
     name       = "<username>"
     password   = "<password>"
   
     permissions {
       database = yandex_mdb_sharded_postgresql_database.<local_DB_name>.name
     }
   
     settings = {
       connection_limit   = <maximum_number_of_user_connections>
       connection_retries = <maximum_number_of_connection_retry_attempts>
     }
   }
   
   resource "yandex_vpc_network" "<local_network_name>" {
     name = "<network_name>"
   }
   
   resource "yandex_vpc_subnet" "<local_subnet_name>" {
     name           = "<subnet_name>"
     zone           = "<availability_zone>"
     network_id     = yandex_vpc_network.<local_network_name>.id
     v4_cidr_blocks = ["<IP_address_range>"]
   }
   
   For a cluster with advanced sharding

   resource "yandex_mdb_sharded_postgresql_cluster" "<local_cluster_name>" {
     name                = "<cluster_name>"
     environment         = "<environment>"
     network_id          = yandex_vpc_network.<local_network_name>.id
     security_group_ids  = ["<security_group_IDs>"]
     deletion_protection = <protect_cluster_from_deletion>
   
     config = {
       sharded_postgresql_config = {
         router = {
           resources = {
             resource_preset_id = "<router_host_class>"
             disk_size          = <storage_size_in_GB>
             disk_type_id       = "<disk_type>"
           }
         }
         
         coordinator = {
           resources = {
             resource_preset_id = "<coordinator_host_class>"
             disk_size          = <storage_size_in_GB>
             disk_type_id       = "<disk_type>"
           }
         }
       }
     }
   
     hosts = {
       <host_1_name> = {
         type             = "ROUTER"
         zone             = "<availability_zone>"
         subnet_id        = yandex_vpc_subnet.<local_subnet_name>.id
         assign_public_ip = <allow_public_access_to_host>
       }
   
       <host_2_name> = {
         type             = "COORDINATOR"
         zone             = "<availability_zone>"
         subnet_id        = yandex_vpc_subnet.<local_subnet_name>.id
         assign_public_ip = <allow_public_access_to_host>
       }
     } 
   }
   
   resource "yandex_mdb_sharded_postgresql_database" "<local_DB_name>" {
     cluster_id = yandex_mdb_sharded_postgresql_cluster.<local_cluster_name>.id
     name       = "<DB_name>"
   }
   
   resource "yandex_mdb_sharded_postgresql_user" "<local_username>" {
     cluster_id = yandex_mdb_sharded_postgresql_cluster.<local_cluster_name>.id
     name       = "<username>"
     password   = "<password>"
   
     permissions {
       database = yandex_mdb_sharded_postgresql_database.<local_DB_name>.name
     }
   
     settings = {
       connection_limit   = <number_of_user_connections>
       connection_retries = <number_of_retries_when_connecting>
     }
   }
   
   resource "yandex_vpc_network" "<local_network_name>" {
     name = "<network_name>"
   }
   
   resource "yandex_vpc_subnet" "<local_subnet_name>" {
     name           = "<subnet_name>"
     zone           = "<availability_zone>"
     network_id     = yandex_vpc_network.<local_network_name>.id
     v4_cidr_blocks = ["<IP_address_range>"]
   }
   

   Where:

   • name: Cluster name.

   • environment: Cluster environment, PRODUCTION or PRESTABLE.

   • network_id: ID of the network the cluster will be deployed in.

     Warning

     The cloud network selected for cluster deployment cannot be changed later. If you need to migrate your cluster to another cloud network in the future, use the restore from a backup feature and specify the required network for your new cluster copy.

   • security_group_ids: List of security group IDs.

     Warning

     For the router to be able to connect to your shard hosts, the Managed Service for Sharded PostgreSQL cluster and the shards and must be in the same security group that allows incoming and outgoing TCP connections to port 6432.

   • deletion_protection: Cluster deletion protection, true or false.

     Once cluster deletion protection is enabled, it also applies to all new and existing databases and users for whom deletion protection is not set explicitly. If a database or user has its own deletion protection setting, it will override the cluster setting.

     Even with deletion protection enabled, one can still connect to the cluster manually and delete the data.

   • hosts: Cluster hosts as an associative array of elements. The key specifies the host name, and the value, the host parameters. Each element in the array has the following structure:

     • type: Host type. Its possible values are:

       • ROUTER: Router in a cluster with advanced sharding.
       • COORDINATOR: Coordinator in a cluster with advanced sharding.
       • INFRA: INFRA host in a cluster with standard sharding.

     • zone: Availability zone.

     • subnet_id: Subnet ID.

     • assign_public_ip: Internet access to the host via a public IP address, true or false.

   • config.sharded_postgresql_config.router.resources: Router resource parameters in a cluster with advanced sharding:

     • resource_preset_id: Host class.
     • disk_size: Disk size, in GB.
     • disk_type_id: Disk type.

   • config.sharded_postgresql_config.coordinator.resources: Coordinator resource parameters in a cluster with advanced sharding:

     • resource_preset_id: Host class.
     • disk_size: Disk size, in GB.
     • disk_type_id: Disk type.

   • config.sharded_postgresql_config.infra.resources: INFRA host resource parameters in a cluster with standard sharding:

     • resource_preset_id: Host class.
     • disk_size: Disk size, in GB.
     • disk_type_id: Disk type.

   1. To set up the backup window, provide the config section to the following parameters:

      resource "yandex_mdb_sharded_postgresql_cluster" "<local_cluster_name>" {
        ...
        config = {
          backup_window_start = {
            hours   = <hours>
            minutes = <minutes>
          }
      
          backup_retain_period_days = <automatic_backup_retention_period_in_days>
          ...
        }
        ...
      }
      

      Where:

      • backup_window_start: Cluster backup start time (UTC):

        • hours: From 0 to 23 hours.
        • minutes: From 0 to 59 minutes.

      • backup_retain_period_days: Automatic backup retention period. Possible values: between 7 and 60 days. The default is 7 days.

   2. To set the maintenance window that will also apply to stopped clusters, add the maintenance_window section to the cluster description:

      resource "yandex_mdb_sharded_postgresql_cluster" "<cluster_name>" {
        ...
        maintenance_window {
          type = "<maintenance_type>"
          day  = "<day_of_week>"
          hour = <sequence_number_of_hour_interval>
        }
        ...
      }
      

      Where:

      • type: Maintenance type. The possible values include:

        • ANYTIME: Any time (default).
        • WEEKLY: On a schedule.

      • day: Day of week for the WEEKLY type, i.e., MON, TUE, WED, THU, FRI, SAT, or SUN.

      • hour: Sequence number of UTC hour interval for the WEEKLY type, from 1 to 24.

        For example, 1 stands for the interval from 00:00 to 01:00, and 5, from 04:00 to 05:00.

   For more information about the yandex_mdb_sharded_postgresql_cluster resource, see this Terraform provider guide.

2. Validate your Terraform configuration:

   1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

   2. Run this command:

      terraform validate
      

      Terraform will show any errors found in your configuration files.

3. Create a cluster:

   1. Run this command to view the intended changes:

      terraform plan
      

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

   2. If everything looks correct, apply the changes:

      1. Run this command:

         terraform apply
         

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

   All the required resources will be created in the specified folder. You can check resource availability and their settings in the management console.

   Timeouts

   The Terraform provider sets the following timeouts for Managed Service for Sharded PostgreSQL cluster operations:

   • Creating a cluster, including by restoring it from a backup: 30 minutes.
   • Updating a cluster: 60 minutes.
   • Deleting a cluster: 15 minutes.

   Operations exceeding the timeout are aborted.

   How to change these limits

   Add the timeouts section to your cluster description, such as the following:

   resource "yandex_mdb_sharded_postgresql_cluster" "<cluster_name>" {
     ...
     timeouts {
       create = "1h30m" # 1 hour 30 minutes
       update = "2h"    # 2 hours
       delete = "30m"   # 30 minutes
     }
   }
   

1. Get an IAM token for API authentication and put it into an environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Create a file named body.json and paste the following code into it:

   {
     "folderId": "<folder_ID>",
     "name": "<cluster_name>",
     "description": "<description>",
     "environment": "<environment>",
     "securityGroupIds": [
       "<security_group_1_ID>",
       "<security_group_2_ID>",
       ...
       "<security_group_N_ID>"
     ],
     "networkId": "<network_ID>",
     "deletionProtection": <protect_cluster_from_deletion>,
     "configSpec": {
       "spqrSpec": {
         "router": {
           "config": {
             "showNoticeMessages": <show_information_notifications>,
             "timeQuantiles": [
               <list_of_time_quantiles_for_displaying_statistics>
             ],
             "defaultRouteBehavior": "<allow_multishard_requests>",
             "preferSameAvailabilityZone": <routing_priority_to_router_availability_zone>
           },
           "resources": {
             "resourcePresetId": "<router_host_class>",
             "diskSize": "<storage_size_in_bytes>",
             "diskTypeId": "<disk_type>"
           }
         },
         "coordinator": {
           "resources": {
             "resourcePresetId": "<coordinator_host_class>",
             "diskSize": "<storage_size_in_bytes>",
             "diskTypeId": "<disk_type>"
           }
         },
         "infra": {
           "router": {
             "showNoticeMessages": <show_information_notifications>,
             "time_quantiles": [
               <list_of_time_quantiles_for_displaying_statistics>
             ],
             "defaultRouteBehavior": "<allow_multishard_requests>",
             "preferSameAvailabilityZone": <routing_priority_to_router_availability_zone>
           },
           "resources": {
             "resourcePresetId": "<INFRA_host_class>",
             "diskSize": "<storage_size_in_bytes>",
             "diskTypeId": "<disk_type>"
           }
         },
         "consolePassword": "<Sharded_PostgreSQL_console_password>",
         "logLevel": "<logging_level>"
       },
       "backupWindowStart": {
         "hours": "<hours>",
         "minutes": "<minutes>",
         "seconds": "<seconds>",
         "nanos": "<nanoseconds>"
       },
       "backupRetainPeriodDays": "<number_of_days>",
     },
     "databaseSpecs": [
       {
         "name": "<DB_name>"
       },
       { <similar_settings_for_DB_2> },
       { ... },
       { <similar_settings_for_DB_N> }
     ],
     "userSpecs": [
       {
         "name": "<username>",
         "password": "<user_password>",
         "permissions": [
           {
             "databaseName": "<DB_name>"
           },
           { <database_2_name> },
           { ... },
           { <DB_N_name> }
         ],
         "settings": {
           "connectionLimit": "<number_of_user_connections>",
           "connectionRetries": "<number_of_retries_when_connecting>"
         },
         "grants": [
           "privilege_1",
           ...,
           "privilege_N"
         ]
       },
       { <similar_settings_for_user_2> },
       { ... },
       { <similar_settings_for_user_N> }
     ],
     "hostSpecs": [
       {
         "zoneId": "<availability_zone>",
         "subnetId": "<subnet_ID>",
         "assignPublicIp": <allow_public_access_to_host>,
         "type": "<host_type>"
       },
       { <similar_settings_for_host_2> },
       { ... },
       { <similar_settings_for_host_N> }
     ],
     "shardSpecs": [
       {
         "shardName": "<shard_name>",
         "mdbPostgresql": {
           "clusterId": "<cluster_ID>"
         }
       },
       { <similar_set_of_settings_for_shard_2> },
       { ... },
       { <similar_set_of_settings_for_shard_N> }
     ],
     "maintenanceWindow": {
       "weeklyMaintenanceWindow": {
         "day": "<day_of_week>",
         "hour": "<sequence_number_of_hour_interval>"
       }
     }
   }
   

   Where:

   • folderId: Folder ID. You can get it from the list of your cloud folders.

   • name: Cluster name.

   • environment: Cluster environment, PRODUCTION or PRESTABLE.

   • networkId: ID of the network the cluster will be deployed in.

     Warning

     The cloud network selected for cluster deployment cannot be changed later. If you need to migrate your cluster to another cloud network in the future, use the restore from a backup feature and specify the required network for your new cluster copy.

   • securityGroupIds: Security group IDs.

     Warning

     For the router to be able to connect to your shard hosts, the Managed Service for Sharded PostgreSQL cluster and the shards and must be in the same security group that allows incoming and outgoing TCP connections to port 6432.

   • deletionProtection: Cluster deletion protection, true or false.

     Once cluster deletion protection is enabled, it also applies to all new and existing databases and users for whom deletion protection is not set explicitly. If a database or user has its own deletion protection setting, it will override the cluster setting.

     Even with deletion protection enabled, one can still connect to the cluster manually and delete the data.

   • configSpec: Cluster settings:

     • spqrSpec: Sharded PostgreSQL settings.

       • router: For advanced sharding, configure the following router settings:

         • config: Router configuration:

           • showNoticeMessages: Show information notifications, true or false.
           • timeQuantiles: Array of time quantile strings for displaying statistics. The default values are "0.5", "0.75", "0.9", "0.95", "0.99", "0.999", "0.9999".
           • defaultRouteBehavior: Router's multishard request execution policy. Possible values: BLOCK or ALLOW.
           • preferSameAvailabilityZone: Enable priority routing of read requests to the router's availability zone, true or false.

         • resources: ROUTER host resource parameters:

           • resourcePresetId: Host class.
           • diskSize: Disk size, in bytes.
           • diskTypeId: Disk type.

       • coordinator: For advanced sharding, set the following coordinator resource parameters:

         • resourcePresetId: Host class.
         • diskSize: Disk size, in bytes.
         • diskTypeId: Disk type.

       • infra: For standard sharding, set the following INFRA host settings:

         • resources: Resource parameters:

           • resourcePresetId: Host class.
           • diskSize: Disk size, in bytes.
           • diskTypeId: Disk type.

         • router: Router configuration:

           • showNoticeMessages: Show information notifications, true or false.
           • timeQuantiles: Array of time quantiles for displaying statistics. The default values are 0.5, 0.75, 0.9, 0.95, 0.99, 0.999, 0.9999.
           • defaultRouteBehavior: Router's multishard request execution policy. Possible values: BLOCK or ALLOW.
           • preferSameAvailabilityZone: Enable priority routing of read requests to the router's availability zone, true or false.

         • consolePassword: Sharded PostgreSQL console password.

         • logLevel: Query logging level: DEBUG, INFO, WARNING, ERROR, FATAL, PANIC.

     • backupWindowStart: Backup window settings.

       Here, specify the backup start time. Allowed values:

       • hours: Between 0 and 23 hours.
       • minutes: Between 0 and 59 minutes.
       • seconds: Between 0 and 59 seconds.
       • nanos: Between 0 and 999999999 nanoseconds.

     • backupRetainPeriodDays: Number of days to retain the cluster backup. Possible values: between 7 and 60 days.

   • databaseSpecs: Database settings as an array of elements, one per database. Each element has the following structure:

     • name: Database name.

   • userSpecs: User settings as an array of elements, one per user. Each element has the following structure:

     • name: Username.

     • password: User password. The password must be from 8 to 128 characters long.

     • permissions.databaseName: Name of the database the user can access.

     • settings: Database user connection parameters:

       • connectionLimit: Connection limit.
       • connectionRetries: Number of reconnect attempts.

     • grants: User privileges as an array of strings. The possible values are reader, writer, admin, or transfer.

   • hostSpecs: Cluster host settings as an array of elements, one per host. Each element has the following structure:

     • zoneId: Availability zone.

     • subnetId: Subnet ID.

     • assignPublicIp: Permission to connect to the host from the internet, true or false.

     • type: Host type. Its possible values are:

       • ROUTER: Router in a cluster with advanced sharding.
       • COORDINATOR: Coordinator in a cluster with advanced sharding.
       • INFRA: INFRA host in a cluster with standard sharding.

   • shardSpecs: Shard settings as an array of elements, one per shard. Each element has the following structure:

     • shardName: Shard name.
     • mdbPostgresql.clusterId: Managed Service for PostgreSQL cluster ID within the shard.

   • maintenanceWindow: Maintenance window settings, including for disabled clusters. Provide one of these two properties:

     • anytime: Maintenance takes place at any time.

     • weeklyMaintenanceWindow: Maintenance takes place once a week at the specified time:

       • day: Day of week, i.e., MON, TUE, WED, THU, FRI, SAT, or SUN.

       • hour: UTC hour interval, from 1 to 24.

         For example, 1 stands for the interval from 00:00 to 01:00, and 5, from 04:00 to 05:00.

3. Call the Cluster.Create method, e.g., via the following cURL request:

   curl \
     --request POST \
     --header "Authorization: Bearer $IAM_TOKEN" \
     --header "Content-Type: application/json" \
     --url 'https://mdb.api.cloud.yandex.net/managed-spqr/v1/clusters' \
     --data "@body.json"
   

4. Check the server response to make sure your request was successful.

1. Get an IAM token for API authentication and put it into an environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume that the repository contents reside in the ~/cloudapi/ directory.

3. Create a file named body.json and paste the following code into it:

   {
     "folder_id": "<folder_ID>",
     "name": "<cluster_name>",
     "description": "<description>",
     "environment": "<environment>",
     "security_group_ids": [
       "<security_group_1_ID>",
       "<security_group_2_ID>",
       ...
       "<security_group_N_ID>"
     ],
     "network_id": "<network_ID>",
     "deletion_protection": <protect_cluster_from_deletion>,
     "config_spec": {
       "spqr_spec": {
         "router": {
           "config": {
             "show_notice_messages": {
               "value": <show_information_notifications>
             },
             "time_quantiles": [
               <list_of_time_quantiles_for_displaying_statistics>
             ],
             "default_route_behavior": "<allow_multishard_requests>",
             "prefer_same_availability_zone": {
               "value": <routing_priority_to_router_availability_zone>
             }
           },
           "resources": {
             "resource_preset_id": "<router_host_class>",
             "disk_size": "<storage_size_in_bytes>",
             "disk_type_id": "<disk_type>" }
         },
         "coordinator": {
           "resources": {
             "resource_preset_id": "<coordinator_host_class>",
             "disk_size": "<storage_size_in_bytes>",
             "disk_type_id": "<disk_type>"
           }
         },
         "infra": {
           "resources": {
             "resource_preset_id": "INFRA_host_class",
             "disk_size": "<storage_size_in_bytes>",
             "disk_type_id": "<disk_type>"
           },
           "router": {
             "show_notice_messages": {
               "value": <show_information_notifications>
             },
             "time_quantiles": [
               <list_of_time_quantiles_for_displaying_statistics>
             ],
             "default_route_behavior": "<allow_multishard_requests>",
             "prefer_same_availability_zone": {
               "value": <routing_priority_to_router_availability_zone>
             }
           },
         },
         "console_password": "<Sharded_PostgreSQL_console_password>",
         "log_level": "<logging_level>"
       },
       "backup_window_start": {
         "hours": "<hours>",
         "minutes": "<minutes>",
         "seconds": "<seconds>",
         "nanos": "<nanoseconds>"
       },
       "backup_retain_period_days": "<number_of_days>"
     },
     "database_specs": [
       {
         "name": "<DB_name>"
       },
       { <similar_settings_for_DB_2> },
       { ... },
       { <similar_settings_for_DB_N> }
     ],
     "user_specs": [
       {
         "name": "<username>",
         "password": "<user_password>",
         "permissions": [
           {
             "database_name": "<DB_name>"
           },
           { <database_2_name> },
           { ... },
           { <DB_N_name> }
         ],
         "settings": {
           "connection_limit": {
             "value": <number_of_user_connections>
           },
           "connection_retries": {
             "value": <number_of_retries_when_connecting>
           }
         },
         "grants": [
           "privilege_1",
           ...,
           "privilege_N"
         ]
       },
       { <similar_settings_for_user_2> },
       { ... },
       { <similar_settings_for_user_N> }
     ],
     "host_specs": [
       {
         "zone_id": "<availability_zone>",
         "subnet_id": "<subnet_ID>",
         "assign_public_ip": <allow_public_access_to_host>,
         "type": "<host_type>"
       },
       { <similar_settings_for_host_2> },
       { ... },
       { <similar_settings_for_host_N> }
     ],
     "shard_specs": [
       {
         "shard_name": "<shard_name>",
         "mdb_postgresql": {
           "cluster_id": "<cluster_ID>"
         }
       },
       { <similar_set_of_settings_for_shard_2> },
       { ... },
       { <similar_set_of_settings_for_shard_N> }
     ],
     "maintenance_window": {
       "weekly_maintenance_window": {
         "day": "<day_of_week>",
         "hour": "<sequence_number_of_hour_interval>"
       }
     }
   }
   

   Where:

   • folder_id: Folder ID. You can get it with the list of folders in the cloud.

   • name: Cluster name.

   • environment: Cluster environment, PRODUCTION or PRESTABLE.

   • network_id: ID of the network the cluster will be deployed in.

     Warning

     The cloud network selected for cluster deployment cannot be changed later. If you need to migrate your cluster to another cloud network in the future, use the restore from a backup feature and specify the required network for your new cluster copy.

   • security_group_ids: Security group IDs.

     Warning

     For the router to be able to connect to your shard hosts, the Managed Service for Sharded PostgreSQL cluster and the shards and must be in the same security group that allows incoming and outgoing TCP connections to port 6432.

   • deletion_protection: Cluster deletion protection, true or false.

     Once cluster deletion protection is enabled, it also applies to all new and existing databases and users for whom deletion protection is not set explicitly. If a database or user has its own deletion protection setting, it will override the cluster setting.

     Even with deletion protection enabled, one can still connect to the cluster manually and delete the data.

   • config_spec: Cluster settings:

     • spqr_spec: Sharded PostgreSQL settings:

       • router: For advanced sharding, configure the following router settings:

         • config: Router configuration:

           • show_notice_messages: Show information notifications, true or false.
           • time_quantiles: Array of time quantiles for displaying statistics. The following values are used by default: 0.5, 0.75, 0.9, 0.95, 0.99, 0.999, 0.9999.
           • default_route_behavior: Router's multishard request execution policy. Possible values: BLOCK or ALLOW.
           • prefer_same_availability_zone: Enable priority routing of read requests to the router's availability zone, true or false.

         • resources: ROUTER host resource parameters:

           • resource_preset_id: Host class.
           • disk_size: Disk size, in bytes.
           • disk_type_id: Disk type.

       • coordinator: For advanced sharding, set the following coordinator resource parameters:

         • resource_preset_id: Host class.
         • disk_size: Disk size, in bytes.
         • disk_type_id: Disk type.

       • infra: For standard sharding, set the following INFRA host settings:

         • resources: Resource parameters:

           • resource_preset_id: Host class.
           • disk_size: Disk size, in bytes.
           • disk_type_id: Disk type.

         • router: Router configuration:

           • default_route_behavior: Default router behavior. Possible values: BLOCK or ALLOW the request.
           • prefer_same_availability_zone: Enable priority routing to the router's availability zone, true or false.

         • console_password: Sharded PostgreSQL console password.

         • log_level: Query logging level: DEBUG, INFO, WARNING, ERROR, FATAL, PANIC.

     • backup_window_start: Backup window settings.

       Here, specify the backup start time. Allowed values:

       • hours: Between 0 and 23 hours.
       • minutes: Between 0 and 59 minutes.
       • seconds: Between 0 and 59 seconds.
       • nanos: Between 0 and 999999999 nanoseconds.

     • backup_retain_period_days: Number of days to retain the cluster backup. Possible values: between 7 and 60 days.

   • database_specs: Database settings as an array of elements, one for each DB. Each element has the following structure:

     • name: Database name.

   • user_specs: User settings as an array of elements, one per user. Each element has the following structure:

     • name: Username.

     • password: User password. The password must be from 8 to 128 characters long.

     • permissions.database_name: Name of the database the user can access.

     • settings: Database user connection parameters:

       • connection_limit: Connection limit.
       • connection_retries: Number of reconnect attempts.

     • grants: User privileges as an array of strings. The possible values are reader, writer, admin, or transfer.

   • host_specs: Cluster host settings as an array of elements, one per host. Each element has the following structure:

     • zone_id: Availability zone

     • subnet_id: Subnet ID.

     • assign_public_ip: Permission to connect to the host from the internet, true or false.

     • type: Host type. Its possible values are:

       • ROUTER: Router in a cluster with advanced sharding.
       • COORDINATOR: Coordinator in a cluster with advanced sharding.
       • INFRA: INFRA host in a cluster with standard sharding.

   • shard_specs: Shard settings as an array of elements, one per shard. Each element has the following structure:

     • shard_name: Shard name.
     • mdb_postgresql.cluster_id: Managed Service for PostgreSQL cluster ID within the shard.

   • maintenance_window: Maintenance window settings, including for disabled clusters. Provide one of these two properties:

     • anytime: Maintenance takes place at any time.

     • weekly_maintenance_window: Maintenance takes place once a week at the specified time:

       • day: Day of week, i.e., MON, TUE, WED, THU, FRI, SAT, or SUN.

       • hour: UTC hour interval, from 1 to 24.

         For example, 1 stands for the interval from 00:00 to 01:00, and 5, from 04:00 to 05:00.

4. Call the ClusterService.Create method, e.g., via the following gRPCurl request:

   grpcurl \
     -format json \
     -import-path ~/cloudapi/ \
     -import-path ~/cloudapi/third_party/googleapis/ \
     -proto ~/cloudapi/yandex/cloud/mdb/spqr/v1/cluster_service.proto \
     -rpc-header "Authorization: Bearer $IAM_TOKEN" \
     -d @ \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.spqr.v1.ClusterService.Create \
     < body.json
   

5. Check the server response to make sure your request was successful.

Create a Managed Service for Sharded PostgreSQL cluster, along with a network, subnet, and security group for it using the following test specifications:

• Name: spqr-std.

• Environment: PRODUCTION.

• Network: spqr-network.

• Security group: spqr-sg with rules allowing inbound and outbound TCP connections on port 6432.

  These rules allow the router to connect to the shard hosts and enable internet access to the cluster. To connect to your cluster over the internet, enable public access to the host as well. Learn more about connecting to clusters.

• Single INFRA host in the ru-central1-a availability zone in the spqr-network-ru-central1-a subnet with the 10.128.0.0/24 IP address range.

• Host class: s2.micro.

• Network SSD storage (network-ssd): 10 GB.

• Database: db1.

• User: user1; password: Password123; db1 access granted.

• Cluster protection from accidental deletion: Enabled.

The configuration file for creating these resources looks like this:

resource "yandex_vpc_network" "spqr_network" {
  description = "Network for the Managed Service for Sharded PostgreSQL"
  name        = "spqr-network"
}

resource "yandex_vpc_subnet" "subnet_a" {
  description    = "Subnet in the ru-central1-a availability zone"
  name           = "spqr-network-ru-central1-a"
  zone           = "ru-central1-a"
  network_id     = yandex_vpc_network.spqr_network.id
  v4_cidr_blocks = ["10.128.0.0/24"]
}

resource "yandex_vpc_security_group" "spqr_sg" {
  description = "Security group for the Managed Service for Sharded PostgreSQL"
  name        = "spqr-sg"
  network_id  = yandex_vpc_network.spqr_network.id

  ingress {
    description    = "Allow connections from the Internet and between cluster components"
    port           = 6432
    protocol       = "TCP"
    v4_cidr_blocks = ["0.0.0.0/0"]
  }

  egress {
    description    = "Allow connections between cluster components"
    port           = 6432
    protocol       = "TCP"
    v4_cidr_blocks = ["0.0.0.0/0"]
  }
}

resource "yandex_mdb_sharded_postgresql_cluster" "spqr_cluster" {
  description         = "Managed Service for Sharded PostgreSQL cluster with standard sharding"
  name                = "spqr-std"
  environment         = "PRODUCTION"
  network_id          = yandex_vpc_network.spqr_network.id
  security_group_ids  = [yandex_vpc_security_group.spqr_sg.id]
  deletion_protection = true

  config = {
    sharded_postgresql_config = {
      infra = {
        resources = {
          disk_size          = 10
          disk_type_id       = "network-ssd"
          resource_preset_id = "s2.micro"
        }
      }
    }
  }

  hosts = {
    infra1 = {
      type      = "INFRA"
      zone      = "ru-central1-a"
      subnet_id = yandex_vpc_subnet.subnet_a.id
    }
  } 
}

resource "yandex_mdb_sharded_postgresql_database" "spqr_cluster_db" {
  cluster_id = yandex_mdb_sharded_postgresql_cluster.spqr_cluster.id
  name       = "db1"
}

resource "yandex_mdb_sharded_postgresql_user" "spqr_cluster_user" {
  cluster_id = yandex_mdb_sharded_postgresql_cluster.spqr_cluster.id
  name       = "user1"
  password   = "Password123"
  
  permissions {
    database = yandex_mdb_sharded_postgresql_database.spqr_cluster_db.name
  }
}

Create a Managed Service for Sharded PostgreSQL cluster, along with a network, subnet, and security group for it using the following test specifications:

• Name: spqr-adv.

• Environment: PRODUCTION.

• Network: spqr-network.

• Security group: spqr-sg with rules allowing inbound and outbound TCP connections on port 6432.

  These rules allow the router to connect to the shard hosts and enable internet access to the cluster. To connect to your cluster over the internet, enable public access to the hosts as well. Learn more about connecting to clusters.

• Three ROUTER hosts of s2.micro class, one in each availability zone:

  • In the ru-central1-a availability zone in the spqr-network-ru-central1-a subnet with the 10.128.0.0/24 IP address range.
  • In the ru-central1-b availability zone in the spqr-network-ru-central1-b subnet with the 10.128.1.0/24 IP address range.
  • In the ru-central1-d availability zone in the spqr-network-ru-central1-d subnet with the 10.128.2.0/24 IP address range.

• Three COORDINATOR hosts of s2.micro class, one in each availability zone:

  • In the ru-central1-a availability zone in the spqr-network-ru-central1-a subnet with the 10.128.0.0/24 IP address range.
  • In the ru-central1-b availability zone in the spqr-network-ru-central1-b subnet with the 10.128.1.0/24 IP address range.
  • In the ru-central1-d availability zone in the spqr-network-ru-central1-d subnet with the 10.128.2.0/24 IP address range.

• Network SSD storage (network-ssd) for each of the ROUTER hosts: 10 GB.

• Network SSD storage (network-ssd) for each of the COORDINATOR hosts: 10 GB.

• Database: db1.

• User: user1; password: Password123; db1 access granted.

• Cluster protection from accidental deletion: Enabled.

The configuration file for creating these resources looks like this:

resource "yandex_vpc_network" "spqr_network" {
  description = "Network for the Managed Service for Sharded PostgreSQL"
  name        = "spqr-network"
}

resource "yandex_vpc_subnet" "subnet_a" {
  description    = "Subnet in the ru-central1-a availability zone"
  name           = "spqr-network-ru-central1-a"
  zone           = "ru-central1-a"
  network_id     = yandex_vpc_network.spqr_network.id
  v4_cidr_blocks = ["10.128.0.0/24"]
}

resource "yandex_vpc_subnet" "subnet_b" {
  description    = "Subnet in the ru-central1-b availability zone"
  name           = "spqr-network-ru-central1-b"
  zone           = "ru-central1-b"
  network_id     = yandex_vpc_network.spqr_network.id
  v4_cidr_blocks = ["10.128.1.0/24"]
}

resource "yandex_vpc_subnet" "subnet_d" {
  description    = "Subnet in the ru-central1-d availability zone"
  name           = "spqr-network-ru-central1-d"
  zone           = "ru-central1-d"
  network_id     = yandex_vpc_network.spqr_network.id
  v4_cidr_blocks = ["10.128.2.0/24"]
}

resource "yandex_vpc_security_group" "spqr_sg" {
  description = "Security group for the Managed Service for Sharded PostgreSQL"
  name        = "spqr-sg"
  network_id  = yandex_vpc_network.spqr_network.id

  ingress {
    description    = "Allow connections from the Internet and between cluster components"
    port           = 6432
    protocol       = "TCP"
    v4_cidr_blocks = ["0.0.0.0/0"]
  }

  egress {
    description    = "Allow connections between cluster components"
    port           = 6432
    protocol       = "TCP"
    v4_cidr_blocks = ["0.0.0.0/0"]
  }
}

resource "yandex_mdb_sharded_postgresql_cluster" "spqr_cluster" {
  description         = "Managed Service for Sharded PostgreSQL cluster with advanced sharding"
  name                = "spqr-adv"
  environment         = "PRODUCTION"
  network_id          = yandex_vpc_network.spqr_network.id
  security_group_ids  = [yandex_vpc_security_group.spqr_sg.id]
  deletion_protection = true

  config = {
    sharded_postgresql_config = {
      router = {
        resources = {
          disk_size          = 10
          disk_type_id       = "network-ssd"
          resource_preset_id = "s2.micro"
        }
      }
      coordinator = {
        resources = {
          disk_size          = 10
          disk_type_id       = "network-ssd"
          resource_preset_id = "s2.micro"
        }
      }
    }
  }

  hosts = {
    router1 = {
      type      = "ROUTER"
      zone      = "ru-central1-a"
      subnet_id = yandex_vpc_subnet.subnet_a.id
    }

    router2 = {
      type      = "ROUTER"
      zone      = "ru-central1-b"
      subnet_id = yandex_vpc_subnet.subnet_b.id
    }
  
    router3 = {
      type      = "ROUTER"
      zone      = "ru-central1-d"
      subnet_id = yandex_vpc_subnet.subnet_d.id
    }

    coordinator1 = {
      type      = "COORDINATOR"
      zone      = "ru-central1-a"
      subnet_id = yandex_vpc_subnet.subnet_a.id
    }

    coordinator2 = {
      type      = "COORDINATOR"
      zone      = "ru-central1-b"
      subnet_id = yandex_vpc_subnet.subnet_b.id
    }
  
    coordinator3 = {
      type      = "COORDINATOR"
      zone      = "ru-central1-d"
      subnet_id = yandex_vpc_subnet.subnet_d.id
    }
  } 
}

resource "yandex_mdb_sharded_postgresql_database" "spqr_cluster_db" {
  cluster_id = yandex_mdb_sharded_postgresql_cluster.spqr_cluster.id
  name       = "db1"
}

resource "yandex_mdb_sharded_postgresql_user" "spqr_cluster_user" {
  cluster_id = yandex_mdb_sharded_postgresql_cluster.spqr_cluster.id
  name       = "user1"
  password   = "Password123"

  permissions {
    database = yandex_mdb_sharded_postgresql_database.spqr_cluster_db.name
  }
}