Managed Database for PostgreSQL and MySQL provides fully-managed relational Database Instances, with MySQL or PostgreSQL as database engines. The resource allows you to focus on development rather than administration or configuration. It comes with a high-availability mode, data replication, and automatic backups.
Compared to traditional database management, which requires customers to provide their infrastructure and resources to manage their databases, Managed Database for PostgreSQL and MySQL Instance offers the user access to Database Instances without 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.
Refer to our dedicated concepts page to find definitions of the different terms referring to Managed Database for PostgreSQL and MySQL.
Requirements
To perform the following steps, you must first ensure that:
curl
1. Configure your environment variables.
Note: This is an optional step that seeks to simplify your usage of the APIs.
2. Edit the POST request payload you will use to create your Database Instance. Replace the parameters in the following example:
Parameter | Description |
---|---|
project_id | The ID of the Project you want to create your Database Instance in. To find your Project ID you can list the projects or consult the Scaleway console. |
engine | REQUIRED Version ID of the database engine. To check the list of available engines you can use the folowing endpoint: https://api.scaleway.com/rdb/v1/regions/$SCW_REGION/database-engines |
name | Name of the Database Instance |
node_type | REQUIRED The node type. To check the list of available node types you can use the folowing endpoint: https://api.scaleway.com/rdb/v1/regions/$SCW_REGION/node-types |
is_ha_cluster | BOOLEAN Defines whether High Availability is enabled for the Database Instance |
disable_backup | BOOLEAN Defines whether automated backups are disabled for the Database Instance |
tags | The list of tags ["tag1", "tag2", ...] that will be associated with the Database Instance. Tags can be appended to the query of the List Redisâ„¢ Database Instances call to show results for only the Database Instances using a specific tag. You can also combine tags to list Database Instances that posess all the appended tags. |
user_name | REQUIRED Identifier of the default user, which is created concurrently with the Database Instance |
password | REQUIRED Password for the default user |
volume_type | Type of volume where data is stored. You can specify either local volume (lssd ) or block volume (bssd ). The default value is lssd |
volume_size | Volume size when volume_type is bssd . The value should be expressed in bytes. For example 30GB is expressed as 30000000000 |
3. Create a Database Instance by running the following command. Make sure you include the payload you edited in the previous step.
4. List your Database Instances.
You should get a response like the following:
Note: This is a response example, the UUIDs and IP address displayed are not real.
5. Retrieve your Database Instance IP and port from the response.
Note: In the example above, the IP and port are 198.51.100.0
and 22245
, respectively.
6. Connect to your Database Instance with the database client of the engine you selected.
For MySQL, run the following command:
For PostgreSQL, run:
For the recurring example, the command would look like:
7. Enter the database password that you defined upon creation.
You are now connected to your Managed Database.
Scaleway's infrastructure is spread across different regions and Availability Zones.
Managed Database for PostgreSQL and MySQL is available in the Paris, Amsterdam and Warsaw regions, which are represented by the following path parameters:
fr-par
nl-ams
pl-waw
Scaleway Database for PostgreSQL supports PostgreSQL versions 11, 12, 13 and 14.
Different modules are available for installation, including TimescaleDB and PostGIS. Refer to the Managed Database for PostgreSQL and MySQL FAQ page for an extensive list of PostgreSQL extensions.
You can create logical databases through the Scaleway console, the Scaleway APIs or SQL.
Scaleway Database for MySQL supports MySQL 8.
mysql_native_password
(default) and caching_sha2_password
authentication are supported.admin
role have access to all logical databases and can create new ones.admin
role have CREATEROLE
and CREATEDB
privileges.SUPERUSER
nor REPLICATION
privileges.Databases that have been backed up and then restored retain the user permission settings in use at the time of backup. If you delete users after backup and then restore your backup in the same database, or if you restore a backup to a different database with different or no users, the permissions configured for them continue to exist, but with no associated owner. This error will put a stop to the restoration process.
To avoid this issue, we recommmend you re-create the users you deleted. In the occasion you restore the backup to a new database, you must create new users with the same names.
For more information about Managed Database for PostgreSQL and MySQL, you can check out the following pages:
If you wish to migrate existing databases to a Managed Database for PostgreSQL or MySQL, you can refer to the Migrating existing databases to a Database Instance tutorial page.
If your Database Instance uses local storage, your local volume might eventually approach full capacity and shift to disk_full
mode. This mode grants you enough space to either upgrade your node type or clear out space in your volume.
A database backup is a dated export of a Database Instance stored on an offsite backend located in a different region than your database, by default. Once a backup is created, it can be used to restore the database. Each logical database in a Database Instance is backed up and can be restored separately.
List all backups in a specified region, for a given Scaleway Organization or Scaleway Project. By default, the backups listed are ordered by creation date in ascending order. This can be modified via the order_by
field.
created_at_asc
, created_at_desc
, name_asc
, name_desc
, status_asc
and status_desc
. The default value is created_at_asc
.Create a new backup. You must set the instance_id
, database_name
, name
and expires_at
parameters.
unknown
, creating
, ready
, restoring
, deleting
, error
, exporting
and locked
. The default value is unknown
.Retrieve information about a given backup, specified by its database backup ID and region. Full details about the backup, like size, URL and expiration date, are returned in the response.
unknown
, creating
, ready
, restoring
, deleting
, error
, exporting
and locked
. The default value is unknown
.Update the parameters of a backup, including name and expiration date.
unknown
, creating
, ready
, restoring
, deleting
, error
, exporting
and locked
. The default value is unknown
.Delete a backup, specified by its database backup ID and region. Deleting a backup is permanent, and cannot be undone.
unknown
, creating
, ready
, restoring
, deleting
, error
, exporting
and locked
. The default value is unknown
.Export a backup, specified by the database_backup_id
and the region
parameters. The download URL is returned in the response.
unknown
, creating
, ready
, restoring
, deleting
, error
, exporting
and locked
. The default value is unknown
.Launch the process of restoring database backup. You must specify the instance_id
of the Database Instance of destination, where the backup will be restored. Note that large database backups can take up to several hours to restore.
unknown
, creating
, ready
, restoring
, deleting
, error
, exporting
and locked
. The default value is unknown
.A database engine is the software component that stores and retrieves your data from a database. Currently PostgreSQL 11, 12, 13 and 14 are available. MySQL is available in version 8.
List the PostgreSQL and MySQL database engines available at Scaleway.
A Database Instance is made up of one or multiple dedicated compute nodes running a single database engine. Two node settings are available: High-Availability (HA), with a main node and one replica, and standalone with a main node. The HA standby node is linked to the main node, using synchronous replication. Synchronous replication offers the ability to confirm that all changes intended by a transaction have been transferred and applied to the synchronous replica node, providing durability to the data.
Note: HA standby nodes are not accessible to users unless the main node becomes unavailable and the standby takes over. If you wish to run queries on a read-only node, you can use Read Replicas
Read Replicas can be used for certain read-only workflows such as Business Intelligence, or for a read-only scaling of your application. Read Replicas use asynchronous replication to replicate data from the main node.
List all Database Instances in the specified region, for a given Scaleway Organization or Scaleway Project. By default, the Database Instances returned in the list are ordered by creation date in ascending order, though this can be modified via the order_by field. You can define additional parameters for your query, such as tags
and name
. For the name
parameter, the value you include will be checked against the whole name string to see if it includes the string you put in the parameter.
created_at_asc
, created_at_desc
, name_asc
, name_desc
, region
, status_asc
and status_desc
. The default value is created_at_asc
.Create a new Database Instance. You must set the engine
, user_name
, password
and node_type
parameters. Optionally, you can specify the volume type and size.
organization_id
and project_id
may be set.organization_id
and project_id
may be set.lssd
and bssd
. The default value is lssd
.unknown
, ready
, provisioning
, configuring
, deleting
, error
, autohealing
, locked
, initializing
, disk_full
, backuping
, snapshotting
and restarting
. The default value is unknown
.Retrieve information about a given Database Instance, specified by the region
and instance_id
parameters. Its full details, including name, status, IP address and port, are returned in the response object.
unknown
, ready
, provisioning
, configuring
, deleting
, error
, autohealing
, locked
, initializing
, disk_full
, backuping
, snapshotting
and restarting
. The default value is unknown
.Update the parameters of a Database Instance, including name, tags and backup schedule details.
unknown
, ready
, provisioning
, configuring
, deleting
, error
, autohealing
, locked
, initializing
, disk_full
, backuping
, snapshotting
and restarting
. The default value is unknown
.Delete a given Database Instance, specified by the region
and instance_id
parameters. Deleting a Database Instance is permanent, and cannot be undone. Note that upon deletion all your data will be lost.
unknown
, ready
, provisioning
, configuring
, deleting
, error
, autohealing
, locked
, initializing
, disk_full
, backuping
, snapshotting
and restarting
. The default value is unknown
.Retrieve information about the TLS certificate of a given Database Instance. Details like name and content are returned in the response.
Clone a given Database Instance, specified by the region
and instance_id
parameters. The clone feature allows you to create a new Database Instance from an existing one. The clone includes all existing databases, users and permissions. You can create a clone on a Database Instance bigger than your current one.
unknown
, ready
, provisioning
, configuring
, deleting
, error
, autohealing
, locked
, initializing
, disk_full
, backuping
, snapshotting
and restarting
. The default value is unknown
.List the available logs of a Database Instance. By default, the logs returned in the list are ordered by creation date in ascending order, though this can be modified via the order_by field.
List remote log details. By default, the details returned in the list are ordered by creation date in ascending order, though this can be modified via the order_by field.
Retrieve the time series metrics of a given Database Instance. You can define the period from which to retrieve metrics by specifying the start_date
and end_date
.
Prepare your Database Instance logs. You can define the start_date
and end_date
parameters for your query. The download URL is returned in the response. Logs are recorded from 00h00 to 23h59 and then aggregated in a .log
file once a day. Therefore, even if you specify a timeframe from which you want to get the logs, you will receive logs from the full 24 hours.
Purge a given remote log from a Database Instance. You can specify the log_name
of the log you wish to clean from your Database Instance.
Renew a TLS for a Database Instance. Renewing a certificate means that you will not be able to connect to your Database Instance using the previous certificate. You will also need to download and update the new certificate for all database clients.
Restart a given Database Instance, specified by the region
and instance_id
parameters. The status of the Database Instance returned in the response.
unknown
, ready
, provisioning
, configuring
, deleting
, error
, autohealing
, locked
, initializing
, disk_full
, backuping
, snapshotting
and restarting
. The default value is unknown
.Upgrade your current Database Instance specifications like node type, high availability, volume, or the database engine version. Note that upon upgrade the enable_ha
parameter can only be set to true
.
node_type
, enable_ha
, volume_size
, volume_type
and upgradable_version_id
may be set.node_type
, enable_ha
, volume_size
, volume_type
and upgradable_version_id
may be set.node_type
, enable_ha
, volume_size
, volume_type
and upgradable_version_id
may be set.lssd
and bssd
. The default value is lssd
. Only one of node_type
, enable_ha
, volume_size
, volume_type
and upgradable_version_id
may be set.node_type
, enable_ha
, volume_size
, volume_type
and upgradable_version_id
may be set.unknown
, ready
, provisioning
, configuring
, deleting
, error
, autohealing
, locked
, initializing
, disk_full
, backuping
, snapshotting
and restarting
. The default value is unknown
.Retrieve information about the logs of a Database Instance. Specify the instance_log_id
and region
in your request to get information such as download_url
, status
, expires_at
and created_at
about your logs in the response.
unknown
, ready
, creating
and error
. The default value is unknown
.A Read Replica is a live copy of a Database Instance that behaves like an Instance, but that only allows read-only connections. The replica mirrors the data of the primary Database node and any changes made are replicated to the replica asynchronously. Read Replicas allow you to scale your Database Instance for read-heavy database workloads. They can also be used for business intelligence workloads.
A Read Replica can have at most one direct access and one Private Network endpoint. Loadbalancer
endpoints are not available on Read Replicas even if this resource is displayed in the Read Replica response example.
If you want to remove a Read Replica endpoint, you can use delete a Database Instance endpoint API call.
Instance Access Control Lists (ACL) also apply to Read Replica direct access endpoints.
Limitations: There might be replication lags between the primary node and its Read Replica nodes. You can try to reduce this lag with some good practices:
Create a new Read Replica of a Database Instance. You must specify the region
and the instance_id
. You can only create a maximum of 3 Read Replicas per Database Instance.
unknown
, provisioning
, initializing
, ready
, deleting
, error
, locked
and configuring
. The default value is unknown
.Retrieve information about a Database Instance Read Replica. Full details about the Read Replica, like endpoints
, status
and region
are returned in the response.
unknown
, provisioning
, initializing
, ready
, deleting
, error
, locked
and configuring
. The default value is unknown
.Delete a Read Replica of a Database Instance. You must specify the region
and read_replica_id
parameters of the Read Replica you want to delete.
unknown
, provisioning
, initializing
, ready
, deleting
, error
, locked
and configuring
. The default value is unknown
.Create a new endpoint for a Read Replica. Read Replicas can have at most one direct access and one Private Network endpoint.
unknown
, provisioning
, initializing
, ready
, deleting
, error
, locked
and configuring
. The default value is unknown
.When you resync a Read Replica, first it is reset, then its data is resynchronized from the primary node. Your Read Replica remains unavailable during the resync process. The duration of this process is proportional to the size of your Database Instance. The configured endpoints do not change.
unknown
, provisioning
, initializing
, ready
, deleting
, error
, locked
and configuring
. The default value is unknown
.Network Access Control Lists allow you to control incoming network traffic by setting up ACL rules.
List the ACL rules for a given Database Instance. The response is an array of ACL objects, each one representing an ACL that denies, allows or redirects traffic based on certain conditions.
Add an additional ACL rule to a Database Instance.
Replace all the ACL rules of a Database Instance.
Delete one or more ACL rules of a Database Instance.
Advanced Database Instance settings allow you to tune the behavior of your database engines to better fit your needs.
Available settings depend on the database engine and its version. Note that some settings can only be defined upon database engine initialization. These are called init settings. You can find a full list of the settings available in the response body of the list available database engines endpoint.
Each advanced setting entry has a default value that users can override. The deletion of a setting entry will restore the setting to default value. Some of the defaults values can be different from the engine's defaults, as we optimize them to the Scaleway platform.
Add an advanced setting to a Database Instance. You must set the name
and the value
of each setting.
Update an advanced setting for a Database Instance. Settings added upon database engine initalization can only be defined once, and cannot, therefore, be updated.
Delete an advanced setting in a Database Instance. You must specify the names of the settings you want to delete in the request.
Privileges are permissions that can be granted to database users. You can manage user permissions either via the console, the Scaleway APIs or SQL. Managed Database for PostgreSQL and MySQL provides a simplified and unified permission model through the API and the console to make things easier to manage and understand.
Each user has associated permissions that give them access to zero or more logical databases. These include:
List privileges of a user on a database. By default, the details returned in the list are ordered by creation date in ascending order, though this can be modified via the order_by field. You can define additional parameters for your query, such as database_name
and user_name
.
user_name_asc
, user_name_desc
, database_name_asc
and database_name_desc
. The default value is user_name_asc
.Set the privileges of a user on a database. You must define database_name
, user_name
and permission
in the request body.
Users are profiles to which you can attribute database-level permissions. They allow you to define permissions specific to each type of database usage. For example, users with an admin
role can create new databases and users.
List all users of a given Database Instance. By default, the users returned in the list are ordered by creation date in ascending order, though this can be modified via the order_by field.
Create a new user for a Database Instance. You must define the name
, password
and is_admin
parameters.
Update the parameters of a user on a Database Instance. You can update the password
and is_admin
parameters, but you cannot change the name of the user.
Delete a given user on a Database Instance. You must specify, in the endpoint, the region
, instance_id
and name
parameters of the user you want to delete.
Databases can be used to store and manage sets of structured information, or data. The interaction between the user and a database is done using a Database Engine, which provides a structured query language to add, modify or delete information from the database.
List all databases of a given Database Instance. By default, the databases returned in the list are ordered by creation date in ascending order, though this can be modified via the order_by field. You can define additional parameters for your query, such as name
, managed
and owner
.
name_asc
, name_desc
, size_asc
and size_desc
. The default value is name_asc
.Create a new database. You must define the name
parameter in the request.
Delete a given database on a Database Instance. You must specify, in the endpoint, the region
, instance_id
and name
parameters of the database you want to delete.
Two node type ranges are available:
List all available node types. By default, the node types returned in the list are ordered by creation date in ascending order, though this can be modified via the order_by
field.
A snapshot is a consistent, instantaneous copy of the Block Storage volume of your Database Instance at a certain point in time. They are designed to recover your data in case of failure or accidental alterations of the data by a user. They allow you to quickly create a new Instance from a previous state of your database, regardless of the size of the volume. Their limitation is that, unlike backups, snapshots can only be stored in the same location as the original data.
Create a new snapshot of a Database Instance. You must define the name
parameter in the request.
unknown
, creating
, ready
, restoring
, deleting
, error
and locked
. The default value is unknown
.List snapshots. You can include the instance_id
or project_id
in your query to get the list of snapshots for specific Database Instances and/or Projects. By default, the details returned in the list are ordered by creation date in ascending order, though this can be modified via the order_by
field.
created_at_asc
, created_at_desc
, name_asc
, name_desc
, expires_at_asc
and expires_at_desc
. The default value is created_at_asc
.Retrieve information about a given snapshot, specified by its snapshot_id
and region
. Full details about the snapshot, like size and expiration date, are returned in the response.
unknown
, creating
, ready
, restoring
, deleting
, error
and locked
. The default value is unknown
.Update the parameters of a snapshot of a Database Instance. You can update the name
and expires_at
parameters.
unknown
, creating
, ready
, restoring
, deleting
, error
and locked
. The default value is unknown
.Delete a given snapshot of a Database Instance. You must specify, in the endpoint, the region
and snapshot_id
parameters of the snapshot you want to delete.
unknown
, creating
, ready
, restoring
, deleting
, error
and locked
. The default value is unknown
.Restore a snapshot. When you restore a snapshot, a new Instance is created and billed to your account. Note that is possible to select a larger node type for your new Database Instance. However, the Block volume size will be the same as the size of the restored snapshot. All Instance settings will be restored if you chose a node type with the same or more memory size than the initial Instance. Settings will be reset to the default if your node type has less memory.
unknown
, ready
, provisioning
, configuring
, deleting
, error
, autohealing
, locked
, initializing
, disk_full
, backuping
, snapshotting
and restarting
. The default value is unknown
.A point of connection to a Database Instance. The endpoint is associated with an IPv4 address and a port. It contains the information about whether the endpoint is read-write or not. The endpoints always point to the main node of a Database Instance.
All endpoints have TLS enabled. You can use TLS to make your data and your passwords unreadable in transit to anyone but you.
For added security, you can set up ACL rules to restrict access to your endpoint to a set of trusted hosts or networks of your choice.
Load Balancers are used to forward traffic to the right node based on the node state (active/hot standby). The Load Balancers' configuration is set to cut off inactive connections if no TCP traffic is sent within a 6-hour timeframe. We recommend using connection pooling on the application side to renew database connections regularly.
Retrieve information about a Database Instance endpoint. Full details about the endpoint, like ip
, port
, private_network
and load_balancer
specifications are returned in the response.
ip
and hostname
may be set.private_network
, load_balancer
and direct_access
may be set.private_network
, load_balancer
and direct_access
may be set.private_network
, load_balancer
and direct_access
may be set.Delete the endpoint of a Database Instance. You must specify the region
and endpoint_id
parameters of the endpoint you want to delete. Note that might need to update any environment configurations that point to the deleted endpoint.
ip
and hostname
may be set.private_network
, load_balancer
and direct_access
may be set.private_network
, load_balancer
and direct_access
may be set.private_network
, load_balancer
and direct_access
may be set.Create a new endpoint for a Database Instance. You can add load_balancer
and private_network
specifications to the body of the request. Note that this action replaces your current endpoint, which means you might need to update any environment configurations that point to the old endpoint.
ip
and hostname
may be set.private_network
, load_balancer
and direct_access
may be set.private_network
, load_balancer
and direct_access
may be set.private_network
, load_balancer
and direct_access
may be set.