[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for PostgreSQL](../index.md) > [Step-by-step guides](index.md) > Clusters > Creating a cluster # Creating a PostgreSQL cluster A PostgreSQL cluster is one or more [database hosts](../concepts/index.md) between which you can configure [replication](../concepts/replication.md). Replication is enabled by default in any cluster consisting of more than one host: the master host accepts write requests and duplicates changes on replicas. A transaction is committed once the data is written to [disk](../concepts/storage.md) on both the master host and on a sufficient number of replicas to form a quorum. {% note info %} * The number of hosts you can create together with a PostgreSQL cluster depends on the selected [disk type](../concepts/storage.md#storage-type-selection) and [host class](../concepts/instance-types.md#available-flavors). * The available disk types depend on the selected [host class](../concepts/instance-types.md#available-flavors). * If the database storage reaches 97% capacity, the cluster will switch to read-only mode. Plan and increase your storage size beforehand, or [set up an automatic storage expansion](storage-space.md#disk-size-autoscale). {% endnote %} By default, Managed Service for PostgreSQL sets the maximum possible number of connections for each host in the PostgreSQL cluster. This maximum cannot exceed the value of the [Max connections](../concepts/settings-list.md#setting-max-connections) setting. {% note warning %} Managed Service for PostgreSQL reserves 15 connections for service users per PostgreSQL host. For example, if the cluster has [Max connections](../concepts/settings-list.md#setting-max-connections) `100`, you can reserve a maximum of 85 connections for cluster users. {% endnote %} ## Creating a cluster {#create-cluster} To create a Managed Service for PostgreSQL cluster, you need the [vpc.user](../../vpc/security/index.md#vpc-user) role and the [managed-postgresql.editor role or higher](../security/index.md#roles-list). For more information on assigning roles, see the [Identity and Access Management guides](../../iam/operations/roles/grant.md). Cluster DB connections are managed by Connection Manager. Creating a cluster automatically creates: * [Connection Manager connection](../../metadata-hub/concepts/connection-manager.md) with information about the database connection. * [Yandex Lockbox secret](../../metadata-hub/concepts/secret.md) that stores the DB owner's user password. Storing passwords in Yandex Lockbox ensures their security. The connection and secret will be created for each new database user. To view all connections, select the **Connections** tab on the cluster page. You need the `connection-manager.viewer` role to view connection info. You can [use Connection Manager](../../metadata-hub/operations/connection-access.md) to configure access to connections. You can use Connection Manager and secrets you create there free of charge. {% list tabs group=instructions %} - Management console {#console} To create a Managed Service for PostgreSQL cluster: 1. In the [management console](https://console.yandex.cloud), select the [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) where you want to create your database cluster. 1. Navigate to **Managed Service for PostgreSQL**. 1. Click **Create cluster**. 1. Enter a cluster name in the **Cluster name** field. The cluster name must be unique within the folder. 1. Select the environment where you want to create your cluster (you cannot change the environment once the cluster is created): * `PRODUCTION`: For stable versions of your applications. * `PRESTABLE`: For testing purposes. The prestable environment is similar to the production environment and likewise covered by an SLA, but it is the first to get new features, improvements, and bug fixes. In the prestable environment, you can test new versions for compatibility with your application. 1. From the **Version** drop-down list, select the DBMS version or leave the default version: `17`. 1. Select the host class, which will determine the technical specifications of the [VMs](../../compute/concepts/vm.md) for deploying your database hosts. All available options are listed under [Host classes](../concepts/instance-types.md). Changing the cluster’s host class updates the specifications for all of its existing hosts. 1. Under **Storage size**: * Select the disk type. The type you select determines the increments for changing the disk size: * Network HDDs and SSDs: In increments of 1 GB. * Local SSDs: * For **Intel Broadwell** and **Intel Cascade Lake**: In 100 GB increments. * For **Intel Ice Lake** and **AMD Zen 4**: In 368 GB increments. * Non-replicated SSDs and ultra high-speed network SSDs with three replicas: In increments of 93 GB. {% note info %} Local SSDs may slightly increase the load on the CPU cores of cluster hosts. {% endnote %} * Select the storage capacity for your data and backups. For more information on how backups take up storage space, see [Backups](../concepts/backup.md). * Optionally, select **Encrypted disk** to encrypt the disk with a [custom KMS key](../../kms/concepts/key.md). * To [create](../../kms/operations/key.md#create) a new key, click **Create**. * To use a previously created key, select it in the **KMS key** field. To learn more about disk encryption, see [Storage](../concepts/storage.md#disk-encryption). 1. Optionally, under **Automatic increase of storage size**, specify the following settings: * In the **Increase size** field, set the conditions for the actions below: * Storage expansion during the [next maintenance window](../concepts/maintenance.md#maintenance-window) if and when the specified storage space utilization percentage is exceeded. * Storage expansion immediately if and when the specified storage space utilization percentage is exceeded. You can set both thresholds, provided that the threshold for immediate scaling is higher than that for scaling during the maintenance window. For more information on storage scaling rules, see [this section](../concepts/storage.md#auto-rescale). * In the **Maximum storage size** field, specify the maximum storage size that can be set during automatic scaling. Upon reaching the specified threshold, the storage expands differently depending on the disk type: * For network HDDs and SSDs, by the higher of the two values: 20 GB or 20% of the current disk size. * For non-replicated SSDs and ultra high-speed network SSDs with three replicas, by 93 GB. * For local SSDs: * In an **Intel Broadwell** or **Intel Cascade Lake** cluster, by 100 GB. * In an **Intel Ice Lake** or **AMD Zen 4** cluster, by 368 GB. If the threshold is reached again, the storage will be automatically expanded until it reaches the specified maximum. After that, you can set a new maximum storage size manually. When you change the storage size, the cluster hosts are updated one at a time in random order. If you need to restart a host during an update, it will become unavailable for this time period. Prior to the master host's update, one of the replicas takes over its role. If you access a cluster using the [FQDN of the master host](connect/fqdn.md), the cluster may become unavailable. To ensure uninterrupted operation of your application, list all the cluster hosts and specify `target_session_attrs=primary` when connecting, or connect via a [special FQDN](connect/fqdn.md) always pointing to the current master host. {% note warning %} You cannot reduce the storage size following an automatic expansion. {% endnote %} {% note info %} Some PostgreSQL settings [depend on the storage size](../concepts/settings-list.md#settings-instance-dependent). {% endnote %} If you have configured storage scaling during a maintenance window, set the maintenance schedule. 1. Under **Database**, specify the database details: * Database name. The name must be unique within the folder. The database name may contain Latin letters, numbers, underscores, and hyphens. The maximum name length is 63 characters. The names `postgres`, `template0`, and `template1` are reserved for internal use by Managed Service for PostgreSQL. You cannot create databases using these names. * Database owner username. By default, the system allocates 50 connections per cluster host to a new user. You can change the maximum number of connections using the [Conn limit](../concepts/settings-list.md#setting-conn-limit) setting. 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 maximum name length is 63 characters. The names `admin`, `repl`, `monitor`, `postgres`, `mdb_superuser`, `mdb_admin`, `mdb_monitor`, and `mdb_replication` are reserved for internal use by Managed Service for PostgreSQL. You cannot create users with these names. * Password: * **Enter manually**: Select this option to set your own password. It must be from 8 to 128 characters long. * **Generate**: Select this option to generate a password using Connection Manager. To view the password after creating a cluster, select the **Users** tab and click **View password** for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. To view passwords, you need the `lockbox.payloadViewer` role. * Collation locale and character set locale. These settings define the rules for sorting strings (`LC_COLLATE`) and classifying characters (`LC_CTYPE`). In Managed Service for PostgreSQL, locale settings apply at the level of an individual database. PostgreSQL supports multiple locales. The choice of locale affects the following: - Sort order in queries using the `ORDER BY` clause or standard text comparison operators. - `upper`, `lower`, `initcap`, and `to_char` functions. - Pattern-matching operators, e.g., `LIKE`, `ILIKE`, `SIMILAR TO`, and regular expressions. - Support of indexes with the `LIKE` operator. The default locale is `C`. When using the `C` encoding for text data with non-Latin, e.g., Cyrillic, characters, you may encounter errors in data sorting and incorrect pattern-matching results. If this locale causes incorrect data handling in your database, select a different one from the list. However, note that using a non-standard locale may reduce database query performance. For more information about locale settings, see [this PostgreSQL article](https://www.postgresql.org/docs/current/locale.html). Once you create a database, you cannot change its locale settings. However, you can set a collation for columns when creating or altering individual tables. For more information, see the relevant [PostgreSQL guides](https://www.postgresql.org/docs/current/sql-createtable.html). 1. Under **Network settings**, select: * [Cloud network](../../vpc/concepts/network.md#network) for cluster deployment. If there are no networks in the list, click **Create network** to create one: 1. In the window that opens, specify the network name and select the folder where it will be created. 1. Optionally, check **Create subnets** to automatically create subnets in all availability zones. 1. Click **Create network**. {% note 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](cluster-backups.md) feature and specify the required network for your new cluster copy. {% endnote %} * [Security groups](../../vpc/concepts/security-groups.md) for the cluster network traffic. You may need to additionally [set up security groups](connect/index.md#configuring-security-groups) to be able connect to the cluster. 1. Under **Hosts**, configure the settings for the cluster’s database hosts. By default, each host is created in a separate [subnet](../../vpc/concepts/network.md#subnet). To select a specific subnet for a host, click ![image](../../_assets/console-icons/pencil.svg) next to it. The minimum number of hosts per cluster depends on the selected [disk type](../concepts/storage.md). The default cluster configuration offered in the management console includes: * Two hosts if the selected disk type is `network-ssd`, `network-hdd`, or `network-ssd-io-m3`. * Three hosts if the selected disk type is `local-ssd` or `network-ssd-nonreplicated`. {% note warning %} We do not recommend creating a single-host cluster. While being cheaper, it will not ensure [high availability](../concepts/high-availability.md#host-configuration). {% endnote %} After creating a Managed Service for PostgreSQL cluster, you can add more hosts to it if your [folder resource](../concepts/limits.md) quotas allow. To enable internet access to the hosts, check **Public access**. 1. Under **Performance diagnostics**, enable standard or advanced diagnostics. The advanced mode also includes collecting and analyzing query plans. To use the [Performance diagnostics](performance-diagnostics.md) tool, select **Statistics sampling** and set up **Sessions sampling interval** and **Statements sampling interval**. Both settings are measured in seconds. 1. Specify additional cluster settings, if required: - **Backup start time (UTC)**: Time interval during which the cluster backup starts. Time is specified in 24-hour UTC format. The default time is `22:00 - 23:00` UTC. - **Retention period for automatic backups, days**: Retention period for automatic backups. Backups are automatically deleted once their retention period expires. The default is 7 days. For more information, see [Backups](../concepts/backup.md). Changing the retention period affects both new and existing automatic backups. For example, the initial retention period was 7 days. A specific automatic backup has 1 day of remaining lifetime. If you increase the retention period to 9 days, that backup’s remaining lifetime becomes 3 days. Automatic cluster backups are stored for a specified number of days, while manually created ones are stored indefinitely. After a cluster is deleted, all its backups are retained for 7 days. - **Maintenance**: [Maintenance window](../concepts/maintenance.md) settings: * To enable maintenance at any time, select **At any time** (default). * To specify the preferred maintenance start time, select **By schedule** and specify the day of the week and the UTC time interval. For example, you can choose a time when the cluster is least loaded. Both active and stopped clusters are subject to maintenance. Maintenance operations include DBMS updates, patches, etc. - **DataLens access**: This option enables you to analyze cluster data in [Yandex DataLens](../../datalens/concepts/index.md). - **WebSQL access**: This option enables you to [run SQL queries](web-sql-query.md) against cluster databases from the Yandex Cloud management console using Yandex WebSQL. - **Yandex Query access**: Enables you to run YQL queries against cluster databases from [Yandex Query](../../query/concepts/index.md). - **Serverless access**: Enable this option to allow cluster access from [Yandex Cloud Functions](../../functions/concepts/index.md). For more information about setting up access, see [Cloud Functions](../../functions/operations/database-connection.md) this guide. - **Pooling mode**: Select one of the [connection pooler modes](../concepts/pooling.md). - **Deletion protection**: Deletion protection for the cluster, its databases, and users. By default, when users and databases are created, this setting’s value is inherited from the cluster. You can also specify this setting manually. See [User management](cluster-users.md) and [Database management](databases.md) for details. If the setting is changed on a running cluster, the new value will only be inherited by users and databases with the **Same as cluster** protection level. Even with deletion protection enabled, one can still connect to the cluster manually and delete the data. 1. If needed, configure [cluster-level DBMS settings](../concepts/settings-list.md#dbms-cluster-settings). {% note info %} Some PostgreSQL settings [depend on the selected host class or storage size](../concepts/settings-list.md#settings-instance-dependent). {% endnote %} 1. Click **Create cluster**. - CLI {#cli} If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install). The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set 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 PostgreSQL cluster: 1. Check whether your [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) has [subnets](../../vpc/concepts/network.md#subnet) for the cluster hosts: ```bash yc vpc subnet list ``` If your folder has no subnets, [create them](../../vpc/operations/subnet-create.md) in [Yandex Virtual Private Cloud](../../vpc/index.md). 1. View the description of the CLI command for creating a cluster: ```bash yc managed-postgresql cluster create --help ``` 1. Specify the cluster properties in this command (the example does not show all that are available): ```bash yc managed-postgresql cluster create \ --name \ --environment \ --network-name \ --host zone-id=,` `subnet-id=,` `assign-public-ip= \ --resource-preset \ --user name=,password= \ --database name=,owner= \ --disk-size \ --disk-type \ --security-group-ids \ --connection-pooling-mode= \ --deletion-protection \ --performance-diagnostics enabled=,` `sessions-sampling-interval=,` `statements-sampling-interval= ``` Where: * `environment`: Environment, `prestable` or `production`. * `disk-type`: Disk type. {% note info %} Local SSDs may slightly increase the load on the CPU cores of cluster hosts. {% endnote %} * `assign-public-ip`: Allow access to the host from the internet, `true` or `false`. * `deletion-protection`: Deletion protection for the cluster, its databases, and users. By default, when users and databases are created, this setting’s value is inherited from the cluster. You can also specify this setting manually. See [User management](cluster-users.md) and [Database management](databases.md) for details. If the setting is changed on a running cluster, the new value will only be inherited by users and databases with the **Same as cluster** protection level. Even with deletion protection enabled, one can still connect to the cluster manually and delete the data. * `--performance-diagnostics`: [Statistics collection](performance-diagnostics.md#activate-stats-collector) settings: * `enabled`: The value of `true` enables statistics collection. The default value is `false`. * `sessions-sampling-interval`: Session sampling interval in seconds. The valid values range from `1` to `86400`. * `statements-sampling-interval`: Statement sampling interval in seconds. The valid values range from `60` to `86400`. You need to specify the `subnet-id` if the selected [availability zone](../../overview/concepts/geo-scope.md) has two or more subnets. {% note 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](cluster-backups.md) feature and specify the required network for your new cluster copy. {% endnote %} The database name may contain Latin letters, numbers, underscores, and hyphens. The maximum name length is 63 characters. The names `postgres`, `template0`, and `template1` are reserved for internal use by Managed Service for PostgreSQL. You cannot create databases using these names. The password must be from 8 to 128 characters long. {% note info %} You can also generate a password using Connection Manager. To do this, edit the command, specifying the user properties as follows: ```bash --user name=,generate-password=true ``` To view the password, select your cluster in the [management console](https://console.yandex.cloud), navigate to the **Users** tab, and click **View password** for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. To view passwords, you need the `lockbox.payloadViewer` role. {% endnote %} The available [connection pooler modes](../concepts/pooling.md) include `SESSION`, `TRANSACTION`, and `STATEMENT`. You can also specify the `--host` `replication-source` option to [manually manage replication streams](../concepts/replication.md#replication-manual). To set up a [maintenance window](../concepts/maintenance.md) (including for disabled clusters), provide `--maintenance-window type=`, where `type` takes the following values: * `anytime`: Any time (default). * `weekly`: On a schedule. For this value, also specify the following: * `day`: Day of week, i.e., `MON`, `TUE`, `WED`, `THU`, `FRI`, `SAT`, or `SUN`. * `hour`: Sequence number of 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`. To encrypt the disk with a [custom KMS key](../../kms/concepts/key.md), provide `--disk-encryption-key-id `. To learn more about disk encryption, see [Storage](../concepts/storage.md#disk-encryption). To allow access to the cluster from [Yandex Cloud Functions](../../functions/index.md), provide the `--serverless-access` parameter. For more information about setting up access, see [this Cloud Functions guide](../../functions/operations/database-connection.md). To allow access to the cluster from [Yandex Query](../../query/index.md), provide `--yandexquery-access=true`. This feature is in the [Preview](../../overview/concepts/launch-stages.md) stage and can be enabled upon request. - Terraform {#tf} With [Terraform](https://www.terraform.io/), 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](https://github.com/hashicorp/terraform/blob/main/LICENSE). The [Yandex Cloud provider for Terraform](https://github.com/yandex-cloud/terraform-provider-yandex) is distributed under the [MPL-2.0](https://www.mozilla.org/en-US/MPL/2.0/) license. For more information about the provider resources, see the guides on the [Terraform](https://www.terraform.io/docs/providers/yandex/index.html) website or [its mirror](../../terraform/index.md). If you do not have Terraform yet, [install it and configure the Yandex Cloud provider](../../tutorials/infrastructure-management/terraform-quickstart.md#install-terraform). To manage infrastructure using Terraform under a service account or user accounts (a Yandex account, a federated account, or a local user), [authenticate](../../terraform/authentication.md) using the appropriate method. To create a Managed Service for PostgreSQL cluster: 1. In the configuration file, describe the resources you want to create: * Database cluster: Description of the cluster and its hosts. * Database: Cluster database description. * User: Cluster user description. * Network: Description of the [cloud network](../../vpc/concepts/network.md#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](../../vpc/concepts/network.md#subnet) to connect the cluster hosts to. If you already have suitable subnets, you don't have to describe them again. {% note 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](cluster-backups.md) feature and specify the required network for your new cluster copy. {% endnote %} Here is an example of the configuration file structure: ```hcl resource "yandex_mdb_postgresql_cluster" "" { name = "" environment = "" network_id = "" security_group_ids = [ "" ] deletion_protection = config { version = "" resources { resource_preset_id = "" disk_type_id = "" disk_size = } pooler_config { pool_discard = pooling_mode = "" } ... } host { zone = "" name = "" subnet_id = "" assign_public_ip = } } resource "yandex_mdb_postgresql_database" "" { cluster_id = "" name = "" owner = "" depends_on = [ yandex_mdb_postgresql_user. ] } resource "yandex_mdb_postgresql_user" "" { cluster_id = "" name = "" password = "" } resource "yandex_vpc_network" "" { name = "" } resource "yandex_vpc_subnet" "" { name = "" zone = "" network_id = "" v4_cidr_blocks = [""] } ``` 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, when users and databases are created, this setting’s value is inherited from the cluster. You can also specify this setting manually. See [User management](cluster-users.md) and [Database management](databases.md) for details. If the setting is changed on a running cluster, the new value will only be inherited by users and databases with the **Same as cluster** protection level. Even with deletion protection enabled, one can still connect to the cluster manually and delete the data. * `version`: PostgreSQL version, 14, 14-1c, 15, 15-1c, 16, 16-1c, 17, 17-1c, 18 and 18-1c. * `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 maximum name length is 63 characters. The names `postgres`, `template0`, and `template1` are reserved for internal use by Managed Service for PostgreSQL. You cannot create databases using these names. The password must be from 8 to 128 characters long. {% note info %} You can also generate a password using Connection Manager. To do this, specify `generate_password = true` instead of `"password" = ""`. To view the password, select your cluster in the [management console](https://console.yandex.cloud), navigate to the **Users** tab, and click **View password** for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. To view passwords, you need the `lockbox.payloadViewer` role. {% endnote %} To configure automatic storage scaling, add the `disk_size_autoscaling` section within the `config` section: ```hcl resource "yandex_mdb_postgresql_cluster" "" { ... config { ... disk_size_autoscaling { disk_size_limit = emergency_usage_threshold = planned_usage_threshold = } ... } ... } ``` Where: * `disk_size_limit`: Maximum storage capacity after expansion, in GB. * `emergency_usage_threshold`: Storage usage percentage threshold triggering an immediate storage expansion. This is an optional setting. The default value is `0` (autoscale disabled). Values for this setting can range from `0` to `100`. * `planned_usage_threshold`: Storage usage percentage threshold triggering a storage expansion during the next maintenance window. This is an optional setting. The default value is `0` (autoscale disabled). Values for this setting can range from `0` to `100`. For more information on storage scaling rules, see [this section](../concepts/storage.md#auto-rescale). {% note warning %} * When using `planned_usage_threshold`, make sure to set up a maintenance window in the `maintenance_window` section. * If you specify both thresholds, `emergency_usage_threshold` must not be less than `planned_usage_threshold`. {% endnote %} To set up the [maintenance window](../concepts/maintenance.md) that will also apply to stopped clusters, add the `maintenance_window` section to the cluster description: ```hcl resource "yandex_mdb_postgresql_cluster" "" { ... maintenance_window { type = "" day = "" hour = } ... } ``` Where: * `type`: Maintenance type. The possible values include: * `ANYTIME`: Any time. * `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`. To encrypt the disk with a [custom KMS key](../../kms/concepts/key.md), add the `disk_encryption_key_id` parameter: ```hcl resource "yandex_mdb_postgresql_cluster" "" { ... disk_encryption_key_id = ... } ``` To learn more about disk encryption, see [Storage](../concepts/storage.md#disk-encryption). To set up statistics collection, add the `performance_diagnostics` block to the `config` section: ```hcl resource "yandex_mdb_postgresql_cluster" "" { ... config { ... performance_diagnostics { enabled = sessions_sampling_interval = statements_sampling_interval = } ... } ... } ``` Where: * `enabled`: Enables statistics collection, `true` or `false`. * `sessions_sampling_interval`: Session sampling interval, from `5` to `86400` seconds. * `statements_sampling_interval`: Statement sampling interval, from `60` to `86400` seconds. For a complete list of configurable Managed Service for PostgreSQL cluster fields, see [this Terraform provider guide](../../terraform/resources/mdb_postgresql_cluster.md). 1. 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. 1. Run this command: ```bash terraform validate ``` Terraform will show any errors found in your configuration files. 1. Create a cluster. 1. Run this command to view the planned changes: ```bash 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. 1. If everything looks correct, apply the changes: 1. Run this command: ```bash terraform apply ``` 1. Confirm updating the resources. 1. Wait for the operation to complete. {% note warning "Timeouts" %} The Terraform provider sets the following timeouts for Managed Service for PostgreSQL cluster operations: * Creating a cluster, including restoration from a backup: 30 minutes. * Updating a cluster: 60 minutes. * Deleting a cluster: 15 minutes. Operations exceeding the timeout are aborted. {% cut "How can I change these timeouts?" %} Add a `timeouts` section to the cluster description, e.g.: ```hcl resource "yandex_mdb_postgresql_cluster" "" { ... timeouts { create = "1h30m" # 1 hour 30 minutes update = "2h" # 2 hours delete = "30m" # 30 minutes } } ``` {% endcut %} {% endnote %} - REST API {#api} 1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable: ```bash export IAM_TOKEN="" ``` 1. Create a file named `body.json` and paste the following code into it: ```json { "folderId": "", "name": "", "environment": "", "networkId": "", "securityGroupIds": [ "", "", ... "" ], "deletionProtection": , "configSpec": { "version": "", "resources": { "resourcePresetId": "", "diskSize": "", "diskTypeId": "", "diskEncryptionKeyId": "" }, "access": { "dataLens": , "webSql": , "serverless": , "yandexQuery": }, "performanceDiagnostics": { "enabled": , "sessionsSamplingInterval": "", "statementsSamplingInterval": "" }, "diskSizeAutoscaling": { "plannedUsageThreshold": "", "emergencyUsageThreshold": "", "diskSizeLimit": "" } }, "databaseSpecs": [ { "name": "", "owner": "" }, { }, { ... }, { } ], "userSpecs": [ { "name": "", "password": "", "permissions": [ { "databaseName": "" } ], "login": }, { }, { ... }, { } ], "hostSpecs": [ { "zoneId": "", "subnetId": "", "assignPublicIp": }, { }, { ... }, { } ], "maintenanceWindow": { "weeklyMaintenanceWindow": { "day": "", "hour": "" } } } ``` Where: * `folderId`: Folder ID. You can get it from the [list of your cloud folders](../../resource-manager/operations/folder/get-id.md). * `name`: Cluster name. * `environment`: Cluster environment, `PRODUCTION` or `PRESTABLE`. * `networkId`: ID of the [network](../../vpc/concepts/network.md#network) where your cluster will be deployed. {% note 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](cluster-backups.md) feature and specify the required network for your new cluster copy. {% endnote %} * `securityGroupIds`: [Security group](../concepts/network.md#security-groups) IDs. * `deletionProtection`: Protection of the cluster, its databases, and users against deletion, `true` or `false` value. By default, when users and databases are created, this setting’s value is inherited from the cluster. You can also specify this setting manually. See [User management](cluster-users.md) and [Database management](databases.md) for details. If the setting is changed on a running cluster, the new value will only be inherited by users and databases with the **Same as cluster** protection level. 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](../concepts/instance-types.md). * `diskSize`: Disk size, in bytes. * `diskTypeId`: [Disk type](../concepts/storage.md). * `diskEncryptionKeyId`: KMS key ID for disk encryption. * `access`: Settings for access to the cluster from the following Yandex Cloud services: * `dataLens`: [Yandex DataLens](../../datalens/index.md) * `webSql`: [Yandex WebSQL](../../websql/index.md) * `serverless`: [Yandex Cloud Functions](../../functions/index.md) * `yandexQuery`: [Yandex Query](../../query/index.md) The possible setting values are `true` or `false`. * `performanceDiagnostics`: [Statistics collection](performance-diagnostics.md#activate-stats-collector) settings: * `enabled`: Enables statistics collection, `true` or `false`. * `sessionsSamplingInterval`: Session sampling interval. The values range from `1` to `86400` seconds. * `statementsSamplingInterval`: Statement sampling interval. The values range from `60` to `86400` seconds. * `diskSizeAutoscaling`: Storage autoscaling setting: * `plannedUsageThreshold`: Storage usage percentage threshold triggering a storage expansion during the next maintenance window. This is an optional setting. The default value is `0` (autoscale disabled). Values for this setting can range from `0` to `100`. * `emergencyUsageThreshold`: Storage usage percentage threshold triggering an immediate storage expansion. This is an optional setting. The default value is `0` (autoscale disabled). Values for this setting can range from `0` to `100`. * `diskSizeLimit`: Maximum storage capacity after expansion, in bytes. {% note warning %} * When using the `plannedUsageThreshold` setting, make sure to specify `maintenanceWindow`. * If you specify both thresholds, make sure `emergencyUsageThreshold` is greater than or equal to `plannedUsageThreshold`. {% endnote %} For more information about storage scaling rules, see [this section](../concepts/storage.md#auto-rescale). * `databaseSpecs`: Database settings as an array of elements, one per database. Each element has the following structure: * `name`: Database name. * `owner`: Database owner username. This setting must correspond to one of the usernames specified in the request. * `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. You can also generate a password using Connection Manager. Do it by specifying `"generatePassword": true` instead of `"password": ""`. To view the password, select your cluster in the [management console](https://console.yandex.cloud), navigate to the **Users** tab, and click **View password** for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. To view passwords, you need the `lockbox.payloadViewer` role. * `permissions.databaseName`: Name of the database the user can access. * `login`: User permission to connect to the DB, `true` or `false`. * `hostSpecs`: Cluster host settings as an array of elements, one per host. Each element has the following structure: * `zoneId`: [Availability zone](../../overview/concepts/geo-scope.md) * `subnetId`: [Subnet](../../vpc/concepts/network.md#subnet) ID. * `assignPublicIp`: Permission to [connect](connect/index.md) to the host from the internet, `true` or `false`. * `maintenanceWindow`: [Maintenance window](../concepts/maintenance.md) settings, applying to both running and stopped clusters. Provide one of these two parameters: * `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, 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`. 1. Call the [Cluster.Create](../api-ref/Cluster/create.md) method, e.g., via the following [cURL](https://curl.se/) request: ```bash 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" ``` 1. Check the [server response](../api-ref/Cluster/create.md#yandex.cloud.operation.Operation) to make sure your request was successful. - gRPC API {#grpc-api} 1. [Get an IAM token for API authentication](../api-ref/authentication.md) and place it in an environment variable: ```bash export IAM_TOKEN="" ``` 1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository: ```bash cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi ``` Below, we assume that the repository contents reside in the `~/cloudapi/` directory. 1. Create a file named `body.json` and paste the following code into it: ```json { "folder_id": "", "name": "", "environment": "", "network_id": "", "security_group_ids": [ "", "", ... "" ], "deletion_protection": , "config_spec": { "version": "", "resources": { "resource_preset_id": "", "disk_size": "", "disk_type_id": "", "disk_encryption_key_id": "" }, "access": { "data_lens": , "web_sql": , "serverless": , "yandex_query": }, "performance_diagnostics": { "enabled": , "sessions_sampling_interval": "", "statements_sampling_interval": "" }, "disk_size_autoscaling": { "planned_usage_threshold": "", "emergency_usage_threshold": "", "disk_size_limit": "" } }, "database_specs": [ { "name": "", "owner": "" }, { }, { ... }, { } ], "user_specs": [ { "name": "", "password": "", "permissions": [ { "database_name": "" } ], "login": }, { }, { ... }, { } ], "host_specs": [ { "zone_id": "", "subnet_id": "", "assign_public_ip": }, { }, { ... }, { } ], "maintenance_window": { "weekly_maintenance_window": { "day": "", "hour": "" } } } ``` Where: * `folder_id`: Folder ID. You can get it with the [list of folders in the cloud](../../resource-manager/operations/folder/get-id.md). * `name`: Cluster name. * `environment`: Cluster environment, `PRODUCTION` or `PRESTABLE`. * `network_id`: ID of the [network](../../vpc/concepts/network.md#network) where your cluster will be deployed. {% note 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](cluster-backups.md) feature and specify the required network for your new cluster copy. {% endnote %} * `security_group_ids`: [Security group](../concepts/network.md#security-groups) IDs. * `deletion_protection`: Protection of the cluster, its databases, and users against deletion, `true` or `false` value. By default, when users and databases are created, this setting’s value is inherited from the cluster. You can also specify this setting manually. See [User management](cluster-users.md) and [Database management](databases.md) for details. If the setting is changed on a running cluster, the new value will only be inherited by users and databases with the **Same as cluster** protection level. 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](../concepts/instance-types.md). * `disk_size`: Disk size, in bytes. * `disk_type_id`: [Disk type](../concepts/storage.md). * `disk_encryption_key_id`: KMS key ID for disk encryption. * `access`: Settings for access to the cluster from the following Yandex Cloud services: * `data_lens`: [Yandex DataLens](../../datalens/index.md) * `web_sql`: [Yandex WebSQL](../../websql/index.md) * `serverless`: [Yandex Cloud Functions](../../functions/index.md) * `yandex_query`: [Yandex Query](../../query/index.md) The possible values are `true` or `false`. * `performance_diagnostics`: [Statistics collection](performance-diagnostics.md#activate-stats-collector) settings: * `enabled`: Enables statistics collection, `true` or `false`. * `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. * `disk_size_autoscaling`: Storage autoscaling settings: * `planned_usage_threshold`: Storage usage percentage threshold triggering a storage expansion during the next maintenance window. This is an optional setting. The default value is `0` (autoscale disabled). Values for this setting can range from `0` to `100`. * `emergency_usage_threshold`: Storage usage percentage threshold triggering an immediate storage expansion. This is an optional setting. The default value is `0` (autoscale disabled). Values for this setting can range from `0` to `100`. * `disk_size_limit`: Maximum storage capacity after expansion, in bytes. {% note warning %} * When using the `planned_usage_threshold` setting, make sure to specify `maintenance_window`. * If you specify both thresholds, make sure that `emergency_usage_threshold` is greater than or equal to `planned_usage_threshold`. {% endnote %} For more information on storage scaling rules, see [this section](../concepts/storage.md#auto-rescale). * `database_specs`: Database settings as an array of elements, one for each DB. Each element has the following structure: * `name`: Database name. * `owner`: Database owner username. This setting must correspond to one of the usernames specified in the request. * `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. You can also generate a password using Connection Manager. Do it by specifying `"generate_password": true` instead of `"password": ""`. To view the password, select your cluster in the [management console](https://console.yandex.cloud), navigate to the **Users** tab, and click **View password** for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. To view passwords, you need the `lockbox.payloadViewer` role. * `permissions.database_name`: Name of the database the user can access. * `login`: User permission to connect to the DB, `true` or `false`. * `host_specs`: Cluster host settings as an array of elements, one per host. Each element has the following structure: * `zone_id`: [Availability zone](../../overview/concepts/geo-scope.md). * `subnet_id`: [Subnet](../../vpc/concepts/network.md#subnet) ID. * `assign_public_ip`: Permission for internet [access](connect/index.md) to the host. * `maintenance_window`: [Maintenance window](../concepts/maintenance.md) settings, applying to both running and stopped clusters. Provide one of these two parameters: * `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, 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`. 1. Call the [ClusterService.Create](../api-ref/grpc/Cluster/create.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request: ```bash 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 ``` 1. Check the [server response](../api-ref/grpc/Cluster/create.md#yandex.cloud.mdb.postgresql.v1.Cluster) to make sure your request was successful. {% endlist %} {% note warning %} If you specified security group IDs when creating a cluster, you may also need to [configure security groups](connect/index.md#configuring-security-groups) to connect to it. {% endnote %} ## Creating a cluster copy {#duplicate} You can create a PostgreSQL cluster with the settings of another one created earlier. Do this by importing the original PostgreSQL cluster configuration into Terraform. This way, you can either create an identical copy or use the imported configuration as the baseline and modify it as needed. The import feature is handy when the original PostgreSQL cluster has a lot of settings and you need to create a similar one. To create a PostgreSQL cluster copy: {% list tabs group=instructions %} - Terraform {#tf} 1. If you do not have Terraform yet, [install it](../../tutorials/infrastructure-management/terraform-quickstart.md#install-terraform). 1. [Get the authentication credentials](../../tutorials/infrastructure-management/terraform-quickstart.md#get-credentials). You can add them to environment variables or specify them later in the provider configuration file. 1. [Configure and initialize a provider](../../tutorials/infrastructure-management/terraform-quickstart.md#configure-provider). There is no need to create a provider configuration file manually, you can [download it](https://github.com/yandex-cloud-examples/yc-terraform-provider-settings/blob/main/provider.tf). 1. Place the configuration file in a separate working directory and [specify the parameter values](../../tutorials/infrastructure-management/terraform-quickstart.md#configure-provider). If you did not add the authentication credentials to environment variables, specify them in the configuration file. 1. In the same working directory, place a `.tf` file with the following contents: ```hcl resource "yandex_mdb_postgresql_cluster" "old" { } ``` 1. Save the ID of the original PostgreSQL cluster to an environment variable: ```bash export POSTGRESQL_CLUSTER_ID= ``` You can get the cluster ID with the [list of clusters in the folder](cluster-list.md#list-clusters). 1. Import the original PostgreSQL cluster settings to the Terraform configuration: ```bash terraform import yandex_mdb_postgresql_cluster.old ${POSTGRESQL_CLUSTER_ID} ``` 1. Get the imported configuration: ```bash terraform show ``` 1. Copy it from your terminal and paste it into the `.tf` file. 1. Create a new directory `imported-cluster` and move your configuration file there. 1. Modify the configuration so that you can use it to create a new cluster: * Specify the new cluster name in the `resource` string and the `name` parameter. * Delete `created_at`, `health`, `id`, and `status`. * In the `host` sections, delete `fqdn` and `role`. * If the `disk_size_autoscaling` section has `disk_size_limit = 0`, delete this section. * If the `maintenance_window` section contains `type = "ANYTIME"`, delete the `hour` setting. * Optionally, make further changes if you need a customized configuration. 1. [Get the authentication credentials](../../tutorials/infrastructure-management/terraform-quickstart.md#get-credentials) in the `imported-cluster` directory. 1. In the same directory, [configure and initialize the provider](../../tutorials/infrastructure-management/terraform-quickstart.md#configure-provider). Instead of manually creating the provider configuration file, you can [download it](https://github.com/yandex-cloud-examples/yc-terraform-provider-settings/blob/main/provider.tf). 1. Move the configuration file to the `imported-cluster` directory and [specify the arguments](../../tutorials/infrastructure-management/terraform-quickstart.md#configure-provider). If you have not set the authentication credentials as environment variables, specify them in the configuration file. 1. Validate your Terraform configuration: ```bash terraform validate ``` Terraform will display any configuration errors detected in your files. 1. Create the required infrastructure: 1. Run this command to view the planned changes: ```bash 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. 1. If everything looks correct, apply the changes: 1. Run this command: ```bash terraform apply ``` 1. Confirm updating the resources. 1. 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](https://console.yandex.cloud). {% note warning "Timeouts" %} The Terraform provider sets the following timeouts for Managed Service for PostgreSQL cluster operations: * Creating a cluster, including restoration from a backup: 30 minutes. * Updating a cluster: 60 minutes. * Deleting a cluster: 15 minutes. Operations exceeding the timeout are aborted. {% cut "How can I change these timeouts?" %} Add a `timeouts` section to the cluster description, e.g.: ```hcl resource "yandex_mdb_postgresql_cluster" "" { ... timeouts { create = "1h30m" # 1 hour 30 minutes update = "2h" # 2 hours delete = "30m" # 30 minutes } } ``` {% endcut %} {% endnote %} {% endlist %} ## Examples {#examples} ### Creating a single-host cluster {#creating-a-single-host-cluster} {% list tabs group=instructions %} - CLI {#cli} To create a single-host cluster, provide one `--host` parameter. Create a Managed Service for PostgreSQL cluster with the following test specifications: * Name: `mypg`. * Environment: `production`. * Network: `default`. * Security group: `enp6saqnq4ie244g67sb`. * One `s2.micro` host in the `b0rcctk2rvtr********` subnet and `ru-central1-a` availability zone. * Network SSD storage (`network-ssd`): 20 GB. * User: `user1`, password: `user1user1`. * Database: `db1`, owner: `user1`. * Deletion protection for the cluster, its databases, and users: Enabled. Run this command: ```bash yc managed-postgresql cluster create \ --name mypg \ --environment production \ --network-name default \ --resource-preset s2.micro \ --host zone-id=ru-central1-a,subnet-id=b0rcctk2rvtr******** \ --disk-type network-ssd \ --disk-size 20 \ --user name=user1,password=user1user1 \ --database name=db1,owner=user1 \ --security-group-ids enp6saqnq4ie244g67sb \ --deletion-protection ``` - Terraform {#tf} Create a Managed Service for PostgreSQL cluster and its network with the following test specifications: * Name: `mypg`. * Version: `18`. * Environment: `PRESTABLE`. * Cloud ID: `b1gq90dgh25bebiu75o`. * Folder ID: `b1gia87mbaomkfvsleds`. * Network: `mynet`. * Security group allowing internet access to the cluster on port `6432`: `pgsql-sg`. * Host class: `s2.micro` (one host), subnet: `mysubnet`, availability zone: `ru-central1-a`. `mysubnet` CIDR 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: ```hcl 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 = 18 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" depends_on = [ yandex_mdb_postgresql_user.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" ] } } ``` {% endlist %}