Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
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 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.
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.
The datacenter must be provided as a UUID. The easiest way to retrieve the UUID is through the Cloud API.
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:
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.
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.
The official docs have a detailed walkthrough on how to manage databases.
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:
To set the default privileges for new object in the future, see docs on ALTER DEFAULT PRIVILEGES.
Users are basically just roles with the LOGIN permission, so everything from above also applies.
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.
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.
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):
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:
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:
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.
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:
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 ...
| Header | Required | Type | Description |
|---|---|---|---|
Follow for more information on how to authenticate and available endpoints.
| Name | Labels | Description |
|---|
... and follow the .
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
Authorization
yes
string
HTTP Basic authorization. A base64 encoded string of a username and password separated by a colon. username@domain.tld:password
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 application/json.
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. |