Interacting with DBaaS

Exoscale DBaaS can be managed via the CLI or the Web Portal. Follow the guides below to create a first data service.

Interacting with DBaaS via the CLI

Prerequisites

To interact with DBaaS you will need the Exoscale CLI >= 1.45.

Binaries for all major platforms are available at https://github.com/exoscale/cli/releases/latest

  • Windows
  • Linux
  • MacOS

Or use your usual package upgrade mechanism to retrieve the latest version.

The CLI relies on the API functionality documented at Exoscale APIv2 API documentation

The quick-start guide for Exoscale DBaaS will demonstrate a first service, Managed PostgreSQL, but the same concept applies for all available services.

Bootstrap instructions

Verify the CLI is working properly. This can be achieved by listing the types of DBaaS services available with the command:

exo dbaas type list

┼────────────┼────────────────────┼─────────────────┼
│    NAME    │ AVAILABLE VERSIONS │ DEFAULT VERSION │
┼────────────┼────────────────────┼─────────────────┼
│ pg         │ 10, 11, 12, 13, 14 │ 14              │
│ opensearch │ 1                  │ 1               │
│ redis      │                    │ -               │
│ kafka      │ 2.8, 3.0, 3.1      │ 3.1             │
│ mysql      │ 8                  │ 8               │
┼────────────┼────────────────────┼─────────────────┼

Then per service you can list the plans available:

exo dbaas type show pg --plans

┼──────────────┼─────────┼────────┼─────────────┼────────────┼────────────┼
│     NAME     │ # NODES │ # CPUS │ NODE MEMORY │ DISK SPACE │ AUTHORIZED │
┼──────────────┼─────────┼────────┼─────────────┼────────────┼────────────┼
│ startup-8    │ 1       │ 4      │ 8.6 GB      │ 188 GB     │ true       │
│ startup-4    │ 1       │ 2      │ 4.3 GB      │ 86 GB      │ true       │
│ startup-32   │ 1       │ 8      │ 34 GB       │ 537 GB     │ true       │
│ startup-225  │ 1       │ 24     │ 242 GB      │ 1.4 TB     │ false      │
│ startup-16   │ 1       │ 4      │ 17 GB       │ 376 GB     │ true       │
│ startup-128  │ 1       │ 16     │ 137 GB      │ 1.1 TB     │ false      │
│ premium-8    │ 3       │ 4      │ 8.6 GB      │ 188 GB     │ true       │
│ premium-4    │ 3       │ 2      │ 4.3 GB      │ 86 GB      │ true       │
│ premium-32   │ 3       │ 8      │ 34 GB       │ 537 GB     │ true       │
│ premium-225  │ 3       │ 24     │ 242 GB      │ 1.4 TB     │ false      │
│ premium-16   │ 3       │ 4      │ 17 GB       │ 376 GB     │ true       │
│ premium-128  │ 3       │ 16     │ 137 GB      │ 1.1 TB     │ false      │
│ hobbyist-2   │ 1       │ 2      │ 2.1 GB      │ 8.6 GB     │ true       │
│ business-8   │ 2       │ 4      │ 8.6 GB      │ 188 GB     │ true       │
│ business-4   │ 2       │ 2      │ 4.3 GB      │ 86 GB      │ true       │
│ business-32  │ 2       │ 8      │ 34 GB       │ 537 GB     │ true       │
│ business-225 │ 2       │ 24     │ 242 GB      │ 1.4 TB     │ false      │
│ business-16  │ 2       │ 4      │ 17 GB       │ 376 GB     │ true       │
│ business-128 │ 2       │ 16     │ 137 GB      │ 1.1 TB     │ false      │
┼──────────────┼─────────┼────────┼─────────────┼────────────┼────────────┼

Create a first service

The sequence below is shown for service pg but is valid with all DBaaS service types.

To create a first service, use the exo dbaas create command. The --help flag will give all the details on the usage, and a minimal command is:

exo dbaas create pg hobbyist-2 test-pg --pg-ip-filter 1.2.3.4/32 -z de-fra-1

Creating Database Service "test-pg"...
┼───────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼
│   DATABASE SERVICE    │                                                                                                                                │
┼───────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼
│ Zone                  │ de-fra-1                                                                                                                       │
│ Name                  │ test-pg                                                                                                                        │
│ Type                  │ pg                                                                                                                             │
│ Plan                  │ hobbyist-2                                                                                                                     │
│ Disk Size             │ 8.0 GiB                                                                                                                        │
│ State                 │ rebuilding                                                                                                                     │
│ Creation Date         │ 2022-06-09 08:26:13 +0000 UTC                                                                                                  │
│ Update Date           │ 2022-06-09 08:26:13 +0000 UTC                                                                                                  │
│ Nodes                 │ 1                                                                                                                              │
│ Node CPUs             │ 2                                                                                                                              │
│ Node Memory           │ 2.0 GiB                                                                                                                        │
│ Termination Protected │ true                                                                                                                           │
│ Maintenance           │ sunday (11:52:43)                                                                                                              │
│ Version               │ 14                                                                                                                             │
│ Backup Schedule       │ 08:19                                                                                                                          │
│ URI                   │ postgres://avnadmin:xxxxx@test-pg-exoscale-980a712f-5c76-4ec4-a3bc-4e4e442deef2.aivencloud.com:21699/defaultdb?sslmode=require │
│ IP Filter             │ 1.2.3.4/32                                                                                                                     │
│ Components            │                                                                                                                                │
│                       │   pg          test-pg-exoscale-980a712f-5c76-4ec4-a3bc-4e4e442deef2.aivencloud.com:21699   route:dynamic   usage:primary       │
│                       │   pgbouncer   test-pg-exoscale-980a712f-5c76-4ec4-a3bc-4e4e442deef2.aivencloud.com:21700   route:dynamic   usage:primary       │
│                       │                                                                                                                                │
│ Users                 │ n/a                                                                                                                            │
┼───────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼

PS1: from now on add the -z <zone> flag to manage this db instance

PS2: your organization is limited to 3 DBaaS by default services in total. Reach out to your Exoscale support if you encounter the message Usage of resource 'dbaas' has been exceeded.

Connect and use your DB

To connect to your DB instance you first need to retrieve your credentials:

exo dbaas -z de-fra-1 show test-pg --uri

postgres://avnadmin:AVNS_SbyJOj29FaxJHa4XJEj@test-pg-exoscale-980a712f-5c76-4ec4-a3bc-4e4e442deef2.aivencloud.com:21699/defaultdb?sslmode=require

Then check the status of your db instance, it should have changed from rebuilding right after the create command to running

exo dbaas show test-pg -z de-fra-1

┼───────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼
│   DATABASE SERVICE    │                                                                                                                                │
┼───────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼
│ Zone                  │ de-fra-1                                                                                                                       │
│ Name                  │ test-pg                                                                                                                        │
│ Type                  │ pg                                                                                                                             │
│ Plan                  │ hobbyist-2                                                                                                                     │
│ Disk Size             │ 8.0 GiB                                                                                                                        │
│ State                 │ rebuilding                                                                                                                     │
│ Creation Date         │ 2022-06-09 08:26:13 +0000 UTC                                                                                                  │
│ Update Date           │ 2022-06-09 08:27:25 +0000 UTC                                                                                                  │
│ Nodes                 │ 1                                                                                                                              │
│ Node CPUs             │ 2                                                                                                                              │
│ Node Memory           │ 2.0 GiB                                                                                                                        │
│ Termination Protected │ true                                                                                                                           │
│ Maintenance           │ sunday (11:52:43)                                                                                                              │
│ Version               │ 14                                                                                                                             │
│ Backup Schedule       │ 08:19                                                                                                                          │
│ URI                   │ postgres://avnadmin:xxxxx@test-pg-exoscale-980a712f-5c76-4ec4-a3bc-4e4e442deef2.aivencloud.com:21699/defaultdb?sslmode=require │
│ IP Filter             │ 1.2.3.4/32                                                                                                                     │
│ Components            │                                                                                                                                │
│                       │   pg          test-pg-exoscale-980a712f-5c76-4ec4-a3bc-4e4e442deef2.aivencloud.com:21699   route:dynamic   usage:primary       │
│                       │   pgbouncer   test-pg-exoscale-980a712f-5c76-4ec4-a3bc-4e4e442deef2.aivencloud.com:21700   route:dynamic   usage:primary       │
│                       │                                                                                                                                │
│ Users                 │ n/a                                                                                                                            │
┼───────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼

You can now connect to the database engine:

psql postgres://avnadmin:AVNS_SbyJOj29FaxJHa4XJEj@test-pg-exoscale-980a712f-5c76-4ec4-a3bc-4e4e442deef2.aivencloud.com:21699/defaultdb?sslmode=require

psql (9.2.24, server 14.3)
WARNING: psql version 9.2, server version 14.0.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

defaultdb=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 _aiven    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =T/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 defaultdb | avnadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

defaultdb=>

Other commands

In addition to the creation commands listed above, you will find:

  • List: exo dbaas list
  • Delete: exo dbaas delete test-pg -z de-fra-1

The update command will enable you to adjust parameters of a DB instance.

Disable termination protection to delete the instance
exo dbaas update test-pg -z de-fra-1 --termination-protection=false

Updating Database Service "test-pg"...
┼───────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼
│   DATABASE SERVICE    │                                                                                                                                │
┼───────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼
│ Zone                  │ de-fra-1                                                                                                                       │
│ Name                  │ test-pg                                                                                                                        │
│ Type                  │ pg                                                                                                                             │
│ Plan                  │ hobbyist-2                                                                                                                     │
│ Disk Size             │ 8.0 GiB                                                                                                                        │
│ State                 │ running                                                                                                                        │
│ Creation Date         │ 2022-06-09 08:26:13 +0000 UTC                                                                                                  │
│ Update Date           │ 2022-06-09 08:40:16 +0000 UTC                                                                                                  │
│ Nodes                 │ 1                                                                                                                              │
│ Node CPUs             │ 2                                                                                                                              │
│ Node Memory           │ 2.0 GiB                                                                                                                        │
│ Termination Protected │ false                                                                                                                          │
│ Maintenance           │ sunday (11:52:43)                                                                                                              │
│ Version               │ 14.3                                                                                                                           │
│ Backup Schedule       │ 08:19                                                                                                                          │
│ URI                   │ postgres://avnadmin:xxxxx@test-pg-exoscale-980a712f-5c76-4ec4-a3bc-4e4e442deef2.aivencloud.com:21699/defaultdb?sslmode=require │
│ IP Filter             │ 1.2.3.4/32                                                                                                                     │
│ Components            │                                                                                                                                │
│                       │   pg          test-pg-exoscale-980a712f-5c76-4ec4-a3bc-4e4e442deef2.aivencloud.com:21699   route:dynamic   usage:primary       │
│                       │   pgbouncer   test-pg-exoscale-980a712f-5c76-4ec4-a3bc-4e4e442deef2.aivencloud.com:21700   route:dynamic   usage:primary       │
│                       │                                                                                                                                │
│ Users                 │ avnadmin (primary)                                                                                                             │
┼───────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼

It is therefore now possible to delete the instance:

exo dbaas -z de-fra-1 delete test-pg

[+] Are you sure you want to delete Database Service "test-pg"? [yN]: y
 ✔ Deleting Database Service "test-pg"... 0s

Otherwise you will get the following error:

exo dbaas -z de-fra-1 delete test-pg

[+] Are you sure you want to delete Database Service "test-pg"? [yN]: y
 ✔ Deleting Database Service "test-pg"... 0s
error: Delete "https://api-de-fra-1.exoscale.com/v2/dbaas-service/test-pg": invalid request: The service is protected against termination and shutdown. Remove termination protection first.
Other global parameters currently supported
  • Maintenance day of the week
  • Maintenance time slot

Getting further

You can learn more about the Exoscale DBaaS architecture and concepts in the overview as well as getting ready with data-protection and lifecycle managemenent. You can then get to the details of each service specifics:

Interacting with DBaaS via Web Portal

Prerequisites

To start an Exoscale DBaaS, only an active Exoscale account is necessary, there is no other component required, Exoscale DBaaS can be used without any other Exoscale product interaction.

The Web Portal quick-start guide for Exoscale DBaaS will demonstrate creating a first service interactively, Managed PostgreSQL, but the same concept applies for all available services.

Adding vi the UI

First navigate to the Exoscale DBaaS menu

Exoscale DBaaS navigation menu

Then select the service you want to add from the top right green button, for example here for a Managed PostgreSQL

Exoscale DBaaS select service dropdown

Finally complete the name, zone location and service plan then click the CREATE button to launch the service.

Exoscale DBaaS add PostgreSQL service

Using the Exoscale DBaaS from the Web Portal

The connection details such as the user, password or certificate and uri are available from the service detail page as follow.

Exoscale DBaaS details PostgreSQL service

Copy these to your favorite database client or application to start interacting with the database and create tables.

Read on to the getting further section for next steps.