Getting Started with Glesys Database as a Service (DBaaS)

Introduction

Glesys Database as a Service is a fully managed database solution. We handle your databases and the underlying infrastructure, allowing your business to operate without interruptions. Our services include maintenance, automated backups, and patches, ensuring that your applications have the reliability and security they require.

We currently offer the following database engines: PostgreSQL v16 and MySQL v8.0.

In this guide, we will learn how to create a database, connect to it using DBeaver as an example, monitor it with InfluxDB as an example, and finally, delete a database.

Prerequisites

• A Glesys Cloud account with access to a verified organization and a valid payment method.
• The applications DBeaver and InfluxDB (optional).

1. Create a database

You find the database service under Storage → Databases in the left-hand menu.

To create your first database, make sure you are in the correct project. Then, click on Databases in the Storage section, and finally, select Create.

Next, you need to decide which type of database to create. You have the option to choose between PostgreSQL v16 and MySQL v8.0. For this example, we will select PostgreSQL v16.

Further down the page, we need to select a plan for our database. The available plans vary in size, which affects the number of CPU cores, memory, and storage. You can choose the storage size independently from the CPU and memory configuration using the dropdown menu below each plan.

As we select different plans and storage sizes, the price is automatically updated in the lower-right corner.

In this example, we have chosen a plan with 1 CPU core, 4 GiB of memory, and 50 GiB of storage.

Finally, you need to select the data center for your database location and give your database a name. In this example, we have chosen the Falkenberg data center and named the database my-test-db.

Once you have made all your selections, click Create in the bottom-right corner.

You will be taken to an details view of your new database, which will take a few minutes to create. During this time, its status will display as Creating... in the upper-right corner. This status will change to Online once the database is ready for use.

Next to the database status, you will see its type and the data center where it is located. For example, it may show PostgreSQL v16 located in Falkenberg.

From time to time, Glesys performs maintenance tasks on the managed PostgreSQL database. In this instance, it is scheduled for Wednesdays at 07:00 each week. These tasks typically involve updating the database's underlying infrastructure, including patching the operating system, updating necessary libraries, or upgrading PostgreSQL itself. Glesys automatically manages these maintenance tasks. During the maintenance, the database may experience a brief period of downtime, lasting up to 10 minutes.

On the overview page, you will find all the necessary details to connect to your database, including the username, password, default database name, and connection string. To view the password, click the eye icon. To copy the password to your clipboard, click the copy icon. The same applies for the connection string.

However, you cannot connect to your database just yet because, by default, all incoming connections are blocked for security reasons.

In the Firewall section of the overview, you need to add the IP addresses or IP ranges that will be permitted to connect to the database. Click Manage access to enter your IP addresses.

In the dialog box that appears, enter your IP addresses or IP ranges, separating them with commas. For example, you can allow the entire IP range 203.0.113.0/24 along with a single IP address 128.66.1.43. After entering your IP addresses, click Update to save your changes.

At the very bottom of the overview page, you will find the address for the monitoring page. Please note that this page is also affected by the firewall, and therefore, it can only be accessed from the allowed IP addresses.

2. Connect to a database

The connection string provided in the overview can typically be used directly in most programming languages and applications. However, some tools may require separate values for the hostname, port, username, and password. These values can be extracted from the connection string.

For instance, given the following connection string:

postgresql://dbadmin:xxxyyy@db-4f959.database-v1.glesys.com:5432/defaultdb?ssl-mode=required

• The database type is PostgreSQL.
• The username is dbadmin.
• The password is xxxyyy.
• The hostname is db-4f959.database-v1.glesys.com (after the @ symbol).
• The port is 5432 (the default port for PostgreSQL).
• The database name is defaultdb.
• The connection requires SSL encryption, as indicated by the ssl-mode=required parameter.

Example using DBeaver

DBeaver is a SQL client software application and a database administration tool.

To create a new database connection in the DBeaver application, start by launching the application. Then, click on Database → New Database Connection in the menu, or use the keyboard shortcut Shift + Ctrl + N. This will open the dialog for setting up a new database connection.

Next, choose the type of database you want to connect to—either PostgreSQL or MySQL. For this example, we will connect to a PostgreSQL database. Click on PostgreSQL and then click Next to proceed.

In the following dialog box, we input all the required information for our database connection, including hostname, port, database name, username, and password.

After filling in all the required information on the Main tab, click on the SSL tab. Here, enable Use SSL and select require under SSL mode. Finally, click Finish.

Now, click the down-arrow icon next to defaultdb under Connections. DBeaver should connect to the database. We can then expand the tree by clicking the down-arrows until we see the defaultdb database and its contents.

We can begin working with our database in two main ways: by using the SQL console (navigate to SQL Editor → Open SQL Console) or through the graphical interface to create and modify tables.

For instance, by expanding Schemas, followed by public, we can right-click on Tables to create new tables or view existing ones.

3. Monitor a database

As a customer, it’s important to keep an eye on your databases to see how they perform. By doing this, you can proactively identify database instances that are experiencing high CPU or memory usage. Knowing this will allow you to size the instances correctly in the future based on your workload needs.

The metrics available at the URL in the Monitoring section of the overview page are formatted in Prometheus metric exposition. This format is compatible with tools like InfluxDB scrapers and Prometheus.

Here is a brief snippet of the metric data:

# HELP disk_free Telegraf collected metric
# TYPE disk_free gauge
disk_free{device="sda2",fstype="ext4",host="db-4f959",label="",mode="rw",path="/"} 2.5386483712e+10
disk_free{device="tmpfs",fstype="tmpfs",host="db-4f959",label="",mode="rw",path="/dev/shm"} 2.044461056e+09
disk_free{device="tmpfs",fstype="tmpfs",host="db-4f959",label="",mode="rw",path="/run"} 4.078592e+08
disk_free{device="tmpfs",fstype="tmpfs",host="db-4f959",label="",mode="rw",path="/run/lock"} 5.24288e+06
disk_free{device="udev",fstype="devtmpfs",host="db-4f959",label="",mode="rw",path="/dev"} 2.006528e+09

Example using InfluxDB

InfluxDB is a comprehensive, open source time series toolkit for managing and analyzing time series data.

In this example, we will use the InfluxDB scraper to collect data from a specified target and then write the scraped data to an InfluxDB bucket. Finally, we will utilize the built-in Data Explorer tool to visualize metrics such as total disk space and used disk space.

To begin ingesting metrics into InfluxDB, we first create a new bucket. In InfluxDB, we hover over the up-arrow icon and select Buckets.

Next, click + Create bucket.

In the dialog box that appears, enter a name for the bucket. In this example, we will name it glesys-my-test-db. Then, click Create.

Next, click on the Scrapers tab, then select Create scraper.

To set up your scraper, you need to provide some specific information. In this case, we will name the scraper GleSYS Scraper my-test-db. Next, you need to select the location where the scraper will store the data; for this example, we will choose the bucket we created earlier.

In the Target URL field, enter the URL for the metrics. You can find this URL in the Monitoring section on the overview page of your database. Click Create when you are done.

Everything is now set up and ready for use. To explore the metrics, click on the graph icon to open Data Explorer.

To simplify your experience, toggle the option to Switch to old Data Explorer. Here, you can click through the available metrics to visualize them.

In the example below, we first select the bucket glesys-my-test-db. Next, we choose the metrics disk_total and disk_used. In the Field column, we select gauge. In the next column, we switch to path and choose the root path, indicated by a forward slash (/). Finally, we click Submit.

Now, you can hover over the graph to see the total storage of your database and the amount that has been used.

4. Delete a database

If you wish to delete a database, click the three dots beside it on the database overview page, then select Delete.

In the dialog box that appears, enter the name of the database in the field and click Delete to confirm.