Cloud Databases: create your first managed database
Cloud Databases is a service that allows you to work with managed databases.
Start working with the Cloud Databases service in any convenient way:
- Follow the instructions to create your first managed PostgreSQL database, connect to it, and run test queries.
- Watch the videos where managed MySQL, PostgreSQL, and ClickHouse databases in the Single configuration are created.
- Take the free training course. The theoretical part of the course explains what a database and a database management system are, and what types and configurations of databases exist. In the practical part of the course, you will create a PostgreSQL cluster and a MySQL database, then connect the created MySQL database to an application installed on a virtual machine.
After completing all the steps in this instruction, you will:
- Create a PostgreSQL database instance in the Single configuration.
- Get an external IP address of the created database instance.
- Install the TimescaleDB extension in the database instance.
- Connect to the database.
- Create tables in the database.
- Fill the tables with data.
- Run test queries against the database.
- (Optionally) Explore the monitoring data collected during the database instance operation.
As an example of test data, an automatically generated dataset will be used, representing information from Internet of Things (IoT) sensors: temperature and CPU load. For more details about the test dataset, see the Timescale documentation. The procedure for generating similar datasets is covered in detail in the Timescale blog.
-
Go to your VK Cloud management console.
-
Select the project where the DB instance will be hosted.
-
Go to the Databases → Database instances section.
-
If there are no DB instances in the selected project, click the Create database button.
Otherwise, click the Add button.
-
At the Configuration step, select:
- PostgreSQL database type and the most recent DBMS version.
- Single configuration.
-
Click the Next step button.
-
At the Instance creation step, set:
- Database instance name: for example,
vk-cloud-dbaas-quickstart. - Virtual machine type:
STD3-2-8. - Availability zone:
Moscow (ME1). - Disk type:
SSD. - Disk size, GB:
10. - Enable disk autoscaling: make sure this option is not selected.
- Network:
Create a new network. - Subnet address:
10.0.1.0/24. - Assign external IP: make sure this option is selected.
- Firewall settings: make sure only the
defaultoption is selected. - Create replica: make sure this option is not selected.
- Backup:
Disabled.
- Database instance name: for example,
-
Click the Next step button.
-
At the Initialization step, set:
-
Creation type:
New database. -
Database name to create:
tsdb1. -
User name:
tsuser1. -
User password: set a password or generate one.
-
-
Click the Create database button.
Wait for the DB instance creation to complete; this process can take a long time.
-
Go to your VK Cloud management console.
-
Select the project where the DB instance is located.
-
Go to the Databases → Database instances section.
-
Click the DB instance name. The information page will open.
-
Go to the Information tab.
The required address will be specified in the External IP address parameter.
-
Go to your VK Cloud management console.
-
Select the project where the DB instance is located.
-
Go to the Databases → Database instances section.
-
Click the DB instance name. The information page will open.
-
Go to the Extensions tab.
-
Click the Add button.
-
In the Available extensions drop-down list, select
Open-source extension for storing time series data (timescaledb). -
Click the Add button.
Wait for the extension installation to complete: it should change from the
Creatingstate to theActivestate.
-
Install the
psqlutility:Linux (APT)Linux (RPM)macOS (Homebrew)Windows-
Connect the PostgreSQL repository:
sudo apt install curl ca-certificates gnupgcurl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg > /dev/nullsudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'sudo apt update -
Install the
psqlutility:sudo apt install -y postgresql-client
-
-
Connect to the
tsdb1database using thepsqlutility:-
Run the command:
psql -h <DB instance external IP address> -d tsdb1 -U tsuser1 -
Enter the password of the
tsuser1user set when creating the DB instance.
On a successful connection, the following prompt should appear:
tsdb1=> -
-
Make sure the TimescaleDB extension is enabled:
CREATE EXTENSION IF NOT EXISTS timescaledb;Wait for the
tsdb1=>prompt to appear. -
Create the
sensorstable:CREATE TABLE sensors(id SERIAL PRIMARY KEY,type VARCHAR(50),location VARCHAR(50)); -
Create the
sensor_datatable:CREATE TABLE sensor_data (time TIMESTAMPTZ NOT NULL,sensor_id INTEGER,temperature DOUBLE PRECISION,cpu DOUBLE PRECISION,FOREIGN KEY (sensor_id) REFERENCES sensors (id)); -
Make sure the tables were created successfully:
SELECT tablenameFROM pg_catalog.pg_tablesWHERE tablename LIKE 'sensor%'; -
Convert the
sensor_dataPostgreSQL table into a TimescaleDB hypertable:SELECT create_hypertable('sensor_data', 'time'); -
Make sure the
sensor_datahypertable was created successfully:SELECT hypertable_nameFROM timescaledb_information.hypertables;
-
Populate the
sensorstable with data:INSERT INTO sensors (type, location) VALUES('a','floor'),('a', 'ceiling'),('b','floor'),('b', 'ceiling'); -
Make sure the data was successfully inserted into the table:
SELECT * FROM sensors; -
Populate the
sensor_datatable with randomly generated data:INSERT INTO sensor_data (time, sensor_id, cpu, temperature)SELECTtime,sensor_id,random() AS cpu,random()*100 AS temperatureFROM generate_series(now() - interval '24 hour', now(), interval '5 minute') AS g1(time), generate_series(1,4,1) AS g2(sensor_id); -
Make sure the data was successfully inserted into the table by displaying the first several rows of the table:
SELECT * FROM sensor_data ORDER BY time LIMIT 8;
-
Display the average temperature and CPU load values for thirty-minute intervals:
SELECTtime_bucket('30 minutes', time) AS period,AVG(temperature) AS avg_temp,AVG(cpu) AS avg_cpuFROM sensor_dataGROUP BY periodORDER BY period; -
Display the average temperature and CPU load values for thirty-minute intervals, as well as the last temperature value recorded for the interval:
SELECTtime_bucket('30 minutes', time) AS period,AVG(temperature) AS avg_temp,last(temperature, time) AS last_temp,AVG(cpu) AS avg_cpuFROM sensor_dataGROUP BY periodORDER BY period;
Output of query results similar to those shown indicates that PostgreSQL and the TimescaleDB extension are working correctly.
Monitoring is available for PostgreSQL. Explore the collected monitoring data:
- Go to your VK Cloud management console.
- Select the project where the DB instance is located.
- Go to the Databases → Database instances section.
- Click the DB instance name. The information page will open.
- Go to the Monitoring tab.
- Select the required time interval and view the collected data.
The DB instance is charged and consumes computing resources. If you no longer need it:
- Delete the DB instance.
- If required, delete the Floating IP address assigned to the DB instance. Floating IP addresses present in the project are charged.
This video shows how to create a managed MySQL database in the Single configuration, configure backup and monitoring, and then connect to the created database.
MySQL is used primarily for web applications and small projects.
Take the free training course.
The theoretical part of the course explains what a database and a database management system are, and what types and configurations of databases exist. In the practical part of the course, you will create a PostgreSQL cluster and a MySQL database, then connect the created MySQL database to an application installed on a virtual machine.