Get started
Yandex Managed Service for PostgreSQL

Changing cluster settings

Written by

After creating a cluster, you can:

Warning

You can't change PostgreSQL server settings using SQL commands.

Change the host class

  1. Go to the folder page and select Managed Service for PostgreSQL.
  2. Select the cluster and click Edit cluster in the top panel.
  3. Under Host class, select the class for the PostgreSQL hosts.
  4. Click Save changes.

If you don't 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 CLI's update cluster command:

    $ yc managed-postgresql cluster update --help

  2. Request a list of available host classes (the ZONES 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-c                  |       |          |
| ...                                                           |
+-----------+--------------------------------+-------+----------+

  3. Specify the class in the update cluster command:

    $ yc managed-postgresql cluster update <cluster name>
     --resource-preset <class ID>

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

You can change the host class using the API update method: pass the necessary value in the request parameter configSpec.resources.resourcePresetId.

To request a list of supported values, use the list method for the ResourcePreset resources.

Increasing storage size

  1. Go to the folder page and select Managed Service for PostgreSQL.
  2. Select the cluster and click Edit cluster in the top panel.
  3. Under Storage size, specify the required value.
  4. Click Save changes.

If you don't 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 increase the storage size for a cluster:

  1. Make sure the required cluster uses network storage (it's currently not possible to increase the size of local storage). To do this, request information about the cluster and find the disk_type_id field: it should be set to network-hdd or network-ssd:

    $ yc managed-postgresql cluster get <cluster name>

id: c7qkvr3u78qiopj3u4k2
folder_id: b1g0ftj57rrjk9thribv
...
config:
  ...
  resources:
    resource_preset_id: s1.micro
    disk_size: "10737418240"
    disk_type_id: network-ssd
...

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

    $ yc managed-postgresql cluster update --help

  3. Make sure the cloud's quota is sufficient to increase the storage size: open the Quotas page for your cloud and check that the Yandex Managed Service for PostgreSQL section still has space available in the space line.

  4. Specify the required amount of storage in the update cluster command (it must be at least as large as disk_size in the cluster properties):

    $ yc managed-postgresql cluster update <cluster name>
     --disk-size <storage size in GB>

    If all these conditions are met, Managed Service for PostgreSQL launches the operation to increase storage space.

You can change the storage size for a cluster using the API update method: pass the appropriate values in the request parameter configSpec.postgresqlConfig_<version>.resources.diskSize.

Make sure the cloud's quota is sufficient to increase the storage size: open the Quotas page for your cloud and check that the Yandex Managed Service for PostgreSQL section still has space available in the space line.

Changing PostgreSQL settings

You can change the DBMS settings for the hosts in your cluster, both the default ones and those changing with the host class.

When the host class changes, Managed Service for PostgreSQL automatically changes the following settings (if they weren't set manually):

  • max_connections
  • shared_buffers
  • min_wal_size
  • max_wal_size
  • autovacuum_max_workers
  • autovacuum_vacuum_cost_delay
  • autovacuum_vacuum_cost_limit

The settings you set manually will no longer change automatically. Exceptions may occur if the set value becomes invalid when changing the host class: for example, you can't set max_connections to 800 and then change the cluster host class to s2.micro (for more information about the maximum number of connections, see Creating PostgreSQL clusters.

If you don't 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 PostgreSQL server settings:

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

    $ yc managed-postgresql cluster get <cluster name> --full

  2. View a description of the CLI's update cluster configuration 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 postgresqlConfig_<version> field. To specify the parameter name in the CLI's call, convert the name from lowerCamelCase to snake_case. For example, the logMinDurationStatement parameter from an API request should be converted to log_min_duration_statement for the CLI command:

    $ yc managed-postgresql cluster update-config <cluster name>
     --set log_min_duration_statement=100,<parameter name>=<value>,...

    Managed Service for PostgreSQL runs the update cluster settings operation.

You can change the DBMS settings for a cluster using the API update method: pass the appropriate values in the request parameter configSpec.postgresqlConfig_<version>.config.

Changing additional cluster settings

  1. Go to the folder page and select Managed Service for PostgreSQL.

  2. Select the cluster and click Edit cluster in the top panel.

  3. Change additional cluster settings:

    • Backup start (UTC): The time in UTC when you want to start creating a backup of a cluster (in 24-hour format). If the time is not set, the backup will start at 22:00 UTC.

    • Maintenance window: Settings of the technical support window. You can use the settings to specify the preferred start time for cluster host maintenance (for example, you can select the time when the cluster is least loaded with requests):

      • To specify the preferred start time for the maintenance window, select by schedule and set the desired day of the week and hour of day in UTC (Coordinated Universal Time) using the drop-down lists.
      • To allow maintenance at any time, select arbitrary.

      Maintenance may include updating the DBMS version, applying patches, and so on.

    • Access from DataLens: Enable this option to be able to analyze data from the cluster in Yandex DataLens. For more information about setting up a connection, see Connecting to DataLens.

    • Access from the management console: Select this option to be able to execute SQL queries to cluster databases from the Yandex.Cloud management console.

    • Collect statistics: Enable this option to use the Performance diagnostics tool in the cluster. This feature is at the Preview stage.

If you don't 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 CLI's update cluster command:

    yc managed-postgresql cluster update --help

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

    yc managed-postgresql cluster update <cluster name> \
    --backup-window-start <backup start time> \
    --datalens-access=<true or false> \
    --maintenance-window type=<weekly or anytime> \
    --websql-access=<true or false>

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 DataLens access. Default value: false. For more information about how to connect to DataLens, see Connecting to DataLens.
  • --maintenance-window: The maintenance window settings. You can use the settings to specify the preferred start time for cluster host maintenance (for example, you can select the time when the cluster is least loaded with requests):
    • type=anytime: Perform maintenance at any time.
    • type=weekly: Perform maintenance on schedule. In the day and hour parameters, specify the preferred day and hour of the start of the maintenance window, separated by a comma. For example: type=weekly,day=wed,hour=14.
      By default, maintenance can be performed at any time.
  • --websql-access: Enables SQL queries to be run from the management console. Default value: false.

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

Use the update API method and pass the required values in the configSpec.access and configSpec.backupWindowStart request parameters.

Setting the operation mode for the connection pooler

You can set session mode or transaction mode for the connection pooler. For more information, see Managing connections.

If you don't 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 connection pooler's operation mode:

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

    $ yc managed-postgresql cluster update --help

  2. Specify the necessary operation mode using the --connection-pooling-mode flag:

    $ yc managed-postgresql cluster update <cluster name>
     --connection-pooling-mode <SESSION or TRANSACTION>

    Managed Service for PostgreSQL runs the operation to change the connection pooler mode.

You can change the connection pooler's operation mode for a cluster using the update API method: pass the appropriate value in the configSpec.poolerConfig.poolingMode request parameter.

Switching the master

In a failover PostgreSQL cluster with multiple hosts, you can switch over the master role from the current master host to a replica host in the cluster. After this operation, the current master host becomes a replica host for the new master.

Specifics of switching master hosts in Managed Service for PostgreSQL

  1. You can't switch the master host over to a replica that explicitly specifies the source of the replication thread.
  2. If you don't specify the replica host name explicitly, the master host will switch over to a synchronous replica.

For more information, see Replication.

To switch the master:

  1. Go to the folder page and select Managed Service for PostgreSQL.
  2. Click on the name of the cluster you want and select the Hosts tab.
  3. Click Switching the master.
  4. To switch the master to a synchronous replica, leave the Select master host automatically option enabled.
  5. To switch the master over to another replica, disable the Select master host automatically option and then select the desired replica from the drop-down list.
  6. Click Switch.

If you don't 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 switch the master, run the command:

$ yc managed-postgresql cluster start-failover <cluster name> --host <replica host name>

The replica host name can be requested with a list of cluster hosts, and the cluster name can be requested with a list of clusters in the folder.

To switch the master, use the startFailover API method and pass the following in the request:

  1. In the clusterId parameter, the ID of the cluster where you want to switch the master. To find out the cluster ID, get a list of clusters in the folder.
  2. In the hostName parameter, the name of the replica host to switch to. To find out the name, request a list of hosts in the cluster.

Changing security groups

  1. Go to the folder page and select Managed Service for PostgreSQL.
  2. Select the cluster and click Edit cluster in the top panel.
  3. Under Network settings, select security groups for cluster network traffic.

If you don't 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 CLI's update cluster command:

    $ yc managed-postgresql cluster update --help

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

    $ yc managed-postgresql cluster update <cluster name>
     --security-group-ids <list of security groups>

To edit the list of cluster security groups, use the update API method and pass the following in the request:

  • The cluster ID in the clusterId parameter. To find out the cluster ID, get a list of clusters in the folder.
  • The list of groups, in the securityGroupIds parameter.
  • The list of settings to update, in the updateMask parameter. If this parameter is omitted, the API method resets any cluster settings that aren't explicitly specified in the request to their default values.

Warning

You may need to additionally set up the security groups to connect to the cluster.

In this article: