Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
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.
Deprecation Notice: Version 11 is currently still supported but will reach end of life on 9 Nov 2023 (see Postgresql documentation). It will soon be removed from IONOS Cloud.
Locations: As of December 2022, DBaaS is offered in all IONOS Cloud Locations.
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.
Note: IONOS Cloud doesn’t allow superuser access for PostgreSQL services. However, most DBA-type actions are still available through other methods.
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:
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.
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
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.
Note: To cite the pgbench docs: "It is very easy to use pgbench to produce completely meaningless numbers". The numbers shown here are only ballpark figures and there are no performance guarantees. The real performance will vary depending on your workload, the IONOS location, and several other factors.
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):
Note: With select * from pg_available_extensions; you will see more available extensions, but many of them can't be enabled or used without superuser rights and thus aren't listed here.
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.
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.
Note: This method of scaling is used to provide high availability. It will not increase performance.
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.
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.
Caution: Data loss might happen if the server crashes and the data has not been replicated yet.
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.
Caution: Turning on non-strict synchronous replication does not guarantee multi node durability of commits under all circumstances. When no suitable standby is available, the primary node will still accept writes, but does not guarantee their replication.
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.
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.
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.
Caution: You cannot enforce a synchronous commit when the cluster is configured to use asynchronous replication. Without a synchronous standby any setting higher than local is equivalent to local, which doesn't wait for replication to complete. Instead, you can configure your cluster to use synchronous replication and choose synchronous_commit=local whenever data loss is acceptable.
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):
| Extension | Enabled | Version | Description |
|---|---|---|---|
Note: With select * from pg_available_extensions; you will see more available extensions, but many of them can't be enabled or used without superuser rights and thus aren't listed here.
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.
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.
Caution: Major changes of the PostgreSQL version are irreversible and can fail. You should read the official migration guide and test major version upgrades with an appropriate development cluster first.
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."
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.
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.
PostgreSQL Backups: A cluster can have multiple backups. They are created
When a cluster is created
When the PostgreSQL version is changed to a higher major version
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.
Warning: When a database is stopped all transactions since the last WAL segment are written to a (partial) WAL file and shipped to the IONOS S3 Object Storage. This also happens when you delete a database. We provide an additional security timeout of 5 minutes to stop and delete the database gracefully. However, under rare circumstances it could happen that this last WAL Segment is not written to the IONOS S3 Object Storage (e.g. due to errors in the communication with the IONOS S3 Object Storage) and these transactions get lost.
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.
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.
Note: WAL records shipping is a continuous process and the backup is continuously catching up with the workload. Should you require that all the data from the old cluster is completely available in the new cluster, stop the workload before recovery.
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.
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
Caution: Major changes of the PostgreSQL version are irreversible and can fail. You should read the official migration guide and test major version upgrades with an appropriate development cluster first.
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.
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.
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).
Caution: The deletion of a LAN with an attached database is forbidden. A special label deleteprotected will be attached to the LAN. If you want to delete the LAN, either attach the database to a different LAN (via PATCH request to update the database) or delete the database.
Alternatively, you can detach a database from the LAN to delete it. The database will remain disconnected.
CPU, RAM, storage, and number of database clusters are counted against quotas. Contact to determine your RAM requirements.
Database performance depends on the storage type. Choose the type that is suitable for your workload.
The are stored alongside the database. The amount of WAL files can grow and shrink depending on your workload. Plan your storage size accordingly.
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.
Note: Having the backup in the same location as your database increases the risk of data loss in case a whole location would experience a disaster. On the other hand chosing a remote location may impact the performance during node recreation.
This request will create a database cluster with two instances of PostgreSQL version 15.
Note: Only contract admins, owners, and users with "Access and manage DBaaS" privilege are allowed to create and manage databases. When a database is created it can be accessed in the specified LAN by using username and password specified during creation.
Note: This is the only opportunity to set the username and password via the API. The API does not provide a way to change the credentials yet. However, you can change them later by using raw SQL.
Note: The sample UUID is 498ae72f-411f-11eb-9d07-046c59cc737e
Your values will differ from those in the sample code. Your response will have different IDs, timestamps etc.
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
Note: The sample UUID is 498ae72f-411f-11eb-9d07-046c59cc737e
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.
To query a single cluster, you will require the id from your "create" response.
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.
Note: You cannot configure the port. Your cluster runs in the default port 5432.
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:
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:
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:
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.
If you want multiple user with the same permissions, you can group them in a role and GRANT the role to the users later.
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:
Users are basically just roles with the LOGIN permission, so everything from above also applies.
Congratulations: You now have a ready to use PostgreSQL cluster!
Prerequisites: Prior to setting up a database, please make sure you are working within a provisioned that contains at least one from which to access the database. The VM you create is counted against the quota allocated in your contract.
Note: Database Manager is available only for contract administrators, owners, and users with Access and manage DBaaS privilege. You can set the privilege via the DCD group privileges.
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.
Note: To know your private IP address/Subnet, you need to:
Create a single server connected to an empty private LAN and check the IP assigned to that NIC in that LAN. The DHCP in that LAN always uses a /24 subnet, so you must reuse the first 3 IP blocks to reach your database.
To prevent a collision with the DHCP IP range, it is recommended to use IP addresses ending between x.x.x.3/24 and x.x.x.10/24 (which are never assigned by DHCP).
If you have disabled DHCP on your private LAN, you must discover the IP address on your own.
8. Select the appropriate Postgres Version. IONOS Database Manager supports versions 11, 12, 13, 14, and 15.
Deprecation Notice: Support for version 11 will soon be removed and should not be used for new clusters.
9. Enter the number of Postgres Instances in the cluster. One Postgres instance always manages the data of exactly one database cluster.
Note: Here, you will have a primary node and one or more standby nodes that run a copy of the active database, so you have n-1 standby instances in the 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.
Note: The credentials will be overwritten if the user already exists in the backup.
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.
| RAM size | max_connections |
|---|---|
| Extension | Enabled | Version | Description |
|---|---|---|---|
| Aspect | Asynchronous | Synchronous |
|---|---|---|
Read the migration guide from Postgres (e.g. ) and make sure your database cluster can be upgraded
As per : "New major versions also typically introduce some user-visible incompatibilities, so application programming changes might be required."
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 . We strive to support new versions as soon as possible.
The datacenter must be provided as a UUID. The easiest way to retrieve the UUID is through the .
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 ().
The following roles are available to grant: cron_admin, pg_monitor, pg_read_all_stats, and pg_stat_scan_tables, see list of .
The official docs have a detailed walkthrough on .
To set the default privileges for new object in the future, see .
Also see the docs on .
Private IP/Subnet: Enter the private IP or subnet using the available .
Storage Type: is set by default.
plpython3u
X
1.0
PL/Python3U untrusted procedural language
pg_stat_statements
X
1.7
track execution statistics of all SQL statements executed
intarray
1.2
functions, operators, and index support for 1-D arrays of integers
pg_trgm
1.4
text similarity measurement and index searching based on trigrams
pg_cron
1.3
Job scheduler for PostgreSQL
set_user
3.0
similar to SET ROLE but with added logging
timescaledb
2.4.2
Enables scalable inserts and complex queries for time-series data
tablefunc
1.0
functions that manipulate whole tables, including crosstab
pg_auth_mon
X
1.1
monitor connection attempts per user
plpgsql
X
1.0
PL/pgSQL procedural language
pg_partman
4.5.1
Extension to manage partitioned tables by time or ID
hypopg
1.1.4
Hypothetical indexes for PostgreSQL
postgres_fdw
X
1.0
foreign-data wrapper for remote PostgreSQL servers
btree_gin
1.3
support for indexing common datatypes in GIN
pg_stat_kcache
X
2.2.0
Kernel statistics gathering
citext
1.6
data type for case-insensitive character strings
pgcrypto
1.3
cryptographic functions
earthdistance
1.1
calculate great-circle distances on the surface of the Earth
postgis
3.2.1
PostGIS geometry and geography spatial types and functions
cube
1.4
data type for multidimensional cubes
2GB
128
3GB
256
4GB
384
5GB
512
6GB
640
7GB
768
8GB
896
> 8GB
1000
plpython3u
X
1.0
PL/Python3U untrusted procedural language
pg_stat_statements
X
1.7
track execution statistics of all SQL statements executed
intarray
1.2
functions, operators, and index support for 1-D arrays of integers
pg_trgm
1.4
text similarity measurement and index searching based on trigrams
pg_cron
1.3
Job scheduler for PostgreSQL
set_user
3.0
similar to SET ROLE but with added logging
timescaledb
2.4.2
Enables scalable inserts and complex queries for time-series data
tablefunc
1.0
functions that manipulate whole tables, including crosstab
pg_auth_mon
X
1.1
monitor connection attempts per user
plpgsql
X
1.0
PL/pgSQL procedural language
pg_partman
4.5.1
Extension to manage partitioned tables by time or ID
hypopg
1.1.4
Hypothetical indexes for PostgreSQL
postgres_fdw
X
1.0
foreign-data wrapper for remote PostgreSQL servers
btree_gin
1.3
support for indexing common datatypes in GIN
pg_stat_kcache
X
2.2.0
Kernel statistics gathering
citext
1.6
data type for case-insensitive character strings
pgcrypto
1.3
cryptographic functions
earthdistance
1.1
calculate great-circle distances on the surface of the Earth
postgis
3.2.1
PostGIS geometry and geography spatial types and functions
cube
1.4
data type for multidimensional cubes
primary failure
A healthy standby will be promoted if the primary node becomes unavailable.
Only standby nodes that contain all confirmed transactions can be promoted.
Standby failure
No effect on primary. Standby catches up once it is back online.
In strict mode at least one standby must be available to accept write requests. In non-strict mode the primary continues as standalone. There is a short delay in transaction processing if the synchronous standby changes.
Consistency model
Strongly consistent (expect for lost data.)
Strongly consistent (expect for lost data.)
Data loss during failover
Non-replicated data is lost.
Not possible.
Data loss during primary storage failure
Non-replicated data is lost.
Non-replicated data is lost in standalone mode.
Latency
Limited by the performance of the primary.
Limited by the performance of the primary, the synchronous standby and the latency between them (usually below 1ms).
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.
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:
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.
Let's create the pod...
... and attach to it.
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 ...
... and follow the network troubleshooting guide.
You can migrate your existing databases over to DBaaS using the pg_dump, pg_restore and psql tools.
To dump a database use the following command:
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.
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.
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.
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.
Note: The use of pg_dumpall is not possible because it requires a superuser role to work correctly. Superuser roles are not obtainable on managed databases.
You can restore a database from a previous backup either in-place or to a different cluster.
Note: Choose the resources carefully for your new database cluster. The operation may fail if there is insufficient disk space or RAM. We recommend at least 4 GB of RAM for the new database, which can be scaled down after the restore operation.
To restore from a backup you will need to provide its ID. You can request a list of all available backups:
You can also list backups belonging to a specific cluster. For this, you need a clusterId.
Our chosen clusterId is: 498ae72f-411f-11eb-9d07-046c59cc737e
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.
Note: To restore a cluster in-place you can only use backups from that cluster. If that backup is from an older Postgres version (after a major version upgrade), only the data is applied. The database will continue running the updated version.
Request
Response
The API will respond with a 202 Accepted status code if the request is successful.
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.
Note: A backup is a continuous process, so if you have any ongoing workload in your current cluster, do not expect the old data to appear instantly. If you wish to avoid a slight delay, you need to stop the workload prior to backing up.
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.
Note: You can use the POST and fromBackup functionality to move a database to another region since the new database cluster doesn't need to be in the same region as the original one.
Request
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.
Note: The sample UUID is 498ae72f-411f-11eb-9d07-046c59cc737e
| Quick Links: | Quick Links: |
|---|---|
With the PATCH request, you can change the name of your database cluster.
DBaaS supports upgrading Postgres to a higher major version in-place. To do so, simply issue a PATCH request containing the target Postgres version:
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.
Caution: Executing in-place upgrades drops objects and extensions from the database that could be incompatible with the new version. If you are unsure whether your application is affected by the changes then try the upgrade on a clone first.
DBaaS supports increasing the storage size of your cluster in-place. To do so, simply issue a PATCH request containing the new storage size:
The resizing happens online without interruptions.
Caution: Decreasing the storage size is not supported with this method.
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):
Caution: This change requires for the underlying nodes to be replaced and therefore will cause one failover.
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):
Caution: Scaling down may cause one or more failovers and interrupt open connections.
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.
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.
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.
Note: When you change the connection to a new LAN, the database will no longer be reachable in the old network almost immediately. However, the new connection will only be established, after your dedicated VMs are updated, which can take a couple of minutes, depending on the number of instances you specified.
In order to remove the connection, you have to specify an empty list in the request body:
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.
The endpoint for fetching logs has four optional query parameters:
| Parameter | Description | Default value | Possible values |
|---|---|---|---|
So if you omit all parameters, you get the latest 100 log lines.
The response will contain the logs separated per instance and look similar to this (of course with different timestamps, log contents etc):
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.
| Quick Links User Management: | Quick Links Database Management: |
|---|
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.
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_.
The endpoint for user management of a postgresql cluster is /users.
A GET request will give you a list of all users. Use the limit and offset parameters to control pagination.
Set the system parameter to true to view system users too. These users are required for administration purposes and cannot be changed or deleted.
A single user can be retrieved by their name using a GET request.
With the POST request, you can create a new user and set the login password.
The created user is returned.
Use a DELETE request to remove a user. System users cannot be deleted.
The response body is empty.
With the PATCH request, you can change the login password.
The updated user is returned. The password is never returned, though.
The endpoint for database management of a postgresql cluster is /databases.
A GET request will give you a list of all databases. Use the limit and offset parameters to control pagination.
A single database can be retrieved by its name using a GET request.
Use a POST request to create a new database. It must specify both the name and the owner.
The created database is returned.
Use a DELETE request to remove a database.
The response body is empty.
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
Include the output of this command if you open a support ticket related to permission problems.
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:
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:
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:
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:
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.
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:
As for now, DBaaS is only offered on Virtual Servers. Cloud Cubes may be used in the future as well.
IONOS DBaaS doesn't provide connection pooling. However, you may use a connection pooler (such as pgbouncer) between your application and the database.
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.
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.
Yes, see for more info.
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 .
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.
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.
To make authenticated requests to the API, you must include a few fields in the request headers. Please find relevant descriptions below:
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:
If you're opening a support ticket, attach the output of the , the output of psql -h $ip -U $user -d postgres and the command showing your problem.
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 .
Follow for more information on how to authenticate and available endpoints.
| Name | Labels | Description |
|---|
Endpoint:
| Header | Required | Type | Description |
|---|
start
Retrieve log lines after this timestamp (format: RCF3339)
30 days ago
between 30 days ago and now (before end)
end
Retrieve log line before this timestamp (format: RFC3339)
now
between 30 days ago and now (after start)
direction
Direction in which the logs are sorted and limited
BACKWARD
BACKWARD or FORWARD
limit
Maximum number of log lines to retrieve. Which log lines are cut depends on direction
100
between 1 and 5000
ionos_dbaas_postgres_connections_count | contract_number, instance, postgres_cluster, role, state | Number of connections per instance and state. The state is one of the following: active, disabled, fastpath function call, idle, idle in transaction, idle in transaction (aborted). |
ionos_dbaas_postgres_cpu_rate5m | contract_number, instance, postgres_cluster, role | The average CPU utilization over the past 5 minutes. |
ionos_dbaas_postgres_disk_io_time_weighted_seconds_rate5m | contract_number, instance, postgres_cluster, role | The rate of disk I/O time, in seconds, over a five-minute period. Provides insight into performance of a disk, as high values may indicate that the disk is being overused or is experiencing performance issues. |
ionos_dbaas_postgres_instance_count | contract_number, instance, postgres_cluster, role | Desired number of instances. The number of currently ready and running instances may be different. ionos_dbaas_postgres_role provides information about running instances split by role. |
ionos_dbaas_postgres_load5 | contract_number, instance, postgres_cluster, role | Linux load average for the last 5 minutes. This metric is represented as a number between 0 and 1 (can be greater than 1 on multicore machines), where 0 indicates that the CPU core is idle and 1 indicates that the CPU core is fully utilized. Higher values may indicate that the system is experiencing performance issues or is approaching capacity. |
ionos_dbaas_postgres_memory_available_bytes | contract_number, instance, postgres_cluster, role | Available memory in bytes. |
ionos_dbaas_postgres_memory_total_bytes | contract_number, instance, postgres_cluster, role | Total memory of the underlying machine in bytes. Some of it is used for our management and monitoring tools and not available to PostgreSQL. During horizontal scaling you might see different values for each instance. |
ionos_dbaas_postgres_role | contract_number, instance, postgres_cluster, role | Current role of the instance. Provides whether an instance is currently "master" or "replica". |
ionos_dbaas_postgres_storage_available_bytes | contract_number, instance, postgres_cluster, role | Free available disk space per instance in bytes. |
ionos_dbaas_postgres_storage_total_bytes | contract_number, instance, postgres_cluster, role | Total disk space per instance in bytes. During horizontal scaling you might see different values for each instance. |
ionos_dbaas_postgres_transactions:rate2m | contract_number, datid, datname, instance, postgres_cluster, role | Per-second average rate of SQL transactions (that have been committed), calculated over the last 2 minutes. |
ionos_dbaas_postgres_user_tables_idx_scan | contract_number, datname, instance, postgres_cluster, relname, role, schemaname | Number of index scans per table/schema. |
ionos_dbaas_postgres_user_tables_seq_scan | contract_number, datname, instance, postgres_cluster, relname, role, schemaname | Number of sequential scans per table/schema. A high number of sequential scans may indicate that an index should be added to improve performance. |
Authorization | yes | string | HTTP Basic authorization. A base64 encoded string of a username and password separated by a colon. |
X-Contract-Number | no | integer | Users with more than one contract may apply this header to indicate the applicable contract. |
Content-Type | yes | string | Set this to |
