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

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.

Resource considerations

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

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

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

Database backups

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

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.

Creating the cluster

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

Request

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

Response

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

{
  "type": "cluster",
  "id": "498ae72f-411f-11eb-9d07-046c59cc737e",
  "metadata": {
    "state": "BUSY",
    "createdDate": "2020-12-10T12:37:50.000Z",
    "createdBy": "[e-mail]",
    "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

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.

Request

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

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

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

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

Response

{
  "type": "collection",
  "id": "clusters",
  "items": [
    {
      "type": "cluster",
      "id": "498ae72f-411f-11eb-9d07-046c59cc737e",
      "metadata": {
        "state": "AVAILABLE",
        "createdDate": "2020-12-10T12:37:50.000Z",
        "createdBy": "[e-mail]",
        "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": {}
}

Note: You cannot configure the port. Your cluster runs in the default port 5432.

Connect to cluster

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

psql -h 192.168.1.100 -d postgres -U dsertionos

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

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

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

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

Initial setup of databases, users, tables etc

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

Database

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

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

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

Roles

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

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

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

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

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

Users

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

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

Also see the docs on how to manage users.

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