# Set Up a Database Cluster ### 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). {% hint style="danger" %} **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. {% endhint %} ### Resource considerations CPU, RAM, storage, and number of database clusters are counted against quotas. Contact [**Resource usage**](https://docs.ionos.com/cloud/managed-services/database-as-a-service/postgresql/overview#resource-usage) to determine your RAM requirements. Database performance depends on the storage type. Choose the [**storage**](https://docs.ionos.com/support/general-information/glossary-of-terms#storage) type that is suitable for your workload. The [**WAL files**](https://www.postgresql.org/docs/current/wal-intro.html) 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. {% hint style="info" %} **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. {% endhint %} ### Creating the cluster This request will create a database cluster with two instances of PostgreSQL version 15. {% hint style="info" %} **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. {% endhint %} {% hint style="info" %} **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. {% endhint %} The datacenter must be provided as a UUID. The easiest way to retrieve the UUID is through the [**Cloud API**](https://docs.ionos.com/reference/general-information/core-api-specification). {% hint style="info" %} **Note:** The sample UUID is 498ae72f-411f-11eb-9d07-046c59cc737e {% endhint %} #### Request ```bash 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. ```json { "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 {% hint style="info" %} **Note:** The sample UUID is 498ae72f-411f-11eb-9d07-046c59cc737e {% endhint %} 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. ```bash 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. ```bash curl --include \ --user "clientname@ionos.com:Mb2.r5oHf-0t" \ --header "Content-Type: application/json" \ https://api.ionos.com/databases/postgresql/clusters ``` #### Response ```json { "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": {} } ``` {% hint style="info" %} **Note:** You cannot configure the port. Your cluster runs in the default port 5432. {% endhint %} ### 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: ```bash 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: ```bash 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](https://www.postgresql.org/docs/current/role-attributes.html)). 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](https://www.postgresql.org/docs/current/predefined-roles.html). ### 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 ```sql 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](https://www.postgresql.org/docs/current/managing-databases.html). #### 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. ```sql 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: ```sql 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](https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html). #### Users Users are basically just roles with the LOGIN permission, so everything from above also applies. ```sql CREATE USER user WITH PASSWORD 'some_secret_passwd'; GRANT role TO user; ``` Also see the docs on [how to manage users](https://www.postgresql.org/docs/current/user-manag.html). Congratulations: You now have a ready to use PostgreSQL cluster! --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.ionos.com/cloud/~/revisions/AMiYziCllTKB2y2lmONY/databases/postgresql/api-howtos/create-a-database.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.