Database RDB APIv1

Scaleway Database is a service that make your database's management easy as pie. It provides simple and reliable solutions to create and run your database, dealing with security and high availability to ensure the durability of your data.

The following sections define concepts such as Instance, Database Engine, Configuration, ACL, Failover, Configuration, Backup and Restore in Scaleway Database.

A Database engine is the software component that stores and retrieves your data from a database. For example, PostgreSQL is a database engine and it implements the SQL standard. There are several database engines based on the SQL standard, only PostgreSQL for now. Database Engines have versions. Differences in major versions represents changes that can break the backward compatibility. PostgreSQL major versions 9.6, 10 and 11 are available during this phase.

A Database instance is composed of one or more computing nodes, depending on the chosen HA setting. A database replica is synchronized with a leader. Synchronous replication offers the ability to confirm that all changes intended by a transaction have been transferred and applied to one or more synchronous standby servers.

A Database instance has settings that let you tune it to better fit your needs. Available settings depend on the Database Engine and its version. Each instance setting entry has a default value that the user can override. The deletion of a setting entry will restore the entry's default value.

Access control list rules define which hosts can reach your database instance at network level. You can add, edit or delete rules from your ACLs. The initial setup of an instance allows full network access from anywhere.

You can order a high availability database instance. With this configuration, we install the RDBMS (Relational Database Management System) on two dedicated compute instances. We also make sure those instances are on two separated racks.

In a high availability database instance, we choose to use synchronous replication. With this replication mode, write operations are routed to one database node. The other node (standby node) replicates these data. Write operations are acknowledged to the user only when they are applied on Master AND on the standby node. With this system, if a node goes down, data integrity is guaranteed. You can't be in a situation of data corruption where the master node has more data than the slave. This replication mode introduces a bit of overhead on the database system but for us, your data integrity is the most important.

High availability instances are monitored continuously and got an autohealing feature. If a database node goes down, the standby node will take write operations in less than 10 seconds letting you be able to continue your business as usual with read and write operations. During this phase, your instance switch to AUTOHEALING status (blue dot in the console). During this period, your high availability instance is working on the remaining database node for at most 700 seconds. After 700 seconds, if your database node is still considered as down, a new node is spawned and automatically configured as a standby database node. Once configured, your database instance get the READY status. With this system, your database instance is unavailable only for a few seconds letting your app working as usual.

Databases can be saved to and restored from our storage facilities, located on a different physical location than your database. Each logical database inside of a database instance is backed up and therefore restorable separately.

The region is a geographic zone of the following list:

• fr-par

You can use cURL to send request to APIs, we will provide curl example for each step.

Ubuntu

?> sudo apt-get install curl

Mac OS

?> brew install curl

Go to the following link: https://console.scaleway.com/register Provide a valid e-mail address and a password.

Login to your Scaleway account and create a token from the credentials page. A secret_key and an access_key will show on your screen, the secret_key will be used as X-Auth-Token.

reference: https://www.scaleway.com/docs/generate-an-api-token/

The organization id is required to create a database. You will need to query:

curl https://account.scaleway.com/organizations -H "X-Auth-Token: $secret_key"

To get your organization id:

{
"organizations": [
  {
->    "id": "000a115d-2852-4b0a-9ce8-47f1134ba95a",
    "name": "jsnow@got.wint",
    "users": [
      {
        ...
      }
    ]
  }
]
}

reference: https://www.scaleway.com/docs/retrieve-my-organization-id-throught-the-api/

This documentation shows how to create and connect to a PostgreSQL Database using Scaleway Database product.

Requirements:

You have to complete the "Setup" explained on upper sections, you are logged in scaleway and have your API Token and Organization ID. We are going to create, connect to and delete a database.

Create your database instance

• endpoint: https://api.scaleway.com/rdb/v1/regions/{region}/instances

{
    "organization_id": "xxx",
    "name": "myDB",
    "engine": "PostgreSQL-11",
    "tags": ["donnerstag"],
    "is_ha_cluster": true,
    "node_type": "db-dev-s",
    "disable_backup": true,
    "user_name": "my_initial_user",
    "password": "thiZ_is_v&ry_s3cret"
}

Example:

?> curl -X POST -d '{}' -H "Content-Type: application/json" \
-H "X-Auth-Token: $secret_key" https://api.scaleway.com/rdb/v1/regions/{region}/instances

Now you've created your database instance, you can open a connection between a database client and your database. You will need a PostgreSQL client such as psql

List instances

• endpoint: https://api.scaleway.com/rdb/v1/regions/{region}/instances

example:

?> curl -X GET -H "Content-Type: application/json" \
-H "X-Auth-Token: $secret_key" https://api.scaleway.com/rdb/v1/regions/{region}/instances

{
  "instances": [
    {
      "id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
      "name": "myDB",
      "organization_id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
      "status": "ready",
      "engine": "PostgreSQL-11",
      "endpoint": {
        "ip": "xxx.xxx.xxx.xxx",
        "port": 22245,
        "name": null
      },
      "tags": [
        "donnerstag"
      ],
      "settings": [],
      "backup_schedule": {
        "frequency": 24,
        "retention": 7,
        "disabled": true
      },
      "is_ha_cluster": true,
      "read_replicas": [],
      "node_type": "db-dev-s",
      "volume": {
        "type": "lssd",
        "size": 30000000000
      }
      "created_at": "2019-04-19T16:24:52.591417Z",
      "region": "fr-par"
    }
  ],
  "total_count": 1
}

Get the endpoint, port

• endpoint: https://api.scaleway.com/rdb/v1/regions/{region}/instances/{instance_id}

?> curl -X GET -H "Content-Type: application/json" \
-H "X-Auth-Token: $secret_key" https://api.scaleway.com/rdb/v1/regions/{region}/instances/{instance_id}

{
  "id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
  "name": "myDB",
  "organization_id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
  "status": "ready",
  "engine": "PostgreSQL-11",
  "endpoint": {
    "ip": "xxx.xxx.xxx.xxx",
    "port": 22245,
    "name": null
  },
  "tags": [
    "donnerstag"
  ],
  "settings": [],
  "backup_schedule": {
    "frequency": 24,
    "retention": 7,
    "disabled": false
  },
  "volume": {
    "type": "local",
    "size": {
      "bytes": 50000000000,
    }
  },
  "is_ha_cluster": false,
  "read_replicas": [],
  "node_type": {
    "name": "db-dev-s",
    "stock_status": "available",
    "description": "2 GB ram; 2 cpu; 50 GB LSSD storage",
    "region": "fr-par"
  },
  "created_at": "2019-04-18T15:27:24.177854Z",
  "region": "fr-par"
}

Connect to your database with psql client

?> psql -h 192.0.2.250 -p 28991 -U my_initial_user -d rdb
password:

postsql>

Congratulations! You have done your first steps on Database product !

• api token: get your api token
• organization id: get your organization id

In order to migrate your postgreSQL instance to a postgreSQL scaleway database instance, you will need the following informations:

• pg_dump command-line installed
• psql command-line installed
• scaleway database instance ip
• scaleway database instance port
• scaleway database instance user and password
• source instance user
• source instance port if required
• list of the database that you want to migrate

You can export your data from your source instance to a local file by performing the following command:

?> pg_dump –-host=<host> --port=<port> --username=<name> --dbname=<database_name> --file=<database>.sql

If your source postgreSQL is in a docker container, the command will be:

?> docker exec -it $CONTAINER_NAME pg_dump --username=<name> --dbname=<database_name> > <database>.sql

In order to restore a database, you have to create the destination database and users of that database in your instance. You can create your database and roles by using the web console, or by using the API. Ensure that your user permission is correctly set to all.

• create database: https://developers.scaleway.com/en/products/rdb/api/#post-560a84
• create user: https://developers.scaleway.com/en/products/rdb/api/#post-944e53

Once you have created each destination database and its users, you can import your data from the <database>.sql file to your scaleway database instance by performing the following command:

?> psql --file=<database>.sql --host=<Scaleway Database Instance Ip> --port=<Scaleway Database Instance Port> --username=<user> --dbname=<destination database name>

Even if we manage your database storage space, you're responsible for its usage.

In the case you're reaching the space provisioned for your instance, the instance will shift to the disk_full status, informing you that there is only 1GB left on your database instance. This lets you either scale up the disk on the console or leaves you enough room to do some cleanup.

In disk_full mode, the default transaction mode will shift to read only, allowing your application to still access its data, while preventing new data from being inserted.

In order to remove data from your instance, you must connect to it as usual, but explicitly shift to a read write transaction, like this:

BEGIN;
SET TRANSACTION READ WRITE;
-- do some cleanup
COMMIT;

When enough space is available on the instance, the default transaction mode will change back to read write, allowing normal operation again.