VK Cloud logo

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.

Create a managed PostgreSQL database

After completing all the steps in this instruction, you will:

  1. Create a PostgreSQL database instance in the Single configuration.
  2. Get an external IP address of the created database instance.
  3. Install the TimescaleDB extension in the database instance.
  4. Connect to the database.
  5. Create tables in the database.
  6. Fill the tables with data.
  7. Run test queries against the database.
  8. (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.

1. Create a PostgreSQL DB instance

  1. Go to your VK Cloud management console.

  2. Select the project where the DB instance will be hosted.

  3. Go to the Databases → Database instances section.

  4. If there are no DB instances in the selected project, click the Create database button.

    Otherwise, click the Add button.

  5. At the Configuration step, select:

    • PostgreSQL database type and the most recent DBMS version.
    • Single configuration.
  6. Click the Next step button.

  7. 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 default option is selected.
    • Create replica: make sure this option is not selected.
    • Backup: Disabled.
  8. Click the Next step button.

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

  10. Click the Create database button.

    Wait for the DB instance creation to complete; this process can take a long time.

2. Get the external IP address of the DB instance

  1. Go to your VK Cloud management console.

  2. Select the project where the DB instance is located.

  3. Go to the Databases → Database instances section.

  4. Click the DB instance name. The information page will open.

  5. Go to the Information tab.

    The required address will be specified in the External IP address parameter.

3. Install the TimescaleDB extension

  1. Go to your VK Cloud management console.

  2. Select the project where the DB instance is located.

  3. Go to the Databases → Database instances section.

  4. Click the DB instance name. The information page will open.

  5. Go to the Extensions tab.

  6. Click the Add button.

  7. In the Available extensions drop-down list, select Open-source extension for storing time series data (timescaledb).

  8. Click the Add button.

    Wait for the extension installation to complete: it should change from the Creating state to the Active state.

4. Connect to the database

  1. Install the psql utility:

    1. 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
    2. Install the psql utility:

      sudo apt install -y postgresql-client
  2. Connect to the tsdb1 database using the psql utility:

    1. Run the command:

      psql -h <DB instance external IP address> -d tsdb1 -U tsuser1
    2. Enter the password of the tsuser1 user set when creating the DB instance.

    On a successful connection, the following prompt should appear:

    tsdb1=>

5. Create the required tables

  1. Make sure the TimescaleDB extension is enabled:

    CREATE EXTENSION IF NOT EXISTS timescaledb;

    Wait for the tsdb1=> prompt to appear.

  2. Create the sensors table:

    CREATE TABLE sensors(  id SERIAL PRIMARY KEY,  type VARCHAR(50),  location VARCHAR(50));
  3. Create the sensor_data table:

    CREATE TABLE sensor_data (  time TIMESTAMPTZ NOT NULL,  sensor_id INTEGER,  temperature DOUBLE PRECISION,  cpu DOUBLE PRECISION,  FOREIGN KEY (sensor_id) REFERENCES sensors (id));
  4. Make sure the tables were created successfully:

    SELECT tablenameFROM pg_catalog.pg_tablesWHERE tablename LIKE 'sensor%';
  5. Convert the sensor_data PostgreSQL table into a TimescaleDB hypertable:

    SELECT create_hypertable('sensor_data', 'time');
  6. Make sure the sensor_data hypertable was created successfully:

    SELECT hypertable_nameFROM timescaledb_information.hypertables;

6. Populate the tables with data

  1. Populate the sensors table with data:

    INSERT INTO sensors (type, location) VALUES('a','floor'),('a', 'ceiling'),('b','floor'),('b', 'ceiling');
  2. Make sure the data was successfully inserted into the table:

    SELECT * FROM sensors;
  3. Populate the sensor_data table with randomly generated data:

    INSERT INTO sensor_data (time, sensor_id, cpu, temperature)SELECT  time,  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);
  4. 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;

7. Run test queries

  1. Display the average temperature and CPU load values for thirty-minute intervals:

    SELECT  time_bucket('30 minutes', time) AS period,  AVG(temperature) AS avg_temp,  AVG(cpu) AS avg_cpuFROM sensor_dataGROUP BY periodORDER BY period;
  2. Display the average temperature and CPU load values for thirty-minute intervals, as well as the last temperature value recorded for the interval:

    SELECT  time_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.

8. (Optional) Explore the DB instance monitoring data

Monitoring is available for PostgreSQL. Explore the collected monitoring data:

  1. Go to your VK Cloud management console.
  2. Select the project where the DB instance is located.
  3. Go to the Databases → Database instances section.
  4. Click the DB instance name. The information page will open.
  5. Go to the Monitoring tab.
  6. Select the required time interval and view the collected data.

Remove unused resources

The DB instance is charged and consumes computing resources. If you no longer need it:

  1. Delete the DB instance.
  2. If required, delete the Floating IP address assigned to the DB instance. Floating IP addresses present in the project are charged.

Video instructions

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.

Training course

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.

FAQ

Was this article helpful?