Creating a PostgreSQL cluster

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

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

To create a Managed Service for PostgreSQL cluster:

1. Check whether the folder has any subnets for the cluster hosts:

   yc vpc subnet list
   

   If there are no subnets in the folder, create the required subnets in Yandex Virtual Private Cloud.

2. View the description of the CLI command to create a cluster:

   yc managed-postgresql cluster create --help
   

3. Specify cluster parameters in the create command (the list of supported parameters in the example is not exhaustive):

   yc managed-postgresql cluster create \
     --name <cluster_name> \
     --environment <environment> \
     --network-name <network_name> \
     --host zone-id=<availability_zone>,`
              `subnet-id=<subnet_ID>,`
              `assign-public-ip=<access_to_host_from_internet> \
     --resource-preset <host_class> \
     --user name=<username>,password=<user_password> \
     --database name=<DB_name>,owner=<database_owner_name> \
     --disk-size <storage_size_in_GB> \
     --disk-type <network-hdd|network-ssd|network-ssd-nonreplicated|local-ssd> \
     --security-group-ids <list_of_security_group_IDs> \
     --connection-pooling-mode=<connection_pooler_mode> \
     --deletion-protection
   

   Where:

   • environment: Environment, prestable or production.

   • disk-type: Disk type.

   • assign-public-ip: Allow access to the host from the internet, true or false.

   • deletion-protection: Protection of the cluster, its databases, and users against deletion.

     By default, the parameter inherits its value from the cluster when creating users and databases. You can also set the value manually; for more information, see the User management and Database management sections.

     If the parameter is changed on a running cluster, only users and databases with the Same as cluster protection will inherit the new value.

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

   You need to specify the subnet-id if the selected availability zone has two or more subnets.

   Warning

   The cloud network selected for cluster deployment can't be changed. If you need to move your cluster to a different cloud network later, use the restore from a backup feature and specify the desired network for the cluster backup.

   The database name may contain Latin letters, numbers, underscores, and hyphens. The name may be up to 63 characters long. The names postgres, template0, and template1 are reserved for Managed Service for PostgreSQL. You cannot create databases with these names.

   The password must be from 8 to 128 characters long.

   Note

   You can also generate a password using Connection Manager. To do this, adjust the command, setting the user parameters as follows:

     --user name=<username>,generate-password=true
   

   To view the password, select the cluster you created in the management console, go to the Users tab and click View password in the user's row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

   The available connection pooler modes include SESSION, TRANSACTION, and STATEMENT.

   You can also set the additional replication-source option in the --host parameter to manually manage replication threads.

   To allow access to the cluster from Yandex Cloud Functions, provide the --serverless-access parameter. For more information about setting up access, see the Cloud Functions documentation.

   To allow access to the cluster from Yandex Query, provide the --yandexquery-access=true parameter. This feature is at the Preview stage and is available upon request.

   Note

   When creating a cluster, the anytime maintenance mode is set by default. You can set a specific maintenance period when updating the cluster settings.

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 documentation on the Terraform website or mirror website.

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

To create a Managed Service for PostgreSQL cluster:

1. In the configuration file, describe the resources you want to create:

   • DB cluster: Description of the cluster and its hosts

   • Database: Description of the cluster DB

   • User: Description of the cluster user

   • Network: Description of the cloud network where a cluster will be located. If you already have a suitable network, you don't have to describe it again.

   • Subnets: Description of the subnets to connect the cluster hosts to. If you already have suitable subnets, you don't have to describe them again.

   Warning

   The cloud network selected for cluster deployment can't be changed. If you need to move your cluster to a different cloud network later, use the restore from a backup feature and specify the desired network for the cluster backup.

   Here is an example of the configuration file structure:

   resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
     name                = "<cluster_name>"
     environment         = "<environment>"
     network_id          = "<network_ID>"
     security_group_ids  = [ "<list_of_security_group_IDs>" ]
     deletion_protection = <deletion_protection>
   
     config {
       version = "<PostgreSQL_version>"
       resources {
         resource_preset_id = "<host_class>"
         disk_type_id       = "<disk_type>"
         disk_size          = <storage_size_in_GB>
       }
       pooler_config {
         pool_discard = <Odyssey_parameter>
         pooling_mode = "<operation_mode>"
       }
       ...
     }
   
     host {
       zone             = "<availability_zone>"
       name             = "<host_name>"
       subnet_id        = "<subnet_ID>"
       assign_public_ip = <access_to_host_from_internet>
     }
   }
   
   resource "yandex_mdb_postgresql_database" "<DB_name>" {
     cluster_id = "<cluster_ID>"
     name       = "<DB_name>"
     owner      = "<database_owner_name>"
     depends_on = [
       yandex_mdb_postgresql_user.<username>
     ]
   }
   
   resource "yandex_mdb_postgresql_user" "<username>" {
     cluster_id = "<cluster_ID>"
     name       = "<username>"
     password   = "<user_password>"
   }
   
   resource "yandex_vpc_network" "<network_name>" { name = "<network_name>" }
   
   resource "yandex_vpc_subnet" "<subnet_name>" {
     name           = "<subnet_name>"
     zone           = "<availability_zone>"
     network_id     = "<network_ID>"
     v4_cidr_blocks = ["<range>"]
   }
   

   Where:

   • environment: Environment, PRESTABLE or PRODUCTION.

   • assign_public_ip: Allow access to the host from the internet, true or false.

   • deletion_protection: Protection of the cluster, its databases, and users against deletion, true or false.

     By default, the parameter inherits its value from the cluster when creating users and databases. You can also set the value manually; for more information, see the User management and Database management sections.

     If the parameter is changed on a running cluster, only users and databases with the Same as cluster protection will inherit the new value.

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

   • version: PostgreSQL version, 13, 13-1c, 14, 14-1c, 15, 15-1c, 16, 16-1c, and 17.

   • pool_discard: Odyssey pool_discard parameter, true or false.

   • pooling_mode: Operation mode, SESSION, TRANSACTION, or STATEMENT.

   The database name may contain Latin letters, numbers, underscores, and hyphens. The name may be up to 63 characters long. The names postgres, template0, and template1 are reserved for Managed Service for PostgreSQL. You cannot create databases with these names.

   The password must be from 8 to 128 characters long.

   Note

   You can also generate a password using Connection Manager. To do this, specify generate_password = true instead of "password" = "<user_password>".

   To view the password, select the cluster you created in the management console, go to the Users tab and click View password in the user's row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

   To set up the maintenance window (for disabled clusters as well), add the maintenance_window section to the cluster description:

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

   Where:

   • type: Maintenance type. The possible values include:
     • anytime: Anytime.
     • weekly: By schedule.
   • day: Day of the week for the weekly type in DDD format, e.g., MON.
   • hour: Hour of the day for the weekly type in the HH format, e.g., 21.

   To set up statistics collection, to the config section, add the performance_diagnostics section:

   resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
     ...
     config {
       ...
       performance_diagnostics {
         enabled = <enables statistics collection: true or false>
         sessions_sampling_interval  = <sessions sampling interval>
         statements_sampling_interval = <statements sampling interval>
       }
       ...
     }
     ...
   }
   

   Where:

   • enabled: Enable statistics collection, true or false.
   • sessions_sampling_interval: Session sampling interval, from 1 to 86400 seconds.
   • statements_sampling_interval: Statement sampling interval, from 60 to 86400 seconds.

   For a complete list of available Managed Service for PostgreSQL cluster configuration fields, see the Terraform provider documentation.

2. Make sure the settings are correct.

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

   Time limits

   A Terraform provider sets the timeout for Managed Service for PostgreSQL cluster operations:

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

   Operations exceeding the set timeout are interrupted.

   How do I change these limits?

   Add the timeouts block to the cluster description, for example:

   resource "yandex_mdb_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 the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Create a file named body.json and add the following contents to it:

   {
     "folderId": "<folder_ID>",
     "name": "<cluster_name>",
     "environment": "<environment>",
     "networkId": "<network_ID>",
     "securityGroupIds": [
       "<security_group_1_ID>",
       "<security_group_2_ID>",
       ...
       "<security_group_N_ID>"
     ],
     "deletionProtection": <deletion_protection:_true_or_false>,
     "configSpec": {
       "version": "<PostgreSQL_version>",
       "resources": {
         "resourcePresetId": "<host_class>",
         "diskSize": "<storage_size_in_bytes>",
         "diskTypeId": "<disk_type>"
       },
       "access": {
         "dataLens": <access_to_DataLens:_true_or_false>,
         "webSql": <access_to_WebSQL:_true_or_false>,
         "serverless": <access_to_Cloud_Functions:_true_or_false>,
         "dataTransfer": <access_to_Data_Transfer:_true_or_false>,
         "yandexQuery": <access_to_Query:_true_or_false>
       },
       "performanceDiagnostics": {
         "enabled": <activate_statistics_collection:_true_or_false>,
         "sessionsSamplingInterval": "<session_sampling_interval>",
         "statementsSamplingInterval": "<statement_sampling_interval>"
       }
     },
     "databaseSpecs": [
       {
         "name": "<DB_name>",
         "owner": "<database_owner_name>"
       },
       { <similar_configuration_for_DB_2> },
       { ... },
       { <similar_configuration_for_DB_N> }
     ],
     "userSpecs": [
       {
         "name": "<username>",
         "password": "<user_password>",
         "permissions": [
           {
             "databaseName": "<DB_name>"
           }
         ],
         "login": <allow_user_to_connect_to_DB:_true_or_false>
       },
       { <similar_configuration_for_user_2> },
       { ... },
       { <similar_configuration_for_user_N> }
     ],
     "hostSpecs": [
       {
         "zoneId": "<availability_zone>",
         "subnetId": "<subnet_ID>",
         "assignPublicIp": <public_host_address:_true_or_false>
       },
       { <similar_configuration_for_host_2> },
       { ... },
       { <similar_configuration_for_host_N> }
     ]
   }
   

   Where:

   • folderId: Folder ID. You can request it with the list of folders in the cloud.

   • name: Cluster name.

   • environment: Cluster environment, PRODUCTION or PRESTABLE.

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

     Warning

     The cloud network selected for cluster deployment can't be changed. If you need to move your cluster to a different cloud network later, use the restore from a backup feature and specify the desired network for the cluster backup.

   • securityGroupIds: Security group IDs.

   • deletionProtection: Protection of the cluster, its databases, and users against deletion.

     By default, the parameter inherits its value from the cluster when creating users and databases. You can also set the value manually; for more information, see the User management and Database management sections.

     If the parameter is changed on a running cluster, only users and databases with the Same as cluster protection will inherit the new value.

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

   • configSpec: Cluster settings:

     • version: PostgreSQL version.

     • resources: Cluster resources:

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

     • access: Cluster settings for access to the following Yandex Cloud services:

       • dataLens: Yandex DataLens
       • webSql: Yandex WebSQL
       • serverless: Yandex Cloud Functions
       • dataTransfer: Yandex Data Transfer
       • yandexQuery: Yandex Query

     • performanceDiagnostics: Statistics collection settings:

       • enabled: Enables statistics collection.
       • sessionsSamplingInterval: Session sampling interval. The values range from 1 to 86400 seconds.
       • statementsSamplingInterval: Statement sampling interval. The values range from 60 to 86400 seconds.

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

     • name: DB name.
     • owner: DB owner username. It must match one of the usenames specified in the request.

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

     • name: Username.

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

       You can also generate a password using Connection Manager. To do this, specify "generatePassword": true instead of "password": "<user_password>".

       To view the password, select the cluster you created in the management console, go to the Users tab and click View password in the user's row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

     • permissions.databaseName: Name of the database the user gets access to.

     • login: User permission to connect to the DB.

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

     • zoneId: Availability zone.
     • subnetId: Subnet ID.
     • assignPublicIp: Permission to connect to the host from the internet.

3. Use the Cluster.Create method and send the following request, e.g., via cURL:

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

4. View the server response to make sure the request was successful.

1. Get an IAM token for API authentication and put it into the 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 the repository contents are stored in the ~/cloudapi/ directory.

3. Create a file named body.json and add the following contents to it:

   {
     "folder_id": "<folder_ID>",
     "name": "<cluster_name>",
     "environment": "<environment>",
     "network_id": "<network_ID>",
     "security_group_ids": [
       "<security_group_1_ID>",
       "<security_group_2_ID>",
       ...
       "<security_group_N_ID>"
     ],
     "deletion_protection": <deletion_protection:_true_or_false>,
     "config_spec": {
       "version": "<PostgreSQL_version>",
       "resources": {
         "resource_preset_id": "<host_class>",
         "disk_size": "<storage_size_in_bytes>",
         "disk_type_id": "<disk_type>"
       },
       "access": {
         "data_lens": <access_to_DataLens:_true_or_false>,
         "web_sql": <access_to_WebSQL:_true_or_false>,
         "serverless": <access_to_Cloud_Functions:_true_or_false>,
         "data_transfer": <access_to_Data_Transfer:_true_or_false>,
         "yandex_query": <access_to_Query:_true_or_false>
       },
       "performance_diagnostics": {
         "enabled": <activate_statistics_collection:_true_or_false>,
         "sessions_sampling_interval": "<session_sampling_interval>",
         "statements_sampling_interval": "<statement_sampling_interval>"
       }
     },
     "database_specs": [
       {
         "name": "<DB_name>",
         "owner": "<database_owner_name>"
       },
       { <similar_configuration_for_DB_2> },
       { ... },
       { <similar_configuration_for_DB_N> }
     ],
     "user_specs": [
       {
         "name": "<username>",
         "password": "<user_password>",
         "permissions": [
           {
             "database_name": "<DB_name>"
           }
         ],
         "login": <allow_user_to_connect_to_DB:_true_or_false>
       },
       { <similar_configuration_for_user_2> },
       { ... },
       { <similar_configuration_for_user_N> }
     ],
     "host_specs": [
       {
         "zone_id": "<availability_zone>",
         "subnet_id": "<subnet_ID>",
         "assign_public_ip": <public_host_address:_true_or_false>
       },
       { <similar_configuration_for_host_2> },
       { ... },
       { <similar_configuration_for_host_N> }
     ]
   }
   

   Where:

   • folder_id: Folder ID. You can request 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 in.

     Warning

     The cloud network selected for cluster deployment can't be changed. If you need to move your cluster to a different cloud network later, use the restore from a backup feature and specify the desired network for the cluster backup.

   • security_group_ids: Security group IDs.

   • deletion_protection: Protection of the cluster, its databases, and users against deletion.

     By default, the parameter inherits its value from the cluster when creating users and databases. You can also set the value manually; for more information, see the User management and Database management sections.

     If the parameter is changed on a running cluster, only users and databases with the Same as cluster protection will inherit the new value.

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

   • config_spec: Cluster settings:

     • version: PostgreSQL version.

     • resources: Cluster resources:

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

     • access: Cluster settings for access to the following Yandex Cloud services:

       • data_lens: Yandex DataLens
       • web_sql: Yandex WebSQL
       • serverless: Yandex Cloud Functions
       • data_transfer: Yandex Data Transfer
       • yandex_query: Yandex Query

     • performance_diagnostics: Statistics collection settings:

       • enabled: Enables statistics collection.
       • sessions_sampling_interval: Session sampling interval. The values range from 1 to 86400 seconds.
       • statements_sampling_interval: Statement sampling interval. The values range from 60 to 86400 seconds.

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

     • name: DB name.
     • owner: DB owner username. It must match one of the usenames specified in the request.

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

     • name: Username.

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

       You can also generate a password using Connection Manager. To do this, specify "generate_password": true instead of "password": "<user_password>".

       To view the password, select the cluster you created in the management console, go to the Users tab and click View password in the user's row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

     • permissions.database_name: Name of the database the user gets access to.

     • login: User permission to connect to the DB.

   • host_specs: Cluster host settings as an array of elements, one for each 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.

4. Use the ClusterService.Create call and send the following request, e.g., via gRPCurl:

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

5. View the server response to make sure the request was successful.

Create a Managed Service for PostgreSQL cluster and a network for it with the following test specifications:

• Name: mypg.

• Version: 17.

• Environment: PRESTABLE.

• Cloud ID: b1gq90dgh25bebiu75o.

• Folder ID: b1gia87mbaomkfvsleds.

• New network: mynet.

• New security group: pgsql-sg, allowing cluster connections from the internet through port 6432.

• Host class: s2.micro (one host), new subnet: mysubnet, availability zone: ru-central1-a. mysubnet range: 10.5.0.0/24.

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

• User: user1, password: user1user1.

• Database: db1, owner: user1.

• Protection of the cluster, its DBs, and users against accidental deletion: Enabled.

The configuration file for this cluster is as follows:

resource "yandex_mdb_postgresql_cluster" "mypg" {
  name                = "mypg"
  environment         = "PRESTABLE"
  network_id          = yandex_vpc_network.mynet.id
  security_group_ids  = [ yandex_vpc_security_group.pgsql-sg.id ]
  deletion_protection = true

  config {
    version = 17
    resources {
      resource_preset_id = "s2.micro"
      disk_type_id       = "network-ssd"
      disk_size          = "20"
    }
  }

  host {
    zone      = "ru-central1-a"
    name      = "mypg-host-a"
    subnet_id = yandex_vpc_subnet.mysubnet.id
  }
}

resource "yandex_mdb_postgresql_database" "db1" {
  cluster_id = yandex_mdb_postgresql_cluster.mypg.id
  name       = "db1"
  owner      = "user1"
}

resource "yandex_mdb_postgresql_user" "user1" {
  cluster_id = yandex_mdb_postgresql_cluster.mypg.id
  name       = "user1"
  password   = "user1user1"
}

resource "yandex_vpc_network" "mynet" {
  name = "mynet"
}

resource "yandex_vpc_subnet" "mysubnet" {
  name           = "mysubnet"
  zone           = "ru-central1-a"
  network_id     = yandex_vpc_network.mynet.id
  v4_cidr_blocks = ["10.5.0.0/24"]
}

resource "yandex_vpc_security_group" "pgsql-sg" {
  name       = "pgsql-sg"
  network_id = yandex_vpc_network.mynet.id

  ingress {
    description    = "PostgreSQL"
    port           = 6432
    protocol       = "TCP"
    v4_cidr_blocks = [ "0.0.0.0/0" ]
  }
}