Cluster options

Single-node cluster: A single-node cluster only has one node which is called the primary node. This node accepts customer connections and performs read/write operations. This is a single point of truth as well as a single point of failure.

Multi-node cluster: In addition to the primary node, this cluster contains standby nodes that can be promoted to primary if the current primary fails. The nodes are spread across availability zones. Currently, we use warm standby nodes, which means they don't serve read requests. Hot standby functionality (when the nodes can serve read requests) might be added in the future.

Database scaling

Existing clusters can be scaled in two ways: horizontal and vertical.

Horizontal scaling is defined as configuring the number of instances that run in parallel. The number of nodes can be increased or decreased in a cluster.

Scaling up the number of instances does not cause a disruption. However, decreasing may cause a switch over, if the current primary node is removed.

Vertical scaling refers to configuring the size of the individual instances. This is used if you want to process more data and queries. You can change the number of cores and the size of memory to have the configuration that you need. Each instance is maintained on a dedicated node. In the event of scaling up or down, a new node will be created for each instance.

Once the new node becomes available, the server will switch from the old node to the new node. The old node is then removed. This process is executed sequentially if you have multiple nodes. We will always replace the standby first and then the primary. This means that there is only one switchover.

During the switch, if you are connected to the DB with an application, the connection will be terminated. All ongoing queries will be aborted. Inevitably, there will be some disruption. It is therefore recommended that the scaling is performed outside of peak times.

You can also increase the size of storage. However, it is not possible to reduce the size of the storage, nor can you change the type of storage. Increasing the size is done on-the-fly and causes no disruption.

Replication modes

The synchronization_mode determines how transactions are replicated between multiple nodes before a transaction is confirmed to the client. IONOS DBaaS supports three modes of replication: Asynchronous (default), Synchronous and Strict Synchronous. In either mode the transaction is first committed on the leader and then replicated to the standby node(s).

Asynchronous replication does not wait for the standby before confirming a transaction back to the user. Transactions are confirmed to the client after being written to disk on the primary node. Replication takes place in the background. In asynchronous mode the cluster is allowed to lose some committed (not yet replicated) transactions during a failover to ensure availability.

The benefit of asynchronous replication is the lower latency. The downside is that recent transactions might be lost if standby is promoted to leader. The lag between the leader and standby tends to be a few milliseconds.

Synchronous replication ensures that a transaction is committed to at least one standby before confirming the transaction back to the client. This standby is known as synchronous standby. If the primary node experiences a failure then only a synchronous standby can take over as primary. This ensures that committed transactions are not lost during a failover. If the synchronous standby fails and there is another standby available then the role of the synchronous standby changes to the latter. If no standby is available then the primary can continue in standalone mode. In standalone mode the primary role cannot change until at least one standby has caught up (regained the role of synchronous standby). Latency is generally higher than with asynchronous replication, but no data is lost during a failover.

At any time there will be at most one synchronous standby. If the synchronous standby fails then another healthy standby is automatically selected as the synchronous standby.

Strict synchronous replication is the same as synchronous replication with the exception that standalone mode is not permitted. This mode will prevent PostgreSQL from switching off the synchronous replication on the primary when no synchronous standby candidates are available. If no standby is available, no writes will be accepted anymore, so this mode sacrifices availability for replicated durability.

If replication mode is set to synchronous (either strict or non-strict) then data loss cannot occur during failovers (e.g. node failures). The benefit of strict replication is that data is not lost in case of a storage failure of the primary node and a simultaneous failure of all standby nodes.

Synchronization mode considerations

Please note that synchronization modes can impact DBaaS in several ways:

The performance penalty of synchronous over asynchronous replication depends on the workload. The primary handles transactions the same way in all replications modes, with the exception of COMMIT statements (incl. implicit transactions). When synchronous replication is enabled, the commit can only be confirmed to the client once it is replicated. Thus, there is a constant latency overhead for each transaction, independent of the transaction's size and duration.

Changing the commit guarantees per transaction

By default, the replication mode of the database cluster determines the guarantees of a committed transaction. However, some workloads might have very diverse requirements regarding accepted data loss vs performance. To address this need, commit guarantees can be changed per transaction. See synchronous_commit (PostgreSQL documentation) for details.

Available PostgreSQL extensions

There are several PostgreSQL extensions preinstalled, that you can enable for your cluster. You can enable the extension by logging into your cluster and executing:

create extension <EXTENSION> CASCADE;

The following table shows which extensions are enabled by default and which can be enabled (PostgreSQL version 12):

Failover procedures

Planned failover: During a failure or planned failover, the client must reconnect to the database. A planned failover is signaled to the client by the closing of the TCP connection on the server. The client must also close the connection and reconnect.

In the event of a failure, the connection might not be closed correctly. The new leader will send a gratuitous ARP packet to update the MAC address in the client's ARP table. Open TCP connections will be reset once the client sends a TCP packet. We recommend re-establishing a connection to the database by using an exponential back-off retry with an initial immediate retry.

Uncontrolled disconnection: Since we do not allow read connections to standby nodes, only primary disconnections are possible. However, uncontrolled disconnections can happen during maintenance windows, a cluster change, and during unexpected situations such as loss of storage disk space. Such disconnections are destructive for the ongoing transactions and also clients should reconnect.

If a node is disconnected from the cluster, then a new node will be created and provisioned. Losing a primary node leads to the same situation when a client should reconnect. Losing a replica is not noticeable to the customer.

PostgreSQL upgrades

IONOS Cloud updates and patches your database cluster to achieve high standards of functionality and security. This includes minor patches for PostgreSQL, as well as patches for the underlying OS. We try to make these updates unnoticeable to your operation. However, occasionally, we might have to restart your PostgreSQL instance to allow the changes to take effect. These interruptions will only occur during the maintenance window for your database, which is a weekly four-hour window.

When your cluster only contains one replica you might experience a short down-time during this maintenance window, while your database instance is being updated. In a replicated cluster, we only update standbys, but we might perform a switchover in order to change the leader node.

Considerations: Updates to a new minor version are always backward compatible. Such updates are done during the maintenance window with no additional actions from the user side.

Major Version Upgrades

Prerequisites:

• Read the migration guide from Postgres (e.g. ) and make sure your database cluster can be upgraded

• Test the upgrade on development cluster with similar / the same data (you can create a new database cluster as a clone of your existing cluster)

• Prepare for a downtime during the major version upgrade

• Ensure the database cluster has enough available storage. While the upgrade is space-efficient (i.e. it does not copy the data directory), some temporary data is written to disk.

Before upgrading PostgreSQL major versions, customers should be aware that IONOS Cloud is not responsible for customer data or any utilized postgreSQL functionality. Hence, it is the responsibility of the customer to ensure that the migration to a new PostgreSQL major version does not impact their operations.

Supported Versions

When a major version approaches its end of life (EOL), we will announce the deprecation and removal of the version at least 3 months in advance. About 1 month before the EOL, no new database can be created with the deprecated version (the exact date will be part of the first announcement). When the EOL is reached, not yet upgraded databases will be upgraded in their next maintenance window.

IONOS's Database as a Service (DBaaS) consists of fully managed databases, with high availability, performance, and reliability hosted in IONOS Cloud and integrated with other IONOS Cloud services.

Database Engines

We currently offer the following database engines:

MongoDB

IONOS DBaaS lets you quickly set up and manage database clusters. Using IONOS DBaaS, you can manage MongoDB clusters, along with their scaling, security and creating snapshots for backups. The feature offers the following editions of MongoDB to meet your enterprise-level deployments: Playground, Business, and Enterprise. For more information, see .

PostgreSQL

IONOS DBaaS gives you access to the capabilities of the database engine. Using IONOS DBaaS, you can manage PostgreSQL cluster operations, database scaling, patch your database, create backups, and security.

Resource allocation

In the DCD > Databases, the database resources allocated as per your user contract is displayed in the Resource Allocation. The resources refers to the Postgres Clusters, MongoDB Clusters, Cores, RAM, and Storage databases quota. For each of these resources, this section shows the number of resources you can use and also the count of resources already consumed. Based on the resources avaialble here, you can allocate resources during the creation of a MongoDB cluster. To avail additional resource allocation, contact .

Quick Links:

With IONOS Cloud Database as a Service, you can quickly setup and manage a PostgreSQL database. Leverage our user guides, reference documentation, and FAQs to support your hosting needs.

Product Overview

DCD How-Tos

API How-Tos

API Reference

SDK Reference

Frequently Asked Questions

To view cluster metrics in DCD, select the cluster of interest from the available Databases. The chosen database will open up. In Properties, select the database name next to the Monitor Databases option. The cluster metrics will open up:

It is possible to choose a time frame for metrics and instances of interest.

Quick Links:

DBaaS API - OpenAPI Specification

To make authenticated requests to the API, you must include a few fields in the request headers. Please find relevant descriptions below:

Request parameter headers

Examples

We use curl in our examples, as this tool is available on Windows 10, Linux and macOS. Please refer to our blogpost about curl on Windows if you encounter any problems:

Backup and Recovery

Backups

PostgreSQL Backups: A cluster can have multiple backups. They are created

1. When a cluster is created

2. When the PostgreSQL version is changed to a higher major version

3. When a Point-In-Time-Recovery operation is conducted.

At any time, Postgres only ships to one backup. We use combined with continuous WAL archiving. A base backup is done via pg_basebackup regularly, and then WAL records are continuously added to the backup. Thus, a backup doesn't represent a point in time but a time range. We keep backups for the last 7 days so recovery is possible for up to one week in the past.

Data is added to the backup in chunks of 16MB or after 30 minutes, whichever comes first. Failures and delays in archiving do not prevent writes to the cluster. If you restore from a backup then only the data that is present in the backup will be restored. This means that you may lose up to the last 30 minutes or 16MB of data if all replicas lose their data at the same time.

You can restore from any backup of any PostgreSQL cluster as long as the backup was created with the same or an older PostgreSQL major version.

Backups are stored encrypted in an IONOS S3 Object Storage bucket in the same region your database is in. Databases in regions without IONOS S3 Object Storage will be backed up to eu-central-2.

As an additional security mechanism you can check which data has been backed up before deleting the database. To verify which was the last archived WAL segment and at what time it was written you can connect to the database and get information from the pg_stat_archiver.

The `last_archived_time might be older than 30 minutes (WAL files are created with a specific timeout, see above) which is normal if there is no new data added.

Recovery

We provide Point-in-Time-Recovery (PITR). When recovering from a backup, the user chooses a specific backup and provides a time (optional), so that the new cluster will have all the data from the old cluster up until that time (exclusively). If the time was not provided, the current time will be used.

It is possible to set the recoveryTargetTime to a time in the future. If the end of the backup is reached before the recovery target time is met then the recovery will complete with the latest data available.

Failover and Upgrade

Failover procedures

Planned failover: During a failure or planned failover, the client must reconnect to the database. A planned failover is signaled to the client by the closing of the TCP connection on the server. The client must also close the connection and reconnect.

In the event of a failure, the connection might not be closed correctly. The new leader will send a gratuitous ARP packet to update the MAC address in the client's ARP table. Open TCP connections will be reset once the client sends a TCP packet. We recommend re-establishing a connection to the database by using an exponential back-off retry with an initial immediate retry.

Uncontrolled disconnection: Since we do not allow read connections to standby nodes, only primary disconnections are possible. However, uncontrolled disconnections can happen during maintenance windows, a cluster change, and during unexpected situations such as loss of storage disk space. Such disconnections are destructive for the ongoing transactions and also clients should reconnect.

If a node is disconnected from the cluster, then a new node will be created and provisioned. Losing a primary node leads to the same situation when a client should reconnect. Losing a replica is not noticeable to the customer.

PostgreSQL upgrades

IONOS Cloud updates and patches your database cluster to achieve high standards of functionality and security. This includes minor patches for PostgreSQL, as well as patches for the underlying OS. We try to make these updates unnoticeable to your operation. However, occasionally, we might have to restart your PostgreSQL instance to allow the changes to take effect. These interruptions will only occur during the maintenance window for your database, which is a weekly four-hour window.

When your cluster only contains one replica you might experience a short down-time during this maintenance window, while your database instance is being updated. In a replicated cluster, we only update standbys, but we might perform a switchover in order to change the leader node.

Considerations: Updates to a new minor version are always backward compatible. Such updates are done during the maintenance window with no additional actions from the user side.

Major Version Upgrades

Prerequisites:

• Test the upgrade on development cluster with similar / the same data (you can create a new database cluster as a clone of your existing cluster)

• Prepare for a downtime during the major version upgrade

• Ensure the database cluster has enough available storage. While the upgrade is space-efficient (i.e. it does not copy the data directory), some temporary data is written to disk.

Before upgrading PostgreSQL major versions, customers should be aware that IONOS Cloud is not responsible for customer data or any utilized postgreSQL functionality. Hence, it is the responsibility of the customer to ensure that the migration to a new PostgreSQL major version does not impact their operations.

Supported Versions

When a major version approaches its end of life (EOL), we will announce the deprecation and removal of the version at least 3 months in advance. About 1 month before the EOL, no new database can be created with the deprecated version (the exact date will be part of the first announcement). When the EOL is reached, not yet upgraded databases will be upgraded in their next maintenance window.

Overview

DBaaS for PostgreSQL is fully integrated into the and has a dedicated . You may also launch it via automation tools like and .

Compatibility: DBaaS gives you access to the capabilities of the PostgreSQL database engine. This means that the code, applications, and tools you already use today with your existing databases can be used with DBaaS. IONOS Cloud currently supports PostgreSQL versions 12, 13, 14, and 15.

Locations: As of December 2022, DBaaS is offered in all IONOS Cloud Locations.

Features

• Scalable: Fully managed clusters that can be scaled on demand.

• High availability: Multi-node clusters with automatic node failure handling.

• Security: Communication between clients and the cluster is encrypted using TLS certificates from Let's Encrypt.

• Upgrades: Customer-defined maintenance windows, with minimal disruption due to planned failover (approx. few seconds for multi-node clusters).

• Backup: Base backups are carried out daily, with Point-in-Time recovery for one week.

• Cloning: Customers also have the option to clone clusters via backups.

• Restore: Databases can be restored in place or to a different target cluster.

• Resources: Offered on Enterprise VM, with a dedicated CPU, storage, and RAM. Storage options are SSD or HDD, with SSD now including encryption-at-rest.

• Network: DBaaS supports private LANs.

• Extensions: DBaaS supports several .

Platform Tasks

DBaaS services offered by IONOS Cloud:

Our platform is responsible for all back-end operations required to maintain your database in optimal operational health.

• Database installation via the DCD or the DBaaS API.

• Pre-set database configuration and configuration management options.

• Automation of backups for a period of 7 days.

• Regular patches and upgrades during maintenance.

• Disaster recovery via automated backup.

• Service monitoring: both for the database and the underlying infrastructure.

Customer database administration duties:

Tasks related to the optimal health of the database remain the responsibility of the customer. These include:

• Optimisation.

• Data organisation.

• Creation of indexes.

• Updating statistics.

• Consultation of access plans to optimize queries.

To conserve disk space, log files are rotated according to size. Logs should not consume more than 175 MB of disk storage. The files are continuously monitored and log messages are shipped to a central storage location with a retention policy of 30 days.

Password encryption: Client libraries must support SCRAM-SHA-256 authentication. Make sure to use an up-to-date client library.

Certificates are issued for the DNS name of the cluster which is assigned automatically during creation and will look similar to pg-abc123.postgresql.de-txl.ionos.com. It is available via the IONOS API as the dnsName property of the cluster resource.

Here is how to verify the certificate using the psql command line tool:

Resource Usage

Resource quotas: Each customer contract is allotted a resource quota. The available number of CPUs, RAM, storage, and database clusters is added to the default limitations for a VDC contract.

• 16 CPU Cores

• 32 GB RAM

• 1500 GB Disk Space

• 10 database clusters

• 5 nodes within a cluster

Additionally, a single instance of your database cluster can not exceed 16 cores and 32GB RAM.

Calculating RAM Requirements: The RAM size must be chosen carefully. There is 1 GB of RAM reserved to capture resource reservation for OS system daemons. Additionally, internal services and tools use up to 500 MB of RAM. To choose a suitable RAM size, the following formula must be used.

ram_size = base_consumption + X * work_mem + shared_buffers

• The base_consumption and reservation of internal services is approximately 1500 MB.

• X is the number of parallel connections. The value work_mem is set to 8 MB by default.

• The shared_buffersis set to about 15% of the total RAM.

Calculating Disk Requirements:

The requested disk space is used to store all the data that Postgres is working with, incl. database logs and WAL segments. Each Postgres instance has its storage (of the configured size). The operating system and applications are kept separately (outside of the configured storage) and are managed by IONOS.

If the disk runs full Postgres will reject write requests. Make sure that you order enough margin to keep the Postgres cluster operational. You can monitor the storage utilization in DCD.

WAL segments: In normal operation mode, older WAL files will be deleted once they have been replicated to the other instances and backed up to archive. If either of the two shipments is slow or failing then WAL files will be kept until the replicas and archive catch up again. Account for enough margin, especially for databases with high write load.

Log files: Database log files (175 MB) and auxiliary service log files (~100 MB) are stored on the same disk as the database.

Limitations

CPU: The total upper limit for CPU cores depends on your quota. A single instance cannot exceed 16 cores.

RAM: The total upper limit for RAM depends on your quota. A single instance cannot exceed 32 GB.

Storage: The upper limit for storage size is 2 TB.

Backups: Storing cluster backups in an IONOS S3 Object Storage is limited to the last 7 days.

Performance Considerations

Database instances are placed in the same location as your specified LAN, so network performance should be comparable to other machines in your LAN.

Estimates: A test with pgbench (scaling factor 1000, 20 connections, duration 300 seconds, not showing detailed logs) and a single small instance (2 cores, 3 GB RAM, 20 GB HDD) resulted in around 830 transactions per second (read and write mixed) and 1100 transactions per second (read-only). For a larger instance (4 cores, 8 GB RAM, 600GB Premium SSD) the results were around 3400 (read and write) and 19000 (read-only) transactions per second. The database was initialized using pgbench -i -s 1000 -h <ip> -U <username> <dbname>. For benchmarking the command line used was pgbench -c 20 -T 300 -h <ip> -U <username> <dbname> for the read/write tests, and pgbench -c 20 -T 300 -S -h <ip> -U <username> <dbname> for the read-only tests.

Activate Extensions

Available PostgreSQL extensions

There are several PostgreSQL extensions preinstalled, that you can enable for your cluster. You can enable the extension by logging into your cluster and executing:

The following table shows which extensions are enabled by default and which can be enabled (PostgreSQL version 12):

Creating a Cluster

1. To create a Postgres cluster, go to Menu > Databases.

2. In the Databases tab, click + Add in the Postgres Clusters section to create a new Postgres Cluster.

3. Provide an appropriate Display Name.

4. To create a Postgres Cluster from the available backups directly, you can go to the Create from Backup section and follow these steps:

• Select a Backup from the list of cluster backups in the dropdown.

• Select the Recovery Target Time field. A modal will open up.

  • Select the recovery date from the calendar.

  • Then, select the recovery time using the clock.

5. Choose a Location where your data for the database cluster will be stored. You can select an available datacenter within the cluster's data directory to create your cluster.

6. Select a Backup Location that is explicitly your backup location (region). You can have off-site backups by using a region that is not included in your database region.

7. In the Cluster to Datacenter Connection section, provide the following information:

• Data Center: Select a datacenter from the available list.

• LAN: Select a LAN for your datacenter.

• Once done, click the Add Connection option to establish your cluster to datacenter connection.

8. Select the appropriate Postgres Version. IONOS Database Manager supports versions 11, 12, 13, 14, and 15.

9. Enter the number of Postgres Instances in the cluster. One Postgres instance always manages the data of exactly one database cluster.

10. Select the mode of replication in the Synchronization Mode field; asynchronous mode is selected by default. The following are the available replication modes:

• Asynchronous mode: In asynchronous mode, the primary PostgreSQL instance does not wait for a replica to indicate that it wrote the data. The cluster can lose some committed transactions to ensure availability.

• Synchronous mode: Synchronous replication allows the primary node to be run standalone. The primary PostgreSQL instance will wait for any or all replicas. So, no transactions are lost during failover.

• Strictly Synchronous: It is similar to the synchronous mode but requires two operating nodes.

11. Provide the initial values for the following:

• CPU Cores: Select the number of CPU cores using the slider or choose from the available shortcut values.

• RAM Size: Select the RAM size using the slider or choose from the available shortcut values.

• Storage Size: Enter the size value in Gigabytes.

  The Estimated price will be displayed based on the input. The estimated cost is exclusive, where certain variables like traffic and backup are not considered.

12. Provide the Database User Credentials, such as a suitable username and an associated password.

13. In the Maintenance Window section, you can set a Maintenance time using the pre-defined format (hh:mm:ss) or the clock. Select a Maintenance day from the dropdown list. The maintenance occurs in a 4-hour-long window. So, adjust the time accordingly.

14. Click Save to create the Postgres Cluster.

Your Postgres Cluster is now created.

You can migrate your existing databases over to DBaaS using the pg_dump, pg_restore and psql tools.

Using the SQL script format

To dump a database use the following command:

pg_dump -U <username> -h <host> -p <port> -t <tablename> <databasename> -f dump.sql

The -t <tablename> flag is optional and can be added if you only want to dump a single table.

This command will create a script file containing all instructions to recreate your database. It is the most portable format available. To restore it, simply feed it into psql. The database to restore to has to already exist.

psql -U <username> -h <host> -p <port> -d <databasename> -f dump.sql

### Using the custom format

`pg_dump` can also be used to dump a database in an archived format. Archived formats cannot be restored using `psql`, instead `pg_restore` is used. This has some advantages like smaller filesize and bandwith savings, but also speedups in the restore process by restoring concurrently.

In this guide we will use the "custom" format. It is compressed by default and provides the most flexible restore options.

```bash
pg_dump -U <username> -h <host> -p <port> -F c <databasename> -f dump

The flag -F c is selecting the custom archive format. For more information, see PostgreSQL Documentation.

To restore from a custom format archive you have to use pg_restore. The following command assumes that the database to be restored already exists.

pg_restore -U <username> -h <host> -p <port> -F c -d <databasename> dump

When specifying the -C parameter, pg_restore can be instructed to recreate the database for you. For this to work you will need to specify a database that already exists. This database is used for initially connecting to and creating the new database. In this example we will use the database "postgres", which is the default database that should exist in every PostgreSQL cluster. The name of the database to restore to will be read from the archive.

pg_restore -U <username> -h <host> -p <port> -F c -C -d postgres dump

Large databases can be restored concurrently by adding the -j <number> parameter and specifying the number of jobs to run concurrently.

For more information on pg_restore see the official documentation.

Preparing the network

To set up a database inside an existing datacenter, you should have at least one server in a private LAN.

You need to choose an IP address, under which the database leader should be made available.

There is currently no IP address management for databases. If you use your own subnet, you may use any IP address in that subnet. If you rely on DHCP for your servers, then you must pick an IP address of the subnet that is assigned to you by IONOS.

To find the subnet you can look at the NIC configuration. To prevent a collision with the DHCP IP range, pick an IP between x.x.x.3/24 and x.x.x.10/24 (which are never assigned by DHCP).

Resource considerations

CPU, RAM, storage, and number of database clusters are counted against quotas. Contact Resource usage to determine your RAM requirements.

Database performance depends on the storage type. Choose the storage type that is suitable for your workload.

The WAL files are stored alongside the database. The amount of WAL files can grow and shrink depending on your workload. Plan your storage size accordingly.

Database backups

All database clusters are backed up automatically. You can choose the location where cluster backups are stored by providing the backupLocation parameter as part of the cluster properties during cluster creation. When no backup location is provided it defaults to be the closest available location to your clusters' location. As of now, the backup location can't be changed after creation.

Creating the cluster

This request will create a database cluster with two instances of PostgreSQL version 15.

The datacenter must be provided as a UUID. The easiest way to retrieve the UUID is through the Cloud API.

Request

curl --include \
    --request POST \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
      "metadata": {},
      "properties":
        {
        "postgresVersion": "15",
        "instances": 2,
        "cores": 4,
        "ram": 2048,
        "location": "DE/FRA",
        "storageSize": 20000,
        "storageType": "HDD",
        "displayName": "a good name for a database",
        "synchronizationMode": "ASYNCHRONOUS",
        "credentials": {
          "username": "dsertionos
          "password": "knight-errant"
        },
        "connections": [
          {
            "datacenterId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
            "lanId": "x",
            "cidr": "x.x.x.x/24"
          }
        ]
      }
    }' \
    https://api.ionos.com/databases/postgresql/clusters

Response

Your values will differ from those in the sample code. Your response will have different IDs, timestamps etc.

{
  "type": "cluster",
  "id": "498ae72f-411f-11eb-9d07-046c59cc737e",
  "metadata": {
    "state": "BUSY",
    "createdDate": "2020-12-10T12:37:50.000Z",
    "createdBy": "clientname@ionos.com",
    "createdByUserId": "012342f-411f-1eeb-9d07-046c59cc737e",
  },
  "properties": {
    "displayName": "a good name for a database",
    "dnsName": "pg-3euh45am6idkppu3.postgresql.de-fra.ionos.com",
    "location": "DE/FRA",
    "connections": [
      {
        "datacenterId": "3",
        "lanId": "28",
        "cidr": "192.168.1.100/24"
      }
    ],
    "maintenanceWindow": {
      "time": "15:39:01",
      "dayOfTheWeek": "Friday"
    },
    "cores": 4,
    "ram": 2048,
    "instances": 2,
    "storageSize": 20000,
    "storageType": "HDD",
    "synchronization_mode": "ASYNCHRONOUS"
  }
}

At this point, you have created your first PostgreSQL cluster. The deployment of the database will take 20 to 30 minutes. You can check if the request was correctly executed.

Note that the state will show as BUSY

Querying database status

You may have noticed that the state is BUSY and that the database is not yet reachable. This is because the cloud will create a completely new cluster and needs to provision new nodes for all the requested replicas. This process runs asynchronously in the background and might take up to 30 minutes.

The notification mechanism is not available yet. However, you can poll the API to see when the state switches to AVAILABLE.

Request

To query a single cluster, you will require the id from your "create" response.

curl --include \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e

If you don't know your PostgreSQL cluster ID, you can also list all clusters and look for the one for which to query the status.

curl --include \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    https://api.ionos.com/databases/postgresql/clusters

Response

{
  "type": "collection",
  "id": "clusters",
  "items": [
    {
      "type": "cluster",
      "id": "498ae72f-411f-11eb-9d07-046c59cc737e",
      "metadata": {
        "state": "AVAILABLE",
        "createdDate": "2020-12-10T12:37:50.000Z",
        "createdBy": "david.sertic@ionos.com",
        "createdByUserId": "012342f-411f-1eeb-9d07-046c59cc737e",
      },
      "properties": {
        "displayName": "a good name for a database",
        "dnsName": "pg-3euh45am6idkppu3.postgresql.de-fra.ionos.com",
        "location": "DE/FRA",
      ...
      }
    }
  ],
  "offset": 0,
  "limit": 1,
  "links": {}
}

Connect to cluster

Now that everything is set up and successfully created, you can connect to your PostgreSQL cluster. Initially, the cluster only contains one database, called postgres, to which you can connect. For example, using psql and the credentials that you set in the POST request above you can connect with this:

psql -h 192.168.1.100 -d postgres -U dsertionos

Alternatively, you can also use the DNS Name returned in the response instead of the IP address. This record will also be updated when you change the IP address in the future:

psql -h pg-3euh45am6idkppu3.postgresql.de-fra.ionos.com -d postgres -U dsertionos

This initial user is no superuser, for security reasons and to prevent conflicts with our management tooling. It only has CREATEDB and CREATEROLE, but not SUPERUSER, REPLICATION or BYPASSRLS (row level security) permissions (docs on role attributes).

The following roles are available to grant: cron_admin, pg_monitor, pg_read_all_stats, and pg_stat_scan_tables, see list of predefined roles.

Initial setup of databases, users, tables etc

Creating additional users, roles, databases, schemas, and other objects must be done by you yourself from inside SQL. Since this highly depends on your architecture, just some pointers:

Database

CREATE DATABASE example;
REVOKE ALL ON DATABASE example FROM PUBLIC;

The PUBLIC role is a special role, in the sense that all database users inherit these permissions. This is also important if you want to have a user without write permissions, since by default PUBLIC is allowed to write to the public schema.

The official docs have a detailed walkthrough on how to manage databases.

Roles

If you want multiple user with the same permissions, you can group them in a role and GRANT the role to the users later.

CREATE ROLE role;
GRANT CONNECT ON DATABASE example TO role;
-- to grant all permissions in that database:
GRANT ALL ON DATABASE example TO role;

For improved security you should only grant the required permissions. If you e.g. want to grant permission to a specific table, you also need to grant permissions to the schema:

GRANT USAGE ON SCHEMA example TO role;
GRANT SELECT ON TABLE example TO role;

To set the default privileges for new object in the future, see docs on ALTER DEFAULT PRIVILEGES.

Users

Users are basically just roles with the LOGIN permission, so everything from above also applies.

CREATE USER user WITH PASSWORD 'some_secret_passwd';
GRANT role TO user;

Also see the docs on how to manage users.

Congratulations: You now have a ready to use PostgreSQL cluster!

Permission problems

If you're receiving errors like ERROR: permission denied for table x, check that the permissions and owners are as you expect them.

PostgreSQL does have separate permissions and owners for each object (e.g. database, schema, table). Being the owner of the database only implies permissions to create objects in it, but does not grant any permissions on object in the database which are created by other users. For example, selecting data from a table in the database is permitted only when the user is the owner of the table or has been granted read privileges to it.

To show the owners and access privileges you can use this command. What each letter in access privileges stands for is documented in https://www.postgresql.org/docs/13/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE

psql -h $ip_address -U $user -d $database << EOC
\l
\d
\dn
\dp
EOC

Include the output of this command if you open a support ticket related to permission problems.

Network issues

If you see error messages like psql: error: could not connect to server: ..., you can try to find the specific problem by executing these commands (on the client machine having the problems, assuming Linux):

To show local IP adresses:

ip address

Make sure that the IP address of the database cluster is NOT listed here. Otherwise this means that the IP address of the cluster collides with your local machines IP address. Make sure to select a non-DHCP IP address for the database cluster (between x.x.x.2/24 and x.x.x.10/24).

To list the known network neighbors:

ip neigh

Make sure that the IP address of the database cluster shows up here and is not FAILED. If it is missing: make sure that the database cluster is connected to the correct LAN in the correct datacenter.

Test that the database cluster IP is reachable:

ping $ip -c 5

This should show no package loss and rtt times should be around some milliseconds (may depend on your network setup).

To finally test the connection using the PostgreSQL protocol:

psql -h $ip -U $user -d postgres

Some possible error messages are:

• No route to host: Can't connect on layer 3 (IP). Maybe incorrect LAN connection.

• Connection refused: Can reach the target, but it refuses to answer on this port. Could be that IP address is also used by another machine that has no PostgreSQL running.

• password authentication failed for user "x": The password is incorrect.

If you're opening a support ticket, attach the output of the check-net-config.sh script, the output of psql -h $ip -U $user -d postgres and the command showing your problem.

Issues with backup restore

Under some circumstances, in-place restore might fail. This is because some SQL statements are not transactional (most notably DROP DATABASE). A typical use case for in-place restore arises after the deletion of a database.

If a database is dropped, first, the data is removed from disk and then the database is removed from pg_database. These two changes are not transactional. In this event, you will want to revert this change by restoring to a time before the drop was issued. Internally, Postgres replays all transactions until a transaction commits after the specified recovery target time. At this point all uncommitted transactions are aborted. However, the deletion of the database from disk cannot be inverted. As a result, the database is still listed in pg_database but trying to connect to it results in the following:

postgres=# \c test
FATAL:  database "test" does not exist
DETAIL:  The database subdirectory "base/16421" is missing.

DBaaS will perform some initialization on start-up. At this point the database will go into an error loop. To restore a database to a working state again, you can request another in-place restore with an earlier target time, such that at least one transaction is between recovery target time and the drop statement. The problem was previously discussed in the Postgres mailing list here.

This guide shows you how to connect to a database from your managed Kubernetes cluster.

We assume the following prerequisites:

• A datacenter with id xyz-my-datacenter.

• A private LAN with id 3 using the network 10.1.1.0/24.

• A database connected to LAN 3 with IP 10.1.1.5/24.

• A Kubernetes cluster with id xyz-my-cluster.

• ionosctl

In this guide we use DHCP to assign IPs to node pools. Therefore, it is important that the database is in the same subnet that is used by the DHCP server.

To enable connectivity, you must connect the node pools to the private LAN in which the database is exposed:

ionosctl k8s nodepool create --cluster-id xyz-my-cluster --datacenter-id xyz-my-datacenter --lan-ids 3 --dhcp=true --name=my_nodepool

Wait for the node pool to become available. To test the connectivity let's create a pod that contains the Postgres tool pg_isready. If you have multiple node pools make sure to schedule the pod only the node pools that are attached to the additional LAN.

# pod.yaml
apiVersion: v1
kind: Pod
metadata:
  name: connectivity-test
  labels:
    role: connectivity-test
spec:
  containers:
  - name: postgres
    image: postgres
    stdin: true
    tty: true
    command:
      - "/bin/bash"

Let's create the pod...

kubectl apply -f pod.yaml

... and attach to it.

kubectl attach -it connectivity-test
If you don't see a command prompt, try pressing enter.
root@connectivity-test:/# pg_isready -h 10.1.1.5
10.1.1.5:5432 - accepting connections

If everything works, we should see that the database is accepting connections. If you see connection issues, make sure that the node is properly connected to the LAN. To debug the node start a debugging container ...

kubectl debug node/$(kubectl get po connectivity-test -o jsonpath="{.spec.nodeName}") -it --image=busybox

... and follow the network troubleshooting guide.

You can restore a database from a previous backup either in-place or to a different cluster.

Listing all available backups

To restore from a backup you will need to provide its ID. You can request a list of all available backups:

Request

curl --include \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    https://api.ionos.com/databases/postgresql/clusters/backups

Response

{
  "id": "backups",
  "type": "collection",
  "items": [
    {
      "type": "backup",
      "id": "dcd31531-3ac8-11eb-9feb-046c59cc737e",
      "properties": {
        "clusterId": "498ae72f-411f-11eb-9d07-046c59cc737e",
        "version": "14",
        "earliestRecoveryTargetTime": "2021-12-08T14:02:59Z"
      },
      "metadata": {...}
    },
    {
      "type": "backup",
      "id": "2b0cd7f8-5924-11ec-b621-da289d52ded8",
      "properties": {
        "clusterId": "2c12ad79-5cba-b2fc-b621-da289d52ded8",
        "version": "15",
        "earliestRecoveryTargetTime": "2021-12-09T14:02:59Z"
      },
      "metadata": {...}
    }
  ],
  "offset": 0,
  "limit": 2,
  "_links": {}
}

Listing backups per cluster

You can also list backups belonging to a specific cluster. For this, you need a clusterId.

Request

curl --include \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e/backups

Response

{
  "type": "collection",
  "id": "498ae72f-411f-11eb-9d07-046c59cc737e/backups",
  "items": [
    {
      "type": "backup",
      "id": "dcd31531-3ac8-11eb-9feb-046c59cc737e",
      "metadata": {...},
      "properties": {
        "clusterId": "498ae72f-411f-11eb-9d07-046c59cc737e",
        "version": "14",
        "isActive": true,
        "earliestRecoveryTargetTime": "2020-12-08T20:13:49.000Z"
      }
    }
  ],
  "offset": 0,
  "limit": 1,
  "links": {}
}

Restoring from backup in-place

You can now trigger a restore of the chosen cluster. Your database will not be available during the restore operation.

The recoveryTargetTime is an ISO-8601 timestamp that specifies the point in time up to which data will be restored. It is non-inclusive, meaning the recovery will stop right before this timestamp.

You should choose a backup with the most recent earliestRecoveryTargetTime. However, this timestamp should be strictly less than the desirable recoveryTargetTime. For example suppose you have three backups with earliestRecoveryTargetTime from 1st, 2nd and 3rd of january 2022 at 0:00:00 espectively. If you want to restore to the recoveryTargetTime 2022-01-02T20:00:00Z, you should use chose the backup from 2nd of january.

Request

curl --include \
    --request POST \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
      "backupId": "dcd31531-3ac8-11eb-9feb-046c59cc737e",
      "recoveryTargetTime": "2020-12-10T12:37:50.000Z"
    }' \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e/restore

Response

The API will respond with a 202 Accepted status code if the request is successful.

Restoring to a different target

You can also create a new cluster as a copy from a backup by adding the fromBackup field in your POST request. You can use any backup from any cluster as long as the target cluster has the same or a more recent version of PostgreSQL.

The field takes the same arguments as shown above, backupId and recoveryTargetTime.

If you want a new database to have all the data from the old one (clone database) use a backup with the most recent earliestRecoveryTargetTime and omit recoveryTargetTime from the POST request.

Request

curl --include \
    --request POST \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
      "metadata": {},
      "properties: {
        "postgresVersion": "14",
        "instances": 2,
        "cores": 4,
        "ram": 2048,
        "location": "DE/TXL",
        "storageSize": 20000,
        "storageType": "HDD",
        "displayName": "a good name for a database",
        "synchronizationMode": "ASYNCHRONOUS",
        "credentials": {
          "username": "dsertionos",
          "password": "knight-errant"
        },
        "connections": [
          {
            "datacenterId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
            "lanId": "x",
            "cidr": "x.x.x.x/24",
          }
        ],
        "fromBackup": {
          "backupId": "dcd31531-3ac8-11eb-9feb-046c59cc737e",
          "recoveryTargetTime": "2020-12-23T09:37:50.000Z"
        }
      }
    }' \
    https://api.ionos.com/databases/postgresql/clusters

MongoDB is a widely used NoSQL database system that excels in performance, scalability, and flexibility, making it an excellent choice for managing large volumes of data. MongoDB offers different editions tailored to meet the requirements of enterprise-level deployments, namely MongoDB Business and MongoDB Enterprise editions. You can try out MongoDB for free with the MongoDB Playground edition and further upgrade to Business and Enterprise editions.

IONOS DBaaS offers you a replicated MongoDB setup in minutes.

DBaaS is fully integrated into the Data Center Designer. You may also manage it via automation tools like Terraform and Ansible.

Compatibility:

Locations:

Features

The MongoDB Playground, MongoDB Business, and MongoDB Enterprise editions offer the following key capabilities:

Platform Tasks

DBaaS services offered by IONOS Cloud:

Our platform is responsible for all back-end operations required to maintain your database in optimal operational health. The following services are offered:

• Database management via the DCD or the DBaaS API.

• Configuring default values, for example for data replication and security-related settings.

• Automated backups for 7 days.

• Regular patches and upgrades during the maintenance.

• Disaster recovery via automated backup.

• Service monitoring: both for the database and the underlying infrastructure.

Customer database administration duties:

Tasks related to the optimal health of the database remain the responsibility of the user. These include:

• choosing adequate sizing,

• data organization,

• creation of indexes,

• updating statistics, and

• consultation of access plans to optimize queries.

Terminology

• Cluster: The whole MongoDB cluster is currently equal to the replica set.

• Instance: A single server or replica set member inside a MongoDB cluster.

Cluster options

To guarantee partition tolerance, only odd numbers of cluster members are allowed. For the playground edition you can only have 1 replica. All other editions allow the use of 3 replicas. Soon we will allow more than 3 replicas per cluster.

The secondary instances are automatically highly available and replicate your data between instances. One instance is the primary, which accepts write transactions, and the others are secondary, which can optionally be used for read operations.

Replication

The instances in an IONOS MongoDB cluster are members of the same replica set, so all secondary instances replicate the data from the primary instance.

By default the write concern before acknowledging a write operations is set to "majority" with j: true. The term "majority" means the write operation must be propagated to the majority of instances. In a three-instance cluster, for example, at least two instances must have the operation before the primary acknowledges it. The j option also requires that the change has already been persisted to the on-disk journal of these instances.

If data is not replicated to the majority, it may be lost in the event of a primary instance failure and subsequent rollback.

Changing the commit guarantees per transaction

You can change the write concern for single operations, for example due to performance reasons. For more information, see Write Acknowledgement.

Read preferences

You can determine which instance to use for read operations by setting the read preference on the client side, if your client supports it. For more information, see Read Preference.

If you read from the primary instance, you always get the most up-to-date data. You can spread the load by reading from secondary sources, but you might get stale data. However, you can get consistency guarantees using a Read Concern.

Maintenance window is a weekly four-hour window. All changes that may cause service interruption (like upgrades) will be executed within the maintenance windows. During maintenance window, an uncontrolled disconnections and inability to connect to cluster can happen. Such disconnections are destructive for any ongoing transactions and also clients should reconnect.

Maintenance window configuration

Maintenance window consists of two parts. The first part specifies the day of the week, while the second part specifies the expected time. Here is an example of a maintenance window configuration:

...
"maintenanceWindow": {
      "time": "15:39:01",
      "dayOfTheWeek": "Friday"
    },
 ...

For more information, see Create a Cluster.

Will DBaaS be ever offered on Cloud Cubes?

As for now, DBaaS is only offered on Virtual Servers. Cloud Cubes may be used in the future as well.

Is there a connection pooling option for PosgreSQL?

IONOS DBaaS doesn't provide connection pooling. However, you may use a connection pooler (such as pgbouncer) between your application and the database.

After the connection limit has been reached, will there be an error?

Depending on the library you are using, it should be something like:

failed to create DB connection: addr x.x.x.x:5432: connection refused.

How do we prevent reaching the connection limit?

The best way to manage connections is to have your application maintain a pool of at least 10-20 connections. It is considered bad practice to have a lot of DB connections. However, letting the user configure max_connections themselves in the future is an option.

Can I scale the deployment to increase its connection limit?

Yes, see Connection Limits for more info.

What are the supported backup methods?

We provide an automated backup within our cloud. If you want to backup to somewhere else, you may use a client-side tool, such as pg_dump.

What are the main considerations for latency and performance?

The number of standby nodes (in addition to primary node) doesn’t really matter. If you have one or ten makes no difference. Synchronous modes are slower in write performance due to the increase in latency for communication between a primary node and a standby node.

Why can't I restore to the time I specified?

If you are receiving an error message Parameter out of bounds: The recovery target time is before the newest basebackup., check the earliestRecoveryTargetTime of your backup. Your target time of the restore needs to be after this timestamp. You can use an earlier earliestRecoveryTargetTime backup for your cluster if you have one.

If the earliestRecoveryTargetTime is missing in your backup, the cluster from where you want to restore wasn't able to do a base backup. This can happen, when you e.g. quickly delete a newly created cluster, since the base backup will be triggered up to a minute after the cluster is available.

Backups

MongoDB Backups: A cluster can have multiple snapshots. A snapshot is a copy of the data in the cluster at a certain time and is added during the following cases:

1. When a cluster is created, known as initial sync which usually happens in less than 24 hours.

2. After a restore.

3. Every 24 hours, a base snapshot is taken, and every Sunday, a full snapshot is taken.

Snapshots are retained for the last seven days; hence, recovery is possible for up to a week from the current date. You can restore from any snapshot as long as it was created with the same or older MongoDB patch version.

Snapshots are stored in an IONOS S3 Object Storage bucket in the same region as your database. Databases in regions where IONOS S3 Object Storage is not available is backed up to eu-central-2.

Offsite Backup

MongoDB Enterprise edition supports Offsite Backups, this allows backup data being stored in a location other than the deployed database cluster.

Available locations are de, eu-south-2, eu-central-2.

Request

curl --include \
    --request POST \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
      "metadata": {},
      "properties": {
        "edition": "enterprise",
        "backup": {
          "location": "de"
        },
        ...
    }' \
    https://api.ionos.com/databases/mongodb/clusters

Recovery

Recovery is achieved via the restore jobs. A restore job is responsible to create and catalog a cluster restoration process. A valid snapshot reference is required for a restore job to recover the database. The API exposes available snapshots of a cluster.

There must be no other active restore job for the cluster to create a new one.

Point in time recovery

This feature is available only for enterprise clusters. The restoration of cluster here includes points in time and operations log (oplog) timestamps. A custom snapshot can be created for the exact oplog timestamp that you choose to restore the database cluster is possible with point in time recovery.

For more information on restoring database from backup by using the Cloud API, see Restore a Database.

Get Metrics via Telemetry API

Metrics can be retrieved via the Telemetry API as described below:

Request

curl --get https://dcd.ionos.com/telemetry/api/v1/query_range \
--data-urlencode "query=ionos_dbaas_postgres_memory_available_bytes{postgres_cluster=\"498ae72f-411f-11eb-9d07-046c59cc737e\"}" \
--data-urlencode "start=2022-12-06T12:32:37.076Z" \
--data-urlencode "end=2022-12-06T12:47:37.076Z" \
--data-urlencode "step=60" \
--header 'Authorization: Bearer your_JWT_token'

Response

{
  "status": "success",
  "data": {
    "resultType": "matrix",
    "result": [
      {
        "metric": {
          "__name__": "ionos_dbaas_postgres_memory_available_bytes",
          "cloud_service": "default",
          "contract_number": "123456",
          "instance": "ionos-498ae72f-411f-11eb-9d07-046c59cc737e-4oymiqu-0",
          "postgres_cluster": "498ae72f-411f-11eb-9d07-046c59cc737e",
          "role": "master"
        },
        "values": [
          [
            1670329957.076,
            "1071443968"
          ],
          ...
        ]
      },
      {
        "metric": {
          "__name__": "ionos_dbaas_postgres_memory_available_bytes",
          "cloud_service": "default",
          "contract_number": "123456",
          "instance": "ionos-498ae72f-411f-11eb-9d07-046c59cc737e-4oymiqu-1",
          "postgres_cluster": "498ae72f-411f-11eb-9d07-046c59cc737e",
          "role": "replica"
        },
        "values": [
          [
            1670329957.076,
            "1086152704"
          ],
          ...
        ]
      }
    ]
  }
}

Follow MaaS documentation for more information on how to authenticate and available endpoints.

Metrics Overview

Once the PostgreSQL cluster is up and running, you can customize several attributes. For the first public release, you can alter the displayName attribute. You can also arrange the maintenanceWindow and change network connections.

Renaming the database cluster

With the PATCH request, you can change the name of your database cluster.

curl --include \
    --request PATCH \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
      "metadata": {},
      "properties": {
        "displayName": "an even better name!",
      }
    }' \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e

Response

{
  "type": "cluster",
  "id": "498ae72f-411f-11eb-9d07-046c59cc737e",
  "metadata": {
    "state": "AVAILABLE",
    "createdDate": "2020-12-10T12:37:50.000Z",
    "createdBy": "clientname@ionos.com",
    "createdByUserId": "012342f-411f-1eeb-9d07-046c59cc737e",
    "lastModifiedDate": "2020-12-18T21:37:50.000Z",
    "lastModifiedBy": "clientname@ionos.com",
    "lastModifiedByUserId": "012342f-411f-1eeb-9d07-046c59cc737e"
  },
  "properties": {
  "displayName": "an even better name!",
  "location": "DE/FRA",
  ...
  }
}

Upgrading the cluster version in-place

DBaaS supports upgrading Postgres to a higher major version in-place. To do so, simply issue a PATCH request containing the target Postgres version:

curl --include \
    --request PATCH \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
    --data-binary '{
      "metadata": {},
      "properties": {
        "postgresVersion": "15",
      }
    }' \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e

The upgrade procedure is efficient and should only take a few minutes (even for large databases). The database will be unavailable (potentially multiple times) until the upgrade is complete. Once the upgrade is done, the creation of a new backup is triggered.

Once the upgrade is triggered it cannot be undone. If the upgrade fails or causes unexpected behaviors for the application then the old state can be restored by creating a new database from the previous backup. A in-place restore will only apply the old data and not roll back to the older Postgres version.

Increasing cluster storage size in-place

DBaaS supports increasing the storage size of your cluster in-place. To do so, simply issue a PATCH request containing the new storage size:

curl --include \
    --request PATCH \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
      "metadata": {},
      "properties": {
        "storageSize": 50000,
      }
    }' \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e

The resizing happens online without interruptions.

Scaling a cluster vertically (RAM, CPU)

DBaaS supports increasing and decreasing the size of your database instances. To do so, simply issue a PATCH request containing the new size (you can also specify only one of cores or ram, if you don't want to change both):

curl --include \
    --request PATCH \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
      "metadata": {},
      "properties": {
        "cores": 4,
        "ram": 4096,
      }
    }' \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e

Scaling a cluster horizontally (replicas)

DBaaS supports increasing and decreasing the amount of your database replicas. To do so, simply issue a PATCH request containing the new replica count (between 1 and 5):

curl --include \
    --request PATCH \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
      "metadata": {},
      "properties": {
        "instances": 3,
      }
    }' \
    https://api.ionos.com/cloudapi/databases/postgres/clusters/498ae72f-411f-11eb-9d07-046c59cc737e

Setting maintenance windows

If you do not provide a window during the creation of your database, a random window will be assigned for the database. You can update the window at any time, as shown in the example below.

curl --include \
    --request PATCH \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
      "metadata": {},
      "properties": {
        "maintenanceWindow": {
          "dayOfTheWeek": "Sunday",
          "time": "03:30:00"
        }
      }
    }' \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e

When your cluster only contains one replica you might experience a short down-time during this maintenance window, while your database instance is being updated. In a replicated cluster, we only update standbys, but we might perform a switchover, in order to change the leader node.

Changing the connection

After creating your database you can change the connection to your private LAN or temporarily remove it completely. You can change it to either be connected to a different LAN, or simply update the IP. However, you always have to include all properties of the connections list for the request, even if you only want to update the database IP address. The newly provided LAN has to be in the same location as the database cluster. Updating the IP address also updates the record of the DNS name of the database.

curl --include \
    --request PATCH \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
      "properties": {
        "connections": [
          {
            "datacenterId": "b4f86015-9918-443d-be14-aa2eb7529f40",
            "lanId": "2",
            "cidr": "192.168.1.100/24"
          }
        ]
      }
    }' \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e

In order to remove the connection, you have to specify an empty list in the request body:

{
  "properties": {
    "connections": []
  }
}

The logs that are generated by a database are stored temporarily on the same disk as the database. We provide logs for connections, disconnections, waiting for locks, DDL statements, any statement that ran for at least 500 ms, and any statement that caused an error (see postgreSQL documentation). Currently, we do not provide an option to change this configuration.

In order to conserve disk space, log files are rotated according to size. Logs should not consume more than 175 MB of disk storage. The files are continuously monitored and log messages are shipped to a central storage location with a retention policy of 30 days.

By using your cluster ID, you can fetch the logs for that cluster via our API.

Requesting logs

The endpoint for fetching logs has four optional query parameters:

So if you omit all parameters, you get the latest 100 log lines.

curl --include \
    --request GET \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e/logs?start=2022-01-05T12:00:00Z&end=2022-01-05T13:00:00Z&direction=FORWARD&limit=2

Response

The response will contain the logs separated per instance and look similar to this (of course with different timestamps, log contents etc):

{
  "instances": [
    {
      "name": "ionos-498ae72f-411f-11eb-9d07-046c59cc737e-by6qu3m-1",
      "messages": [
        {
          "time": "2022-01-05T12:00:29.793Z",
          "message": "2022-01-05 12:00:29.793 UTC,,,107077,\"[local]\",61d5885d.1a245,1,\"\",2022-01-05 12:00:29 UTC,,0,LOG,00000,\"connection received: host=[local]\",,,,,,,,,\"\",\"not initialized\""
        },
        {
          "time": "2022-01-05T12:00:29.794Z",
          "message": "2022-01-05 12:00:29.794 UTC,\"postgres\",\"test\",107078,\"[local]\",61d5885d.1a246,2,\"authentication\",2022-01-05 12:00:29 UTC,9/73319,0,LOG,00000,\"connection authorized: user=postgres database=test application_name=pgq ticker\",,,,,,,,,\"\",\"client backend\""
        }
      ]
    }
  ]
}

Users (i.e. roles with LOGIN privileges) and databases can be created using the documented SQL commands. The API provides an alternative way to manage these objects.

Common attributes

Each response from the API will include some standard attributes for metadata and pagination (for collections) which follow the IONOS API standards. Most of these will be omitted from the response examples for brevity.

Type, ID and Pagination in collections

{
  "_links": {
    "next": "https://link-to-next-collection-page",
    "previous": "https://link-to-previous-collection-page",
    "self": "https://link-to-this-collection-page"
  },
  "href": "http://link-to-this-collection-page",
  "id": "UUID-for-this-collection-page",
  "items": [
    ...
  ],
  "limit": 100,
  "offset": 0,
  "type": "collection"
}

Type, ID and Metadata for resources

{
  "href": "https://link-to-this-resource-page",
  "id": "UUID-for-this-resource-page",
  "metadata": {
    "createdBy": "URN-of-creator",
    "createdByUserId": "UUID-of-creator",
    "createdDate": "creation-timestamp",
    "lastModifiedBy": "URN-of-actor",
    "latModifiedByUserId": "UUID-of-actor",
    "lastModifiedDate": "modification-timestamp",
    "resourceURN": "URN-for-this-resource"
  },
  "properties": {
    ...
  },
  "type": "user-or-database"
}

If a resource is:

• not created via the API, its createdBy field ends with _unmanaged_.

• a read-only system resource, its createdBy field ends with _system_.

Managing users

The endpoint for user management of a postgresql cluster is /users.

List all users

A GET request will give you a list of all users. Use the limit and offset parameters to control pagination.

curl --request GET \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e/users

Response

{
  "id": "a580ee24-7cf1-509c-8a40-d716f1e2f187",
  "items": [
    {
      "id": "b35402eb-bd7b-512d-92c9-9726db47c101",
      "properties": {
        "system": false,
        "username": "someuser"
      },
      "type": "user"
    }
  ],
  "limit": 100,
  "offset": 0,
  "type": "collection"
}

List system users

Set the system parameter to true to view system users too. These users are required for administration purposes and cannot be changed or deleted.

curl --request GET \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e/users?system=true

Response

{
  "id": "c90a5396-1a43-538b-a4c8-564cd0eafc07",
  "items": [
    {
      "id": "6e209957-a468-562e-9867-2a69069044c3",
      "properties": {
        "system": true,
        "username": "ionos_cloud_admin"
      },
      "type": "user"
    },
    {
      "id": "27d4069e-4df9-56cf-8114-1a846589c73a",
      "properties": {
        "system": true,
        "username": "postgres"
      },
      "type": "user"
    },
    {
      "id": "b35402eb-bd7b-512d-92c9-9726db47c101",
      "properties": {
        "system": false,
        "username": "someuser"
      },
      "type": "user"
    },
    {
      "id": "e1ca756d-957e-5e5b-9089-56943078f98c",
      "properties": {
        "system": true,
        "username": "standby"
      },
      "type": "user"
    }
  ],
  "limit": 100,
  "offset": 0,
  "type": "collection"
}

Retrieve a single user

A single user can be retrieved by their name using a GET request.

curl --request GET \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e/users/someuser

Response

{
  "id": "b35402eb-bd7b-512d-92c9-9726db47c101",
  "properties": {
    "system": false,
    "username": "someuser"
  },
  "type": "user"
}

Create a user

With the POST request, you can create a new user and set the login password.

curl --request POST \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
        "properties":{
            "username": "newuser",
            "password": "pwMin10Chars"
        }
    }' \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e/users

Response

The created user is returned.

{
  "id": "b35402eb-bd7b-512d-92c9-9726db47c101",
  "properties": {
    "system": false,
    "username": "newuser"
  },
  "type": "user"
}

Remove a user

Use a DELETE request to remove a user. System users cannot be deleted.

curl --request DELETE \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e/users/someuser

Response

The response body is empty.

Change the login password

With the PATCH request, you can change the login password.

curl --request PATCH \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    --header "Content-Type: application/json" \
    --data-binary '{
        "properties":{
            "password": "newMin10CharPw"
        }
    }' \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e/users/someuser

Response

The updated user is returned. The password is never returned, though.

{
  "id": "b35402eb-bd7b-512d-92c9-9726db47c101",
  "properties": {
    "system": false,
    "username": "someuser"
  },
  "type": "user"
}

Managing databases

The endpoint for database management of a postgresql cluster is /databases.

List all databases

A GET request will give you a list of all databases. Use the limit and offset parameters to control pagination.

curl --request GET \
    --user "clientname@ionos.com:Mb2.r5oHf-0t" \
    https://api.ionos.com/databases/postgresql/clusters/498ae72f-411f-11eb-9d07-046c59cc737e/databases

Response

{
  "id": "68b29b09-20e3-5fbc-94bb-b193a2ef0d28",
  "items": [
    {
      "id": "13852d13-dd87-5973-ae2c-12ecc918ee0f",
      "properties": {
        "databasename": "postgres",
        "databaseowner": "postgres"
      },
      "type": "database"
    },
    {
      "id": "d3d34784-1d4c-505f-a4ec-e8f593098ea6",
      "properties": {
        "databasename": "template0",
        "databaseowner": "postgres"
      },
      "type": "database"
    },
    {
      "id": "44e6b542-3f75-5882-8a99-53ee43ac1d97",
      "properties": {
        "databasename": "template1",
        "databaseowner": "postgres"
      },
      "type": "database"
    }
  ],
  "limit": 100,
  "offset": 0,
  "type": "collection"
}