Scaleway Elements Database for PostgreSQL and MySQL

Relational Database Instances Overview

Scaleway Elements Database provides fully-managed relational database instances, providing MySQL and PostgreSQL as database engines.
The product lets you focus on development, rather than administration or configuration. It comes with high-availability mode, data replication, and automatic backups.

In comparison to traditional database management, which requires customers to provision their infrastructure and resources to manage their databases, Scaleway Elements Database offers the user access to a database instance without the need for setting up the hardware or configuring the software. Scaleway handles the provisioning, manages the configuration, and provides useful features as high availability, Automated Backup, User management, and more.

Requirements

Core Concept

Instance: An instance is made up of multiple (at least 1) dedicated compute nodes, and is running a single Database Engine. Exactly one database engine is running on each node.

Engine: A Database engine is the software component that stores and retrieves your data from a database. Currently PostgreSQL version 9.6, 10, 11 and 12 are available. MySQL is available in version 8.

Endpoint: A point of connection to a database which is currently TCP. The endpoint is associated with an IPv4 address and a port, and contains the information of whether the endpoint is read-write or not.

Allowed IPs: Access Control List (ACL) rules defines permissions for remote access to a database.

Database Backup: A Database backup is a dated export of a database stored on an offsite backup backend. A database can be restored from a backup.

Creating a Database Instance

In the Storage section of the side menu, click Database. If you do not have a Database already created, the product presentation is displayed.

Scaleway Console

1 . Click Create a Database Instance.

The creation wizard displays.

2 . Choose a Database Engine.

Scaleway Database is currently available with the following database engines:

  • PostgreSQL is available in version 9.6, 10, 11, and 12.
  • MySQL is currently in version 8.

3 . Choose a Region.

You can choose a region in which your database will be deployed geographically.
Currently we provide the following locations:

  • FR-PAR: Paris, France
  • NL-AMS: Amsterdam, The Netherlands
  • PL-WAW: Warsaw, Poland

4 . Select the configuration of your instance.

You can choose between two configuration options:

  • High Availability - Creates a cluster of two nodes, one a primary node and the other a secondary node, with replication between them. If the primary node fails for any reason, the secondary can take over requests, reducing downtime.

In a high availability database instance, we choose to use synchronous replication. With this replication mode, writes operations are routed on one database node. The other node (standby node) replicate this data. Writes 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 where the master node have more data than the slave. This replication mode introduce 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 writes 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.

  • Standalone - Creates a standalone database provisioned on a single node, with no replication.

5 . Choose a Node Type.

Several types are available to match your requirements.

6 . Choose your storage type and the capacity.

You can choose between two storage types:

  • Local SSD Storage - In this case, your storage capacity is fixed and tied to your compute resources (vCPU/RAM).
  • Block SSD Storage - With this type, your storage is decoupled from your compute resources. You can increase your storage space without changing your node type, with no downtime. You can define your storage capacity by entering the desired amount in the box. You can increase your volume to up to 10 TB.

Note: The Block Storage type is only available for instances in the AMS and WAW regions.

7 . If necessary, enable automatic backups.

By default, backups are created on a daily basis. Find out more in the Backups section of this documentation.

8 . Add a name and set a password for your user.

Every Database instance contains a set of database users with different privileges.

By default, users are created with admin rights. A user with admin rights on the instance has the ability to create other users (CREATE_ROLE) and to create new logical databases (CREATE_DATABASE). A non-admin user has no rights at all on any database resources by default, with the exception to connect on the rdb default database.

9 . Enter a name for your instance.

Optionally, you can assign tags to it.

10 . Click Create a new Database Instance to confirm and launch the creation.

Deleting the Database Instance

From the Database list, you can:

  • Delete a Database directly from the menu, by clicking Delete. This action is irreversible and all your data associated with this instance will be deleted.

  • Click on the Database name to access the overview page. Scroll down the page to the Delete Instance section.

Adding Users

Once the Instance is created, it is displayed in the instance list. Click on the database instance name or on the >More info to access the database instance overview.

Users can connect to a database and access its data. Each one has a different set of customizable permissions.

1 . From the top menu, click Users

2 . If no user were created beforehand, the user specified during the instance creation is listed. Click Add User

In the User Information tab, you can grant admin rights to the newly created user. Admin rights allow a user to create logical databases and users. It does not override permissions on logical databases. Logical databases permissions must be set in the Permissions tab.

3 . Right slide the toggle button to grant admin rights

4 . Enter the Username and Password

5 . Click Create a user Once the user is created, it is displayed in the user list.

To update the password or delete the user, you can click on the to display the associated options.

Each user has a different set of permissions. Permissions define the actions a user can perform on a logical database.

In the Permissions tab, select the appropriate permissions according to the user. Three types of permissions are available:

None: No access to the database Read: Allow users to read tables and fields in a database Write: Allow users to write content in databases

Managing Backups

Backups are logical data dumps of an individual database. They are useful when you need to restore a specific logical database. Scaleway’s Managed Database makes backups which you can restore.

They can optionally have a retention period defined, at the end of which they will be automatically deleted. You can also manually delete these backups. You can export and download the backup to perform specific data restoration.

To ensure the safety of your backups, we make sure to store them in a physical location different from that of the source instance.

Backups can be manually deleted or else will be purged after the defined retention period is reached.

Creating Backups

Scaleway Elements Database allows you to create automatic backups of your database. When the Autobackup feature is enabled, Scaleway managed database instances create and manage backups according to the backup schedule configuration.

Autobackups

Upon the creation of an instance, the Autobackup feature is enabled by default. A backup schedule policy will be created, with a frequency of 1 per day and a retention period of 7 days. You can change this default configuration after you create the instance.

Although it is enabled by default, you can disable the feature before you create the instance, by deselecting the box in the creation wizard. You can also disable it after instance creation.

The backup schedule can be managed from the database instance Overview page.

Important: For database instances that use Block Storage volumes, Scaleway Database uses the Snaphots feature to create Autobackups.

Once the Instance is created, it is displayed in the instance list.

1 . Click on the database name or on the menu, then on More info to access the database instance overview.

2 . Click the Edit button within the Backup section, to configure the backup frequency and retention or to disable or enable the automatic backup feature.

Manual Backups

It is also possible to create manual backups of your database in case you need to have a backup of a specific state of the database instance.

1 . Click on the Backups tab, to enter the backup section.

2 . Click on Create a Backup.

3 . Enter a name for the backup, choose the database to backup and set the expiration date on which the backup will be removed automatically.

4 . Click on Create a Backup to launch the backup process for the database.

Restoring Backups

From the top menu, click Backups. If you did not enable the backup option when creating the Database instance, the backup list is empty.

To restore a backup:

1 . Click on > Restore.

From the API, it is now possible to restore a backup on a logical database, of the same instance, which is different from the source logical database. In addition, the option of restoring a backup in another instance is also available.

2 . Click Restore this backup

To edit a backup:

1 . Click on the >Edit

2 . Enter the backup name and expiration date

3 . Click Ok

Note: Large database backups can take up to several hours to restore.

Deleting Backups

When you delete an instance, the backups and snapshots created with the Autobackup feature are not automatically deleted.

Automatic backups can be manually deleted or will be purged after the retention period defined in the backup schedule configuration.

1 . Select the Database instance

2 . Click on the >Delete

3 . Click Delete backup

Configuring Allowed IPs

Once the Instance is created, it is displayed in the instance list. Click on the database name or on the >More info to access the database instance overview.

From the top menu, click Allowed IPs. Allowed IPs define remote access restrictions to your database instance. The default entry 0.0.0.0/0 enables any host to establish a connection.

To add an IP:

1 . Click on Add IP. The IP can be a single IP or an IP block. Note that the IP must be specified in CIDR format, i.e. 198.51.100.135/32 for a single IP or 198.51.100.0/24 for an IP block.

2 . Enter a single IP address or a sub-network (e.g., 198.51.100.0/24)

To delete an IP:

1 . From the IP’s list, click on the bin icon.

2 . Click Delete your IP

Important: To restore initial settings (allow connections from all IPs), add the following IP block to the list: 0.0.0.0/0

Configuring Advanced Settings

Once the Instance is created, it is displayed in the instance list. Click on the database name or on the >More info to access the database instance overview.

From the top menu, click Advanced Settings. If you did not configure advanced settings, the list is empty. Configure Advanced Settings to modify the default configuration of your database engine. Changing these settings can have an impact on the performance or your Database Instance.

To add advanced settings: 1 . Click on Add custom setting

2 . Click Add parameters

3 . Select your parameter from the dropdown list:

  • tcp_keepalives_count
  • tcp_keepalives_idle
  • tcp_keepalives_interval
  • commit_delay
  • default_statistics_target
  • default_transaction_deferrable
  • default_transaction_read_only
  • maintenance_work_mem
  • statement_timeout
  • temp_buffers
  • work_mem
  • lock_timeout
  • max_locks_per_transaction
  • temp_file_limit
  • max_connections
  • idle_in_transaction_session_timeout

4 . Configure the value associated with your setting

5 . Validate with the check icon.

To delete advanced settings:

1 . Next to the setting to be deleted, click the bin icon

2 . Click Delete the advanced setting

Going Further?

In case you need to go further with the service refer to the API documentation or the Database FAQ.

To give you some inspiration for some common use cases, we have written several tutorials, including:

Discover the Cloud That Makes Sense