Creating a PostgreSQL cluster

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. 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 create cluster CLI command:

   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.

     Warning

     You cannot change disk type after you create a cluster.

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

   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 available connection pooler modes include SESSION, TRANSACTION, and 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.

   Note

   The password must be between 8 and 128 characters.

   Enabled deletion protection will not prevent a manual connection with the purpose to delete database contents.

   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 don't have Terraform, install it and configure the Yandex Cloud provider.

To create a Managed Service for PostgreSQL cluster:

1. In the configuration file, describe the parameters of 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.

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

   Note

   The password must be between 8 and 128 characters.

   Enabled deletion protection will not prevent a manual connection with the purpose to delete database contents.

   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. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

3. Create a cluster.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of 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 a 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.

   • 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.
     • 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 a 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.

   • 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.
     • 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" ]
  }
}