Updating PostgreSQL cluster settings

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 change the host class for the cluster:

1. View a description of the update cluster CLI command:

   yc managed-postgresql cluster update --help
   

2. Request a list of available host classes (the ZONE IDS column specifies the availability zones where you can select the appropriate class):

   yc managed-postgresql resource-preset list
   

   +-----------+--------------------------------+-------+----------+
   |    ID     |            ZONE IDS            | CORES |  MEMORY  |
   +-----------+--------------------------------+-------+----------+
   | s1.micro  | ru-central1-a, ru-central1-b,  |     2 | 8.0 GB   |
   |           | ru-central1-d                  |       |          |
   | ...                                                           |
   +-----------+--------------------------------+-------+----------+
   

3. Specify the class in the update cluster command:

   yc managed-postgresql cluster update <cluster_name_or_ID> \
       --resource-preset <host_class_ID>
   

   Managed Service for PostgreSQL will run the update host class command for the cluster.

1. Open the current Terraform configuration file with an infrastructure plan.

   For more information about creating this file, see Creating clusters.

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

2. In the Managed Service for PostgreSQL cluster description, change the resource_preset_id attribute value under config.resources:

   resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
     ...
     config {
       resources {
         resource_preset_id = "<host_class>"
         ...
       }
     }
   }
   

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

4. Confirm updating the resources.

   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 place it in the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the Cluster.update method and make a request, e.g., via cURL:

   Warning

   This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the updateMask parameter (one line separated by commas).

   curl \
     --request PATCH \
     --header "Authorization: Bearer $IAM_TOKEN" \
     --header "Content-Type: application/json" \
     --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>' \
     --data '{
               "updateMask": "configSpec.resources.resourcePresetId",
               "configSpec": {
                 "resources": {
                   "resourcePresetId": "<host_class>"
                 }
               }
             }'
   

   Where:

   • updateMask: List of parameters to update as a single string, separated by commas.

     In this case, only one parameter is provided.

   • configSpec.resources.resourcePresetId: New host class.

   You can get the cluster ID with a list of clusters in the folder.

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

1. Get an IAM token for API authentication and place it in 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. Use the ClusterService/Update call and make a request, e.g., via gRPCurl:

   Warning

   This API method will assign default values to all object parameters not explicitly set in the request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] strings.

   Format for listing settings

   "update_mask": {
       "paths": [
           "<setting_1>",
           "<setting_2>",
           ...
           "<setting_N>"
       ]
   }
   

   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 '{
           "cluster_id": "<cluster_ID>",
           "update_mask": {
             "paths": [
               "config_spec.resources.resource_preset_id"
             ]
           },
           "config_spec": {
             "resources": {
               "resource_preset_id": "<host_class>"
             }
           }
         }' \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.postgresql.v1.ClusterService.Update
   

   Where:

   • update_mask: List of parameters to update as an array of paths[] strings.

     In this case, only one parameter is provided.

   • config_spec.resources.resource_preset_id: New host class.

   You can get the cluster ID with a list of clusters in the folder.

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

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 update the PostgreSQL settings:

1. View the full list of settings specified for the cluster:

   yc managed-postgresql cluster get <cluster_name_or_ID> --full
   

2. View a description of the update cluster configuration CLI command:

   yc managed-postgresql cluster update-config --help
   

3. Set the required parameter values:

   All supported parameters are listed in the request format for the update method, in the following field: postgresqlConfig_<PostgreSQL_version>. To specify a parameter name in the CLI call, convert the name from lowerCamelCase to snake_case. For example, the maxPreparedTransactions parameter from an API call must be converted to max_prepared_transactions for the CLI command:

   yc managed-postgresql cluster update-config <cluster_name_or_ID> \
      --set <parameter_1_name>=<value_1>,<parameter_2_name>=<value_2>,...
   

   Managed Service for PostgreSQL runs the update cluster settings operation.

1. Open the current Terraform configuration file with an infrastructure plan.

   For more information about creating this file, see Creating clusters.

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

2. In the Managed Service for PostgreSQL cluster description, change the values of the parameters in config.postgresql_config. If there is no such section, create one.

   resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
     ...
     config {
       ...
       postgresql_config = {
         max_connections                   = <maximum_number_of_connections>
         enable_parallel_hash              = <true_or_false>
         vacuum_cleanup_index_scale_factor = <number_between_0_and_1>
         ...
       }
     }
   }
   

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

4. Confirm updating the resources.

   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 place it in the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the Cluster.update method and make a request, e.g., via cURL:

   Warning

   This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the updateMask parameter (one line separated by commas).

   curl \
     --request PATCH \
     --header "Authorization: Bearer $IAM_TOKEN" \
     --header "Content-Type: application/json" \
     --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>' \
     --data '{
               "updateMask": "configSpec.postgresqlConfig_<PostgreSQL_version>.<setting_1>,...,configSpec.postgresqlConfig_<PostgreSQL_version>.<setting_N>",
               "configSpec": {
                 "postgresqlConfig_<PostgreSQL_version>": {
                   "<setting_1>": "<value_1>",
                   "<setting_2>": "<value_2>",
                   ...
                   "<setting_N>": "<value_N>"
                 }
               }
             }'
   

   Where:

   • updateMask: List of parameters to update as a single string, separated by commas.

     In this case, specify all the PostgreSQL settings to update.

   • configSpec.postgresqlConfig_<PostgreSQL_version>: PostgreSQL settings. Use a separate line for each setting, separated by commas.

     See the method description for the list of PostgreSQL versions available for the parameter. See Cluster-level settings for description and possible values for each setting.

   You can get the cluster ID with a list of clusters in the folder.

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

1. Get an IAM token for API authentication and place it in 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. Use the ClusterService/Update call and make a request, e.g., via gRPCurl:

   Warning

   This API method will assign default values to all object parameters not explicitly set in the request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] strings.

   Format for listing settings

   "update_mask": {
       "paths": [
           "<setting_1>",
           "<setting_2>",
           ...
           "<setting_N>"
       ]
   }
   

   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 '{
           "cluster_id": "<cluster_ID>",
           "update_mask": {
             "paths": [
               "config_spec.postgresql_config_<PostgreSQL_version>.<setting_1>",
               "config_spec.postgresql_config_<PostgreSQL_version>.<setting_2>",
               ...,
               "config_spec.postgresql_config_<PostgreSQL_version>.<setting_N>"
             ]
           },
           "config_spec": {
             "postgresql_config_<PostgreSQL_version>": {
               "<setting_1>": "<value_1>",
               "<setting_2>": "<value_2>",
               ...
               "<setting_N>": "<value_N>"
             }
           }
         }' \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.postgresql.v1.ClusterService.Update
   

   Where:

   • update_mask: List of parameters to update as an array of paths[] strings.

     In this case, specify all the PostgreSQL settings to update.

   • config_spec.postgresql_config_<PostgreSQL_version>: PostgreSQL settings. Use a separate line for each setting, separated by commas.

     See the method description for the list of PostgreSQL versions available for the parameter. See Cluster-level settings for description and possible values for each setting.

   You can get the cluster ID with a list of clusters in the folder.

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

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 change additional cluster settings:

1. View a description of the update cluster CLI command:

   yc managed-postgresql cluster update --help
   

2. Run the following command with a list of settings to update:

   yc managed-postgresql cluster update <cluster_name_or_ID> \
       --backup-window-start <backup_start_time> \
       --datalens-access=<true_or_false> \
       --maintenance-window type=<maintenance_type>,`
                           `day=<day_of_week>,`
                           `hour=<hour> \
       --websql-access=<true_or_false> \
       --deletion-protection=<deletion_protection> \
       --connection-pooling-mode=<connection_pooler_mode> \
       --serverless-access=<true_or_false> \
       --yandexquery-access=<access_via_Query> \
       --performance-diagnostics enabled=<true_or_false>,`
                                `sessions-sampling-interval=<session_sampling_interval>,`
                                `statements-sampling-interval=<statement_sampling_interval>
   

You can change the following settings:

• --backup-window-start: The cluster backup start time, set in UTC format HH:MM:SS. If the time is not set, the backup will start at 22:00 UTC.

• --datalens-access: Enables access from DataLens. The default value is false. For more information on setting up a connection, see Connecting to a cluster from DataLens.

• --maintenance-window: Maintenance window settings (including for disabled clusters), where type is the maintenance type:

  • anytime (default): Any time.
  • weekly: On a schedule. If setting this value, specify the day of week and the hour:
    • day: Day of week in DDD format: MON, TUE, WED, THU, FRI, SAT, or SUN.
    • hour: Hour (UTC) in HH format: 1 to 24.

• --websql-access: Enables you to run SQL queries against cluster databases from the Yandex Cloud management console using Yandex WebSQL. The default value is false.

• --serverless-access: Enables cluster access from Yandex Cloud Functions. The default value is false. For more information about setting up access, see the Cloud Functions documentation.

• --yandexquery-access: Enables cluster access from Yandex Query. This feature is at the Preview stage and provided upon request.

• --autofailover: Manages automatic master change setup. To learn more, see Replication. The default value is true.

• --connection-pooling-mode: Specifies the connection pooler mode (SESSION, TRANSACTION, or STATEMENT).

• --deletion-protection: Protection of the cluster, its databases, and users against accidental 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.

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

• --performance-diagnostics: Settings for collecting statistics:

  • enabled: If true, enables collecting statistics. The default value is false.
  • sessions-sampling-interval: Session sampling interval, seconds. The values range from 1 to 86400.
  • statements-sampling-interval: Statement sampling interval, seconds. The values range from 60 to 86400.

You can get the cluster name with a list of clusters in the folder.

1. Open the current Terraform configuration file with an infrastructure plan.

   For more information about creating this file, see Creating clusters.

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

2. To change the backup start time, add a config.backup_window_start block to the Managed Service for PostgreSQL cluster description:

   resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
     ...
     config {
       backup_window_start {
         hours   = <backup_start_hour>
         minutes = <backup_start_minute>
       }
       ...
     }
   }
   

3. To enable access from Yandex DataLens and allow execution of SQL queries from the management console using Yandex WebSQL, change the values of the appropriate fields in the config.access section:

   resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
     ...
     config {
       access {
         data_lens = <access_from_DataLens>
         web_sql   = <run_SQL_queries_from_management_console>
         ...
     }
     ...
   }
   

   Where:

   • data_lens: Access from DataLens, true or false.
   • web_sql: Execution of SQL queries from the management console using Yandex WebSQL (true or false).

4. To change the connection pooler mode, add the config.pooler_config section to the Managed Service for PostgreSQL cluster description:

   resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
     ...
     config {
       pooler_config {
         pool_discard = <Odyssey_parameter>
         pooling_mode = "<operation_mode>"
       }
       ...
     }
   }
   

   Where:

   • pool_discard: Odyssey pool_discard parameter, true or false.
   • pooling_mode: Operation mode, SESSION, TRANSACTION, or STATEMENT.

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

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

7. To enable protection of the cluster, its databases, and users against accidental deletion, add the deletion_protection field set to true to your cluster description:

   resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
     ...
     deletion_protection = <deletion_protection>
   }
   

   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.

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

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

9. Confirm updating the resources.

   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 place it in the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the Cluster.update method and make a request, e.g., via cURL:

   Warning

   This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the updateMask parameter (one line separated by commas).

   curl \
     --request PATCH \
     --header "Authorization: Bearer $IAM_TOKEN" \
     --header "Content-Type: application/json" \
     --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>' \
     --data '{
               "updateMask": "configSpec.poolerConfig,configSpec.backupWindowStart,configSpec.backupRetainPeriodDays,configSpec.access,configSpec.performanceDiagnostics.sessionsSamplingInterval,configSpec.performanceDiagnostics.statementsSamplingInterval,maintenanceWindow,deletionProtection",
               "configSpec": {
                 "poolerConfig": {
                   "poolingMode": "<connection_pooling_mode>",
                   "poolDiscard": <discard_client_state_after_each_transaction:_true_or_false>
                 },
                 "backupWindowStart": {
                   "hours": "<hours>",
                   "minutes": "<minutes>",
                   "seconds": "<seconds>",
                   "nanos": "<nanoseconds>"
                 },
                 "backupRetainPeriodDays": "<number_of_days>",
                 "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>"
                 }
               },
               "maintenanceWindow": {
                 "weeklyMaintenanceWindow": {
                   "day": "<day_of_week>",
                   "hour": "<hour>"
                 }
               },
               "deletionProtection": <deletion_protection:_true_or_false>
             }'
   

   Where:

   • updateMask: List of parameters to update as a single string, separated by commas.

   • configSpec: Cluster settings:

     • poolerConfig: Connection pooler settings:

       • poolingMode: Connection pooler's operation mode. Possible values: SESSION, TRANSACTION, and STATEMENT. For more information on each of the modes, see Managing connections.
       • poolDiscard: Whether clients discard their states after each transaction. Similar to the server_reset_query_always for the PgBouncer connection pooler.

     • backupWindowStart: Backup window settings.

       In the parameter, specify the time to start backup. Possible values:

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

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

     • access: Settings of the cluster 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: Settings for collecting statistics:

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

   • maintenanceWindow: Maintenance window settings (including for disabled clusters). In maintenanceWindow, provide one of the two parameters:

     • ANYTIME: Maintenance can take place at any time.

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

       • day: Day of week, in DDD format.
       • hour: Hour, in HH format. The values range from 1 to 24 hours.

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

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

   You can get the cluster ID with a list of clusters in the folder.

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

1. Get an IAM token for API authentication and place it in 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. Use the ClusterService/Update call and make a request, e.g., via gRPCurl:

   Warning

   This API method will assign default values to all object parameters not explicitly set in the request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] strings.

   Format for listing settings

   "update_mask": {
       "paths": [
           "<setting_1>",
           "<setting_2>",
           ...
           "<setting_N>"
       ]
   }
   

   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 '{
           "cluster_id": "<cluster_ID>",
           "update_mask": {
             "paths": [
               "config_spec.pooler_config",
               "config_spec.backup_window_start",
               "config_spec.backup_retain_period_days",
               "config_spec.access",
               "config_spec.performance_diagnostics.sessions_sampling_interval",
               "config_spec.performance_diagnostics.statements_sampling_interval",
               "maintenance_window",
               "deletion_protection"
             ]
           },
           "config_spec": {
             "pooler_config": {
               "pooling_mode": "<connection_pooling_mode>",
               "pool_discard": <discard_client_state_after_each_transaction:_true_or_false>
             },
             "backup_window_start": {
               "hours": "<hours>",
               "minutes": "<minutes>",
               "seconds": "<seconds>",
               "nanos": "<nanoseconds>"
             },
             "backup_retain_period_days": "<number_of_days>",
             "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>"
             }
           },
           "maintenance_window": {
             "weekly_maintenance_window": {
               "day": "<day_of_week>",
               "hour": "<hour>"
             }
           },
           "deletion_protection": <deletion_protection:_true_or_false>
         }' \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.postgresql.v1.ClusterService.Update
   

   • update_mask: List of parameters to update as an array of paths[] strings.

   • config_spec: Cluster settings:

     • pooler_config: Connection pooler settings:

       • pooling_mode: Connection pooler's operation mode. Possible values: SESSION, TRANSACTION, and STATEMENT. For more information on each of the modes, see Managing connections.
       • pool_discard: Whether clients discard their states after each transaction. Similar to the server_reset_query_always for the PgBouncer connection pooler.

     • backup_window_start: Backup window settings.

       In the parameter, specify the time to start backup. Possible values:

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

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

     • access: Settings of the cluster 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: Settings for collecting statistics:

       • enabled: Enable collecting statistics.
       • 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.

   • maintenance_window: Maintenance window settings (including those for disabled clusters). In maintenance_window, provide one of the two parameters:

     • ANYTIME: Maintenance can take place at any time.

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

       • day: Day of week, in DDD format.
       • hour: Hour, in HH format. The values range from 1 to 24 hours.

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

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

   You can get the cluster ID with a list of clusters in the folder.

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

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.

Run this command:

yc managed-postgresql cluster start-failover <cluster_name_or_ID> \
    --host <replica_host_name>

You can request the replica host name with a list of cluster hosts and the cluster name with a list of clusters in the folder.

1. Open the current Terraform configuration file with an infrastructure plan.

   For more information about creating this file, see Creating clusters.

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

2. In the host_master_name parameter, specify the name of the replica host to switch to.

   resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
     ...
     host_master_name = "<replica_host_name>"
   }
   

   Where host_master_name is the name of the replica host, i.e., the name attribute of the appropriate host section.

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

4. Confirm updating the resources.

   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 place it in the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the Cluster.startFailover method and make a 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/<cluster_ID>:startFailover' \
     --data '{
               "hostName": "<host_FQDN>"
             }'
   

   Where hostName is the FQDN of the replica to become the master host.

   You can get the cluster ID with a list of clusters in the folder.

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

1. Get an IAM token for API authentication and place it in 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. Use the ClusterService/StartFailover call and make a 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 '{
           "cluster_id": "<cluster_ID>",
           "host_name": "<host_FQDN>"
         }' \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.postgresql.v1.ClusterService.StartFailover
   

   Where host_name is the FQDN of the replica to become the master host.

   You can get the cluster ID with a list of clusters in the folder.

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

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 move a cluster:

1. View a description of the CLI move cluster command:

   yc managed-postgresql cluster move --help
   

2. Specify the destination folder in the move cluster command:

   yc managed-postgresql cluster move <cluster_ID> \
      --destination-folder-name=<destination_folder_name>
   

   You can get the cluster ID with a list of clusters in the folder.

1. Get an IAM token for API authentication and place it in the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the Cluster.move method and make a 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/<cluster_ID>:move' \
     --data '{
               "destinationFolderId": "<folder_ID>"
             }'
   

   Where destinationFolderId is the ID of the folder to which you want to move your cluster. You can fetch this ID along with the list of folders in the cloud.

   You can get the cluster ID with a list of clusters in the folder.

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

1. Get an IAM token for API authentication and place it in 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. Use the ClusterService/Move call and make a 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 '{
           "cluster_id": "<cluster_ID>",
           "destination_folder_id": "<folder_ID>"
         }' \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.postgresql.v1.ClusterService.Move
   

   Where destination_folder_id is the ID of the folder to which you want to move your cluster. You can fetch this ID along with the list of folders in the cloud.

   You can get the cluster ID with a list of clusters in the folder.

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

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 edit the list of security groups for your cluster:

1. View a description of the update cluster CLI command:

   yc managed-postgresql cluster update --help
   

2. Specify the security groups in the update cluster command:

   yc managed-postgresql cluster update <cluster_name_or_ID> \
       --security-group-ids <list_of_security_group_IDs>
   

1. Open the current Terraform configuration file with an infrastructure plan.

   For more information about creating this file, see Creating clusters.

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

2. Change the value of the security_group_ids parameter in the cluster description:

   resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
     ...
     security_group_ids = [ <list_of_security_group_IDs> ]
   }
   

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

4. Confirm updating the resources.

   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 place it in the environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the Cluster.update method and make a request, e.g., via cURL:

   Warning

   This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the updateMask parameter (one line separated by commas).

   curl \
     --request PATCH \
     --header "Authorization: Bearer $IAM_TOKEN" \
     --header "Content-Type: application/json" \
     --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>' \
     --data '{
               "updateMask": "securityGroupIds",
               "securityGroupIds": [
                 "<security_group_1_ID>",
                 "<security_group_2_ID>",
                 ...
                 "<security_group_N_ID>"
               ]
             }'
   

   Where:

   • updateMask: List of parameters to update as a single string, separated by commas.

     In this case, only one parameter is provided.

   • securityGroupIds: New list of security groups, in the array-like form.

   You can get the cluster ID with a list of clusters in the folder.

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

1. Get an IAM token for API authentication and place it in 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. Use the ClusterService/Update call and make a request, e.g., via gRPCurl:

   Warning

   This API method will assign default values to all object parameters not explicitly set in the request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] strings.

   Format for listing settings

   "update_mask": {
       "paths": [
           "<setting_1>",
           "<setting_2>",
           ...
           "<setting_N>"
       ]
   }
   

   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 '{
           "cluster_id": "<cluster_ID>",
           "update_mask": {
             "paths": [
               "security_group_ids"
             ]
           },
           "security_group_ids": [
             "<security_group_1_ID>",
             "<security_group_2_ID>",
             ...
             "<security_group_N_ID>"
           ]
         }' \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.postgresql.v1.ClusterService.Update
   

   Where:

   • update_mask: List of parameters to update as an array of paths[] strings.

     In this case, only one parameter is provided.

   • security_group_ids: New list of security groups, in the array-like form.

   You can get the cluster ID with a list of clusters in the folder.

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