Managed Databases offer many benefits over manual maintenance such as ease of deployment, configuration and backups. However, while the default configurations are optimised according to the service plans, some aspects might not be ideal in specific use cases. Therefore, we’ve exposed certain database properties for you to customise via the UpCloud API.<\/p>\n\n\n\n

Database configuration properties allow you to customise your Managed Database for finer details such as logging parameters, certain timeouts or buffer sizes. Follow along with this tutorial to see which properties you can customise and how to do so.<\/p>\n\n\n\n

Getting started with UpCloud API<\/h2>\n\n\n\n

UpCloud API<\/a> offers a developer-centric approach to all the same services and features that are available via UpCloud Control Panel. To use the API, you will need to enable access using your UpCloud account or sub-account credentials.<\/p>\n\n\n\n

If you are new to using the API, check out our introductory tutorial<\/a> to find out how to get started. When you are set to run requests, continue ahead with the API requests regarding Managed Database properties below.<\/p>\n\n\n\n

Listing configuration properties<\/h2>\n\n\n\n

The available configuration properties depend on the database type you are using. Both MySQL and PostgreSQL have their own customisable properties which can be listed by querying the service types.<\/p>\n\n\n\n

Use the request examples in the following sections to list the available service plans and properties for your database type.<\/p>\n\n\n\n

MySQL properties<\/h3>\n\n\n\n

For MySQL databases, run the API request underneath.<\/p>\n\n\n\n

GET \/1.3\/database\/service-types\/mysql<\/pre>\n\n\n\n
You will then get a response with an output like the example below.<\/p>\n\n\n\n
HTTP\/1.0 200 OK<\/pre>\n\n\n\n
{\n    \"name\": \"mysql\",\n    \"description\": \"MySQL - Relational Database Management System\",\n    \"latest_available_version\": \"8.0.26\",\n    \"service_plans\": [\n        ... service plan options ...\n    ],\n    \"properties\": {\n        \"admin_password\": {\n            \"createOnly\": true,\n            \"example\": \"z66o9QXqKM\",\n            \"maxLength\": 256,\n            \"minLength\": 8,\n            \"pattern\": \"^[a-zA-Z0-9-_]+$\",\n            \"title\": \"Custom password for admin user. Defaults to random string. This must be set only when a new service is being created.\",\n            \"type\": [\"string\",\"null\"],\n            \"user_error\": \"Must consist of alphanumeric characters, underscores or dashes\"\n        },\n        \"admin_username\": {\n            \"createOnly\": true,\n            \"example\": \"avnadmin\",\n            \"maxLength\": 64,\n            \"pattern\": \"^[_A-Za-z0-9][-._A-Za-z0-9]{0,63}$\",\n            \"title\": \"Custom username for admin user. This must be set only when a new service is being created.\",\n            \"type\": [\"string\",\"null\"],\n            \"user_error\": \"Must consist of alphanumeric characters, dots, underscores or dashes, may not start with dash or dot, max 64 characters\"\n        },\n        \"automatic_utility_network_ip_filter\": {\n            \"default\": true,\n            \"title\": \"Automatic utility network IP Filter\",\n            \"type\": \"boolean\",\n            \"description\": \"Automatically allow connections from servers in the utility network within the same zone\"\n        },\n        \"backup_hour\": {\n            \"example\": 3,\n            \"title\": \"The hour of day (in UTC) when backup for the service is started. New backup is only started if previous backup has already completed.\",\n            \"type\": [\"integer\",\"null\"],\n            \"minimum\": 0,\n            \"maximum\": 23\n        },\n      ... more configurations properties ...\n    }\n  }\n}<\/pre>\n\n\n\n
The above output has been truncated for brevity, the actual list of customisable properties can be found in the Managed Database documentation<\/a>.<\/p>\n\n\n\n
PostgreSQL properties<\/h3>\n\n\n\n
PostgreSQL-specific properties can be listed with the following request.<\/p>\n\n\n\n
GET \/1.3\/database\/service-types\/pg<\/pre>\n\n\n\n
You will then get a response with an output like the example below.<\/p>\n\n\n\n
HTTP\/1.0 200 OK<\/pre>\n\n\n\n
{\n    \"name\": \"pg\",\n    \"description\": \"PostgreSQL - Object-Relational Database Management System\",\n    \"latest_available_version\": \"14.2\",\n    \"service_plans\": [\n        ... service plan options ...\n    \"properties\": {\n        \"admin_password\": {\n            \"createOnly\": true,\n            \"example\": \"z66o9QXqKM\",\n            \"maxLength\": 256,\n            \"minLength\": 8,\n            \"pattern\": \"^[a-zA-Z0-9-_]+$\",\n            \"title\": \"Custom password for admin user. Defaults to random string. This must be set only when a new service is being created.\",\n            \"type\": [\n                \"string\",\n                \"null\"\n            ],\n            \"user_error\": \"Must consist of alphanumeric characters, underscores or dashes\"\n        },\n        \"admin_username\": {\n            \"createOnly\": true,\n            \"example\": \"avnadmin\",\n            \"maxLength\": 64,\n            \"pattern\": \"^[_A-Za-z0-9][-._A-Za-z0-9]{0,63}$\",\n            \"title\": \"Custom username for admin user. This must be set only when a new service is being created.\",\n            \"type\": [\n                \"string\",\n                \"null\"\n            ],\n            \"user_error\": \"Must consist of alphanumeric characters, dots, underscores or dashes, may not start with dash or dot, max 64 characters\"\n        },\n        \"automatic_utility_network_ip_filter\": {\n            \"default\": true,\n            \"title\": \"Automatic utility network IP Filter\",\n            \"type\": \"boolean\",\n            \"description\": \"Automatically allow connections from servers in the utility network within the same zone\"\n        },\n        \"autovacuum_analyze_scale_factor\": {\n            \"title\": \"autovacuum_analyze_scale_factor\",\n            \"type\": \"number\",\n            \"description\": \"Specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE. The default is 0.2 (20% of table size)\",\n            \"minimum\": 0,\n            \"maximum\": 1\n        },\n      ... more configurations properties ...\n    }\n  }\n}<\/pre>\n\n\n\n
The example output has been truncated for brevity. Run the request or refer to the Managed Database documentation<\/a> for the full list of customisable properties.<\/p>\n\n\n\n
Setting custom database properties<\/h2>\n\n\n\n
Configuration properties can be set either during Managed Database creation or modified afterwards.<\/p>\n\n\n\n
For example, you can set the properties you want when creating a new Managed Database. Simply include the properties section with the nested parameters you want.<\/p>\n\n\n\n
POST \/1.3\/database<\/pre>\n\n\n\n
{\n  \"hostname_prefix\": \"doc-api-unique-prefix\",\n  \"plan\": \"1x1xCPU-2GB-25GB\",\n  \"title\": \"my-managed-database\",\n  \"type\": \"mysql\",\n  \"zone\": \"de-fra1\",\n  \"maintenance\": {\n    \"dow\": \"sunday\",\n    \"time\": \"05:00:00\"\n  },\n  \"properties\": {\n    \"automatic_utility_network_ip_filter\": true,\n    \"backup_hour\": 4,\n    \"backup_minute\": 30\n  }\n}<\/pre>\n\n\n\n
Alternatively, if you already have a Managed Database instance up and running, you can modify the properties using the PATCH request.<\/p>\n\n\n\n
Use the following request and body as an example. Note that you will need to identify the correct Managed Database using its UUID.<\/p>\n\n\n\n
PATCH \/1.3\/database\/{uuid}<\/pre>\n\n\n\n
Then define the properties you want to change in the request body.<\/p>\n\n\n\n
{\n  \"properties\": {\n    \"automatic_utility_network_ip_filter\": true,\n    \"backup_hour\": 4,\n    \"backup_minute\": 30\n  }\n}<\/pre>\n\n\n\n
After a successful request to change the database properties, you will get a confirmation of the new configuration in the response.<\/p>\n\n\n\n
Conclusions<\/h2>\n\n\n\n
Managed Databases offer an easy-to-use, expert-level configuration for many database needs. It provides a turn-key solution for databases including replication and point-in-time backups which normally require a significant amount of configuration. With the bulk of the work already done, you are free to focus on the finer details such as the configuration properties.<\/p>\n\n\n\n
However, there are some restrictions on what properties you can customise to ensure service stability. As such, not all properties can be set through the API at this time. If you come across a database configuration property that is not currently listed and you would like to change, get in touch<\/a>! We are always happy to work with our customers to improve the usability of Managed Databases.<\/p>\n","protected":false},"featured_media":21438,"comment_status":"open","ping_status":"closed","template":"","community-category":[124,127],"class_list":["post-24981","tutorial","type-tutorial","status-publish","has-post-thumbnail","hentry","community-category-databases","community-category-managed-services"],"acf":[],"_links":{"self":[{"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/tutorial\/24981","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/tutorial"}],"about":[{"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/types\/tutorial"}],"replies":[{"embeddable":true,"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/comments?post=24981"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/media\/21438"}],"wp:attachment":[{"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/media?parent=24981"}],"wp:term":[{"taxonomy":"community-category","embeddable":true,"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/community-category?post=24981"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}