How we make our DBaaS

Now we are in Postgres Professionalwe are developing a new interesting product – DataBase as a Service, and in this article I would like to talk a little about our successes, find out your opinion and listen to possible suggestions.

But before we talk about the possibilities, we need to agree on what DBaaS actually is and what we are doing here. There won't be any big news: DBaaS is a subset of as-a-service services that focuses on relieving the administrator of the pain associated with database administration. In our case we will talk about PostgreSQL.

If you try to formalize it, then in the classic “I can do everything myself” approach, in order to get a working database, you need to go something like this: set up a hardware server, download and configure the OS, install the DBMS with auxiliary packages, then configure the whole thing and finally -start working. After some time, or better yet, right away, you will realize the need to build a backup policy, a flexible system of access and keeping software versions up to date. And somewhere very soon we also need to think about scaling this entire structure, because this topic for databases is not the easiest, and many carefully put it off until the last minute.

Naturally, you want to avoid all this as much as possible. Therefore, absolutely as expected, the engineers came up with a simple idea: we will give the user that big red “Do it well” button, and we will hide and automate all operations related to the deployment and maintenance of databases from him.

Therefore, we decided to make our DBaaS with the expectation that it will be launched in public and private clouds. But, as we all understand, with the departure of well-known vendors, the list has been greatly reduced. And of the private ones, in fact, the only ones left are clouds on OpenStack. So we focus our development on this.

Some terminology

To talk about DBaaS, we need to agree on what it is called and what it is called. So that there are no stories when for some a node is a physical server, while for others it is a logical entity.

DBaaS is designed quite simply. This is a kind of machine where the DBaaS service itself runs, to which clients connect via API or via a web interface. Next, DBaaS creates virtual machines, installs its managing agent on them and deploys the database according to the client’s request.

Simple DBaaS schematic diagram from Postgres Professional

And now the very terms that most often cause confusion:

Database – We support and develop a solution for PostgreSQL. It just so happens that we are engaged in postgres and work only with it.
An instance is a virtual machine on which one or more databases are running. That is, within the framework of a DBMS instance, one is running, but several databases can be launched within a given one Postgres application.
A cluster is a fault-tolerant group of instances. Everything here is quite transparent, but we must make a reservation that a cluster may have a degenerate case when it consists of one instance. A sort of pseudo-cluster.
A project is a logical unit of our DBaaS for managing groups of clusters. The project has an owner who creates internal users and gives them access rights.

In total, within one project there can be several clusters. Each cluster can have one or more instances. And each instance runs databases.

About the available functionality

General view of the main cluster management page. For each cluster, all its main parameters and status are shown

Working with DBaaS begins with creating a project, within which the necessary clusters and users will already be deployed. The cluster can be made either with one instance or fault-tolerant. That is, a node will be created with a master and slaves (one synchronous, the other asynchronous). Patroni is responsible for automatic switching in case of master failure, but manual switching functionality is also available.

General view of the cluster control panel. You can see the use of allocated quotas, the role of each node and the type of replication

In terms of configuration options, all the most expected things are available. You can dynamically change the capacity allocated to instances, including the size of disks. Theoretically, you can even change the number of processors on the fly, but this will lead to a reboot of the node. This is a feature of OpenStack. And, what’s convenient, you can configure both all members of the cluster at once, as well as individual instances.

In the instance editing menu, in addition to the basic settings, you can set parameters directly for postgres

I'll tell you about managing extensions. The end user is not given database owner rights, so if an extension requires them for installation, this can only be done through DBaaS.

If such rights are not needed, the user can install the extension themselves. Creating users and assigning rights to them is also done through DBaaS.

You can check the result right there in the console

And since we touched on the topic of accounts, now we will talk a little about their creation, authorization and authentication. Since we initially aimed at private clouds, LDAP became the main authorization provider. From the point of view of implementing alternatives, there are no difficulties, it just became the first.

In the user management window, you can open the Postgres console. All that remains is to enter the password

The user role model is hierarchical. There is an all-powerful DBaaS Administrator who manages everything, including resource quotas for projects. CPU, RAM and allocated disk size are quotas. If the quota is exceeded, the creation of new clusters and the addition of instances is blocked.

Below in the hierarchy there is a group of Project Owners who manage at the level of individual projects and appoint Project Users. The latter can be assigned rights within the management of specific clusters.

On the database management screen, ready-made connection strings are displayed

Regarding backup, DBaaS can create dumps (logical copies) and classic backups (physical copies). Their difference is in compatibility: having made a logical copy on the 14th version of Postgres, it can be restored on the 15th. This won’t work with physical data, but it has its own important advantage – recovery at a certain point in time, even for a specific transaction.

Dumps are created and restored from the Database Management screen

Both types of backups are stored inside S3, which is already in the cloud. This allows us to save them very quickly and restore them just as quickly. Moreover, you can restore everything from a dump at once or choose between restoring only the data or the schema.

You can restore the dump directly to a freshly created database

As for backups, flexible scheduling and storage policies are available for them. It is also possible to make continuous backups with archiving of the write-ahead log, better known as WAL logs.

When creating a new cluster, you can immediately restore it from backup

And for dessert, let there be the Events tab, where all significant events within the project are stored in a convenient form and with a lot of filters. Detailing is carried out down to the instance, and events are ranked by importance. Accordingly, now in one place you can find out when, why and which node changed its replication status, who became the leader, where the new database came from, and so on. A lot of events are logged and the full list will be in the documentation.

And the most interesting thing is how this luxury can be controlled. Initially, we approached this issue as true developers and implemented our own API, through which any possible operation in DBaaS is available. A little later, a web interface was created, rapidly developing in its capabilities. And what’s interesting is that we implemented a terraform provider, allowing you to create projects in the classic infrastructure as a code approach of declarative description.

And a short video with a live demonstration of DBaaS. It was recorded six months ago and some things have changed (for example, it became possible to manage table spaces), but all the main functions are shown there.