Quick start

Quick start will help you get started with the service and become familiar with its features. PostgreSQL will be used as example.

After going through all the steps of the quick start, you will:

1. Create a single host PostgreSQL DB instance.

2. Install the TimescaleDB extension to the instance.

3. Learn how to connect to the instance for both viewing logs and executing SQL queries.

4. Create test data and quieries for TimescaleDB to make sure that the extension operates correctly.

   An automatically generated test dataset will be used in this quick start. It comprises the information from the IoT sensors: temperature and CPU utilization. Read more about the dataset in Timescale documentation.

   The procedure for generating such datasets is discussed in details in Timescale blog.

5. Familiarize yourself with the monitoing data collected during the operation of the DB instance.

1. Make sure that the psql utility is installed. To do this, view its version:

   Copy
   psql --version

   If the utility is installed, its version will be displayed:

   Copy
   psql (PostgreSQL) 14.7

   Otherwise, there will be shown a message stating that the psql command is not found.

2. If the psql utility is not installed, then install it:

   Linux (APT)

   Linux (RPM)

   macOS (Homebrew)

   Windows

   1. Connect the PostgreSQL repository:

      Copy
      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:

      Copy
      sudo apt install -y postgresql-client

1. Go to VK Cloud personal account.

2. Select project, where the instance will be placed.

3. Go to Databases → Database instances.

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

   Otherwise, click the Add button.

5. On the “Configuration” step: select:

   • The PostgreSQL database type and the most current version.
   • The Single configuration.

6. Click the Next step button.

7. On the “Create instance” step set:

   • Database instance name: for example, vk-cloud-dbaas-quickstart.

   • Type of virtual machine: STD3-2-8.

   • Availability zone: Moscow (GZ1).

   • Disk Type: SSD.

   • Disk size, GB: 10.

   • Enable volume autoscaling: make sure that this option is disabled.

   • Network: Create new network.

   • Subnet address: 10.0.1.0/24.

   • Assign external IP: make sure that this option is enabled.

   • Настройки Firewall: select ssh from the drop-down list.

     The resulting list of the secrity groups should look like: default, ssh.

   • Create replica: make sure that this option is disabled.

   • SSH access key: Create a new key.

   • Backup: Disabled.

   • Enable monitoring: make sure that this option is enabled.

8. Click the Next step button.

9. On the “Initialization” step set:

   • Creation type: New database.

   • Name of database for creation: tsdb1.

   • Username: tsuser1.

   • User password: provide a password or generate it.

10. Click the Create database button.

    Wait until the creation of the DB instance is completed, this process can take a long time.

1. Go to VK Cloud personal account.

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

3. Go to Databases → Database instances.

4. Click on the name of the DB instance. A page with information will open.

5. Navigate to the Information tab.

   The necessary address will be listed in the External IP address parameter.

1. Connect to DB instace via SSH. Use private SSH key, obtained during the instance creation:

   Linux/macOS

   Windows

   Copy
   chmod 0600 <path/to/the/key.pem>ssh -i <path/to/the/key.pem> admin@<DB instance external IP address>

2. View DB instance logs:

   Copy
   journalctl -u postgresql

   Example output fragment

   It is possible to conclude from this output that PostgreSQL is up and running and ready to accept incoming connections.

1. Go to VK Cloud personal account.

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

3. Go to Databases → Database instances.

4. Click on the name of the DB instance. A page with information will open.

5. Navigate to the Extensions tab.

6. Click the Add button.

7. Select Open-source database extension for storing time-series data (timescaledb) from the Available extensions drop-down list.

8. Click the Add button.

   Wait until the installation of the extension is completed: its status should change from Creating to Active.

Connect to the tsdb1 database via the psql utility:

1. Execute the command:

   Copy
   psql -h <DB instance external IP address> -d tsdb1 -U tsuser1

2. Enter the tsuser1 user's password, specified during the instance creation.

If the connection is successful, the following prompt will be displayed:

Copy
tsdb1=>

1. Activate the TimescaleDB extension:

   Copy
   CREATE EXTENSION timescaledb;

   Wait for the tsdb1=> prompt to appear.

2. Create the sensors table:

   Copy
   CREATE TABLE sensors(  id SERIAL PRIMARY KEY,  type VARCHAR(50),  location VARCHAR(50));

3. Create the sensor_data table:

   Copy
   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 that the tables were created successfully:

   Copy
   SELECT tablenameFROM pg_catalog.pg_tablesWHERE tablename LIKE 'sensor%';

   Query result

5. Convert the PostgreSQL sensor_data table into the TimescaleDB hypertable:

   Copy
   SELECT create_hypertable('sensor_data', 'time');

6. Make sure that the sensor_data hypertable was created successfully:

   Copy
   SELECT hypertable_nameFROM timescaledb_information.hypertables;

   Query result

1. Fill in the sensors table with data:

   Copy
   INSERT INTO sensors (type, location) VALUES('a','floor'),('a', 'ceiling'),('b','floor'),('b', 'ceiling');

2. Make sure that the data was successfully inserted into the table:

   Copy
   SELECT * FROM sensors;

   Query result

3. Fill in the sensor_data table with randomly generated data:

   Copy
   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 that the data was successfully inserted into the table by selecting a few rows from the table:

   Copy
   SELECT * FROM sensor_data ORDER BY time LIMIT 8;

   Example query result

1. Output the average temperature and CPU utilization values over thirty-minute intervals:

   Copy
   SELECT  time_bucket('30 minutes', time) AS period,  AVG(temperature) AS avg_temp,  AVG(cpu) AS avg_cpuFROM sensor_dataGROUP BY periodORDER BY period;

   Example query result fragment

2. Output the average temperature and CPU utilization values over thirty-minute intervals, along with the last temperature value recorded during the interval:

   Copy
   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;

   Example query result fragment

Output of query results similar to the above ones indicates that PostgreSQL and TimescaleDB extension are working correctly.

The monitoring feature was enabled during the instance creation. Familiarize yourself with gathered monitoring data:

1. Go to VK Cloud personal account.
2. Select the project where the DB instance is located.
3. Go to Databases → Database instances.
4. Click on the name of the DB instance. A page with information will open.
5. Navigate to the Monitoring tab.
6. Select the desired time interval and look at the collected data.

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

1. Delete DB instance.
2. If necessary, delete floating IP address, that was assigned to the DB instance. Floating IP addresses, that exist in the project, are charged.