Managing PostgreSQL cluster hosts

Written by

Updated at October 28, 2024

Getting a list of cluster hosts
Creating a host
Changing a host
Removing a host

You can add and remove cluster hosts and manage their settings. For information about moving cluster hosts to a different availability zone, see this guide.

Getting a list of cluster hosts

Management console

CLI

REST API

gRPC API

Go to the folder page and select Managed Service for PostgreSQL.
Click the name of the cluster you need and select the Hosts tab.

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 get a list of cluster hosts, run the command:

yc managed-postgresql host list \
  --cluster-name <cluster_name>

Result:

+----------------------------+----------------------+---------+--------+--------------------+
|            NAME            |      CLUSTER ID      |  ROLE   | HEALTH |      ZONE ID       |
+----------------------------+----------------------+---------+--------+--------------------+
| rc1b***mdb.yandexcloud.net | c9qp71dk1dfg******** | MASTER  | ALIVE  | ru-central1-b      |
| rc1a***mdb.yandexcloud.net | c9qp71dk1dfg******** | REPLICA | ALIVE  | ru-central1-a      |
+----------------------------+----------------------+---------+--------+--------------------+

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

Get an IAM token for API authentication and place it in the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```

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

curl \
  --request GET \
  --header "Authorization: Bearer $IAM_TOKEN" \
  --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/hosts'

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

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

Get an IAM token for API authentication and place it in the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```
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.

Use the ClusterService/ListHosts 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>"
      }' \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.postgresql.v1.ClusterService.ListHosts

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

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

Creating a host

The number of hosts in Managed Service for PostgreSQL clusters is limited by the CPU and RAM quotas available to DB clusters in your cloud. To check the resources in use, open the Quotas page and find the Managed Databases section.

Management console

CLI

Terraform

REST API

gRPC API

To create a host:

Go to the folder page and select Managed Service for PostgreSQL.
Click the cluster name and go to the Hosts tab.
Click Create host.
Specify the host parameters:
- Availability zone.
- Subnet (if the required subnet is not on the list, create it).
- Replication source (if you use cascading replication).
- Select Public access if the host must be accessible from outside Yandex Cloud.

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

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To create a host:

Request a list of cluster subnets to select one for the new host:

yc vpc subnet list

Result:

+----------------------+-----------+----------------------+---------------+-------------------+
|          ID          |   NAME    |      NETWORK ID      |      ZONE     |      RANGE        |
+----------------------+-----------+----------------------+---------------+-------------------+
| b0cl69q1w2e3******** | default-d | enp6rq71w2e3******** | ru-central1-d | [172.16.**.**/20] |
| e2lkj9q1w2e3******** | default-b | enp6rq71w2e3******** | ru-central1-b | [10.10.**.**/16]  |
| e9b0phq1w2e3******** | a-2       | enp6rq71w2e3******** | ru-central1-a | [172.16.**.**/20] |
| e9b9v2q1w2e3******** | default-a | enp6rq71w2e3******** | ru-central1-a | [172.16.**.**/20] |
+----------------------+-----------+----------------------+---------------+-------------------+

If the required subnet is not in the list, create it.

View a description of the CLI command for adding a host:
```
yc managed-postgresql host add --help
```
Run the add host command:
```
yc managed-postgresql host add
  --cluster-name <cluster_name>
  --host zone-id=<availability_zone>,subnet-id=<subnet_ID>
```
The subnet ID should be specified if the availability zone contains multiple subnets; otherwise, Managed Service for PostgreSQL will automatically select a single subnet. You can request the cluster name with a list of clusters in the folder.

You can also specify several additional options in the --host parameter to manage public access to a host and replication in a cluster:
- Replication source for the host in the replication-source option to manually manage replication threads.
- Host availability from outside Yandex Cloud in the assign-public-ip option:
  - true: Public access enabled.
  - false: Public access disabled.

Managed Service for PostgreSQL will run the add host operation.

To create a host:

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.

Add a host section to the Managed Service for PostgreSQL cluster description:

resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
  ...
  host {
    name                    = "<host_name>"
    zone                    = "<availability_zone>"
    subnet_id               = "<subnet_ID>"
    replication_source_name = "<replication_source>"
    assign_public_ip        = <public_access_to_host>
  }
}

Where:

name: Host name.
zone: Availability zone.
subnet_id: Subnet ID.
replication_source_name: Replication source, i.e., the name attribute of the corresponding host section.
assign_public_ip: Public access to the host, true or false.

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

Get an IAM token for API authentication and place it in the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```

Use the Cluster.addHosts 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>/hosts:batchCreate' \
  --data '{
            "hostSpecs": [
              {
                "zoneId": "<availability_zone>",
                "subnetId": "<subnet_ID>",
                "assignPublicIp": <deletion_protection:_true_or_false>,
                "replicationSource": "<host_FQDN>",
                "priority": "<host_priority>",
                "configSpec": {
                  "postgresqlConfig_<PostgreSQL_version>": {
                    <PostgreSQL_settings>
                  }
                }
              },
              { <similar_settings_for_new_host_2> },
              { ... },
              { <similar_settings_for_new_host_N> }
            ]
          }'

Where hostSpecs is an array of new hosts. One array element contains settings for a single host and has the following structure:

zoneId: Availability zone.
subnetId: Subnet ID.
assignPublicIp: Internet access to the host via a public IP address.
replicationSource: Replication source for the host to manually manage replication threads. In the parameter, specify the FQDN of the host to be used as a replication source.
priority: Host priority among all hosts.
configSpec.postgresqlConfig_<PostgreSQL_version>: PostgreSQL settings. Use a separate line for each setting; separate them by commas.

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

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

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

Get an IAM token for API authentication and place it in the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```
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.

Use the ClusterService/AddHosts 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_specs": [
          {
            "zone_id": "<availability_zone>",
            "subnet_id": "<subnet_ID>",
            "assign_public_ip": <deletion_protection:_true_or_false>,
            "replication_source": "<host_FQDN>",
            "priority": "<host_priority>",
            "config_spec": {
              "postgresql_config_<PostgreSQL_version>": {
                <PostgreSQL_settings>
              }
            }
          },
          { <similar_settings_for_new_host_2> },
          { ... },
          { <similar_settings_for_new_host_N> }
        ]
      }' \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.postgresql.v1.ClusterService.AddHosts

Where host_specs is an array of new hosts. One array element contains settings for a single host and has the following structure:

zone_id: Availability zone.
subnet_id: Subnet ID.
assign_public_ip: Internet access to the host via a public IP address.
replication_source: Replication source for the host to manually manage replication threads. In the parameter, specify the FQDN of the host to be used as a replication source.
priority: Host priority among all hosts.
config_spec.postgresql_config_<PostgreSQL_version>: PostgreSQL settings. Use a separate line for each setting; separate them by commas.

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

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

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

Warning

If you cannot connect to the host you added, check that the cluster security group is configured correctly for the host's subnet.

Changing a host

For each host in a Managed Service for PostgreSQL cluster, you can specify the replication source and manage host public access.

Management console

CLI

Terraform

REST API

gRPC API

To change the parameters of the cluster host:

Go to the folder page and select Managed Service for PostgreSQL.
Click the cluster name and open the Hosts tab.
Click in the host row and select Edit.
Set new settings for the host:
1. Select the replication source for the host to manually manage replication threads.
2. Enable Public access if the host must be accessible from outside Yandex Cloud.
Click Save.

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 parameters of a host in a cluster, run the command below:

yc managed-postgresql host update <host_name>
  --cluster-name <cluster_name>
  --replication-source <source_host_name>
  --assign-public-ip=<public_access_to_host>

Where:

cluster-name: Cluster name.
replication-source: Source host name.
assign-public-ip: Public access to the host, true or false.

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

To manually manage replication threads in the cluster, change the host's replication source in the --replication-source parameter.

To change the parameters of the cluster host:

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.

In the Managed Service for PostgreSQL cluster description, change the attributes of the host block corresponding to the host you are updating.

resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
  ...
  host {
    replication_source_name = "<replication_source>"
    assign_public_ip        = <public_access_to_host>
  }
}

Where:

replication_source_name: Replication source, i.e., the name attribute of the corresponding host section.
assign_public_ip: Public access to the host, true or false.

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

Get an IAM token for API authentication and place it in the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```
Use the Cluster.updateHosts 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 POST \
  --header "Authorization: Bearer $IAM_TOKEN" \
  --header "Content-Type: application/json" \
  --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/hosts:batchUpdate' \
  --data '{
            "updateHostSpecs": [
              {
                "updateMask": "assignPublicIp,replicationSource,priority,configSpec.postgresqlConfig_<PostgreSQL_version>",
                "hostName": "<host_FQDN>",
                "assignPublicIp": <deletion_protection:_true_or_false>,
                "replicationSource": "<host_FQDN>",
                "priority": "<host_priority>",
                "configSpec": {
                  "postgresqlConfig_<PostgreSQL_version>": {
                    <PostgreSQL_settings>
                  }
                }
              },
              { <similar_settings_for_host_2_being_changed> },
              { ... },
              { <similar_settings_for_host_N_being_changed> }
            ]
          }'
```
Where updateHostSpecs is an array of hosts being changed. One array element contains settings for a single host and has the following structure:
- updateMask: List of parameters to update as a single string, separated by commas.
- hostName: FQDN of a host being changed.
- assignPublicIp: Internet access to the host via a public IP address, true or false.
- replicationSource: Replication source for the host to manually manage replication threads. In the parameter, specify the FQDN of the host to be used as a replication source.
- priority: Host priority among all hosts.
- configSpec.postgresqlConfig_<PostgreSQL_version>: PostgreSQL settings. Use a separate line for each setting; separate them by commas.
  
  See the method description for the list of PostgreSQL versions available for the parameter. See PostgreSQL settings for a description and possible values for each setting.
You can get the cluster ID with a list of clusters in the folder.
View the server response to make sure the request was successful.

Get an IAM token for API authentication and place it in the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```
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.

Use the ClusterService/UpdateHosts 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_host_specs": [
          {
            "update_mask": {
                "paths": [
                  "assign_public_ip",
                  "replication_source",
                  "priority",
                  "config_spec.postgresql_config_16"
                ]
            },
            "host_name": "<host_FQDN>",
            "assign_public_ip": <deletion_protection:_true_or_false>,
            "replication_source": "<host_FQDN>",
            "priority": "<host_priority>",
            "config_spec": {
              "postgresql_config_<PostgreSQL_version>": {
                <PostgreSQL_settings>
              }
            }
          }
        ]
      }' \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.postgresql.v1.ClusterService.UpdateHosts

Where update_host_specs is an array of hosts being changed. One array element contains settings for a single host and has the following structure:

update_mask: List of parameters to update as an array of paths[] strings.
hostName: FQDN of a host being changed.
assignPublicIp: Internet access to the host via a public IP address, true or false.
replicationSource: Replication source for the host to manually manage replication threads. In the parameter, specify the FQDN of the host to be used as a replication source.
priority: Host priority among all hosts.
configSpec.postgresqlConfig_<PostgreSQL_version>: PostgreSQL settings. Use a separate line for each setting; separate them by commas.

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

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

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

Warning

If you cannot connect to the host after you changed it, check that the cluster security group is configured correctly for the host's subnet.

Removing a host

You can remove a host from a PostgreSQL cluster if it is not the only host in it. To replace a single host, first create a new host and then remove the old one.

If the host is the master when deleted, Managed Service for PostgreSQL automatically assigns the replica of the next highest priority as the master.

Management console

CLI

Terraform

REST API

gRPC API

To remove a host from a cluster:

Go to the folder page and select Managed Service for PostgreSQL.
Click the cluster name and open the Hosts tab.
Click in the required host row, select Delete, and confirm the deletion.

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 remove a host from the cluster, run:

yc managed-postgresql host delete <host_name>
  --cluster-name <cluster_name>

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

To remove a host from a cluster:

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.
Delete the corresponding host block from the Managed Service for PostgreSQL cluster description.
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.
Type yes and press Enter.
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 } }

Get an IAM token for API authentication and place it in the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```

Use the Cluster.deleteHosts 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>/hosts:batchDelete' \
  --data '{
            "hostNames": [
              "<host_FQDN>"
            ]
          }'

Where hostNames is an array of strings, each containing the FQDN of the host being deleted.

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

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

Get an IAM token for API authentication and place it in the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```
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.

Use the ClusterService/DeleteHosts 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_names": [
          "<host_FQDN>"
        ]
      }' \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.postgresql.v1.ClusterService.DeleteHosts

Where host_names is an array of strings, each containing the FQDN of the host being deleted.

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

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

Managing PostgreSQL cluster hosts

Getting a list of cluster hostsGetting a list of cluster hosts

Creating a hostCreating a host

Changing a hostChanging a host

Removing a hostRemoving a host

Was the article helpful?

Getting a list of cluster hosts

Creating a host

Changing a host

Removing a host