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):

DBaaS for PostgreSQL is fully integrated into the Data Center Designer and has a dedicated API. You may also launch it via automation tools like Terraform and Ansible.

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 PostgreSQL Extensions.

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

Logs: The logs that are generated by a database are stored 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.

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.

Write-Ahead Logs: PostgreSQL uses Write Ahead Logs (WAL) for continuous archiving and point-in-time recovery. These logs are created in addition to the regular logs.

Every change to the database is recorded in the WAL record. WALs are generated along with daily base backups and offer a consistent snapshot of the database as it was at that time. WALs and backups are automatically deleted after 7 days, which is the earliest point in time you can recover from. Please consult PostgreSQL WAL documentation for more information.

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

Connection encryption: All client connections are encrypted using TLS; the default SSL mode is prefer and clients cannot disable it. Server certificates are issued by Let's Encrypt and the root certificate is ISRG Root X1. This needs to be made available to the client for verify-ca and verify-full to function.

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:

curl https://crt.sh/?d=9314791 > ca.crt
export PGSSLROOTCERT=$(pwd)/ca.crt
export PGSSLMODE=verify-full
psql -h pg-abc123.postgresql.de-txl.ionos.com -U dbadmin postgres

Resource usage

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

Connection Limits: The value for max_connections is calculated based on RAM size.

The superuser needs to maintain the state and integrity of the database, which is why the platform reserves 11 connections for internal use: connections for superusers (see superuser_reserved_connections), for replication.

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.

IP Ranges: The following IP ranges cannot be used with our PostgreSQL services:

• 10.208.0.0/12

• 10.233.0.0/18

• 192.168.230.0/24

• 10.233.64.0/18

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:

create extension <EXTENSION> CASCADE;

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

Resource allocation

The database resources allocated as per your user contract are displayed in Resource allocation. The resources refer to the Postgres Clusters, number of CPU cores, RAM, and Storage databases quota:

• 16 CPU Cores

• 32 GB RAM

• 10 database clusters

• 1500 GB Disk Space

• 5 instances via within each cluster

You can view the number of resources that are available and can be used, as well as the number of resources already consumed. Based on the resources available here, you can allocate resources during the creation of a database cluster. For resource allocation, contact IONOS Cloud Support.

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 base backups 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.

SELECT now(); # verify server time
SELECT * FROM pg_stat_archiver; # get information about last archival wal and time

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:

• Read the migration guide from Postgres (e.g. to version 13) 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.

As per PostgreSQL official documentation: "New major versions also typically introduce some user-visible incompatibilities, so application programming changes might be required."

Supported Versions

Starting with version 10, PostgreSQL moved to a yearly release schedule, where each major version is supported for 5 years after initial release. You can find more details at https://www.postgresql.org/support/versioning/. We strive to support new versions as soon as possible.

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.

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

Product Overview

Overview

Quick Links

Developer Tools

Frequently Asked Questions (FAQs)

To get answers to the most commonly encountered questions about PostgreSQL database, see FAQs.

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!

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.

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\""
        }
      ]
    }
  ]
}

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.

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

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

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. to version 13) 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.

As per PostgreSQL official documentation: "New major versions also typically introduce some user-visible incompatibilities, so application programming changes might be required."

Supported Versions

Starting with version 10, PostgreSQL moved to a yearly release schedule, where each major version is supported for 5 years after initial release. You can find more details at https://www.postgresql.org/support/versioning/. We strive to support new versions as soon as possible.

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.

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"
}

Retrieve a single database

A single database can be retrieved by its 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/databases/postgres

Response

{
  "id": "13852d13-dd87-5973-ae2c-12ecc918ee0f",
  "properties": {
    "databasename": "postgres",
    "databaseowner": "postgres"
  },
  "type": "database"
}

Create a database

Use a POST request to create a new database. It must specify both the name and the owner.

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

Response

The created database is returned.

{
  "id":"57a27d14-3966-5f2d-94ea-2a21086e9d76",
  "properties": {
    "databasename": "newdb",
    "databaseowner":"someuser"
  },
  "type":"database"
}

Remove a database

Use a DELETE request to remove a database.

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

Response

The response body is empty.

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": []
  }
}

After creation, you can modify the name, properties, configuration details, and the maintenance period of a PostgreSQL Cluster.

To restore a cluster, follow these steps:

1. Log in to the DCD with your username and password.

2. Go to Menu > Databases > PostgreSQL.

1. Click Edit in the OPTIONS column.

a. Alternatively, you can select a cluster from the list by clicking on its name or by selecting Details in the OPTIONS column.

b. Select Edit in the Cluster details tab.

1. The Edit window displays the details of the cluster. You can edit the following:

• Properties: Modify the name of the cluster.

• Cluster configuration: Update the PostgreSQL version and the number of instances.

• Instance configuration: Modify the number of CPUs utilized, RAM size, or the storage.

• Network configuration: Displays the name of the data center, the associated LAN, and the dedicated private IP address.

• Maintenance period (optional): Displays the stipulated day of the week and the time scheduled for the maintenance window.

• Delete cluster: Select to delete the respective cluster. For more information, see Delete a PostgreSQL Cluster.

After successfully configuring your PostgreSQL clusters, you can modify specific details, clone the cluster configuration, or delete a specific PostgreSQL cluster if it is no longer required.

To modify the values, follow these steps:

1. Log in to DCD with your username and password.

2. Go to Menu > Databases > PostgreSQL. A list of all PostgreSQL clusters is displayed.

View individual Cluster details

You can view the details of a specific cluster. To do so, follow these steps:

1. Log in to DCD with your username and password.

2. Go to Menu > Databases > PostgreSQL. A list of all PostgreSQL clusters is displayed.

3. Select a cluster from the list by clicking on its name. Alternatively, click Details in the OPTIONS column.

With IONOS DBaaS, you may roll back your PostgreSQL clusters to a previous point in time or use it to create a new cluster. This reliable function prevents data loss and downtime in unexpected situations.

For example, consider that certain values were accidentally deleted from your cluster around 11 hours today. The easiest way to recover missing data is to restore a backup before 11 hours.

Restore a Cluster

To restore a cluster, follow these steps:

1. Log in to the DCD with your username and password.

2. Go to Menu > Databases > PostgreSQL.

1. Select a cluster from the list by clicking on its name. Alternatively, click Details in the OPTIONS column.

1. The Cluster details tab displays the necessary information of the chosen cluster. For more information, see View PostgreSQL Cluster Details.

2. Select Backups to view the list of backups. Point In Time Recovery displays the earliest backup available for restoration. Select Restore backup for backup restoration.

   a. In the Restore backup pop-up window, click within the text box to open the Calendar.

   b. Backups are available only for those dates that are available for selection. Select a date and click Ok.

   c. Select the Clock icon to set a time for restoring the appropriate backup from the chosen day. Click Ok to confirm your selection.

   d. Select Restore to proceed with the restoration.

   e. Select Restore to confirm the restoration.

Quick Links

To create a new PostgreSQL cluster by cloning an existing one, follow these steps:

1. Log in to the DCD with your username and password.

2. Go to Menu > Databases > PostgreSQL. A list of all PostgreSQL clusters is displayed.

3. Select a cluster from the list by clicking on its name. Alternatively, click Details in the OPTIONS column.

1. The Cluster details tab displays the necessary information of the chosen cluster. Select Clone.

1. Verify the details of the chosen cluster in the Clone window.

1. Select Save to clone.

To delete a PostgreSQL cluster, follow these steps:

1. Log in to the DCD with your username and password.

2. Go to Menu > Databases > PostgreSQL. A list of all PostgreSQL clusters is displayed.

3. Click in the OPTIONS column and select Delete.

Alternatively, you can also delete a cluster using the following options:

• Select a PostgreSQL cluster by clicking on its name and in the Cluster details tab, select Delete.

• Select a PostgreSQL cluster by clicking on its name and in the Cluster details tab, select Edit. In the Edit window, select Delete cluster.

1. Select Delete in the dialog box to confirm deletion.

Quick Links:

DBaaS API - OpenAPI Specification

Endpoint: https://api.ionos.com/docs/postgresql/v1/

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:

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 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 .

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.

Create a Cluster

To create a Postgres cluster, follow these steps:

1. Log in to the DCD, go to Menu > Databases > PostgreSQL.

2. In the PostgreSQL cluster overview window, click Create cluster to create a new Postgres cluster.

3. Specify the following:

• Provide an appropriate Cluster Name.

• Select 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.

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 cluster from the drop-down list of cluster backups.

• Click the Calendar icon in the Recover target time field to select a date and time:

  • Select the recovery date from the calendar.

  • Select the recovery time using the clock.

5. To configure your Cluster, enter the following details in the Cluster configuration section:

a. Select the appropriate PostgreSQL Version. IONOS Database Manager supports versions 12, 13, 14, and 15.

b. Enter the number of Instances in the cluster. One PostgreSQL instance always manages the data of exactly one database cluster. You can create a maximum of five instances within a cluster.

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

• Asynchronous: 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: 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.

d. 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.

6. Specify the following in the Instance configuration section:

• Number of CPUs (per instance): Select the number of CPU cores using the slider or choose from the available shortcut values.

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

• Storage Type: Select a storage type for your instance. HDD is set by default.

• Storage Size: Enter the storage size value in Gigabytes.

7. In the Network configuration section, provide the following information to connect your cluster with the datacenter:

• Datacenter: Select a datacenter from the available list.

• Datacenter LAN: Select a LAN for your datacenter.

• Private IP: Select a private IP address from the drop-down list. For more information, see Private IP Address Ranges.

8. In the Maintenance period (optional) section, you can set a maintenance day and time using the pre-defined format (hh:mm:ss) or the clock.

• Select a Day from the drop-down list.

9. Provide the following details in the User Creation section:

• Username: Enter a username to provide access to the Postgres Cluster for the respective user.

• Password: Enter a password for the respective user.

10. Click Save to create the Postgres Cluster.