Managed Postgres OpenAPI

Private preview

Use the ClickHouse OpenAPI to programmatically control your Managed Postgres services just like ClickHouse services. The same API also exposes a Prometheus endpoint for scraping service metrics. Already familiar with OpenAPI? Get your API keys and jump right to the Managed Postgres API reference. Otherwise, follow along for a quick run-through.

API Keys

Using the ClickHouse OpenAPI requires authentication; see API keys for how to create them. Then use them via basic auth credentials like so:

KEY_ID=mykeyid
KEY_SECRET=mykeysecret

curl -s --user "$KEY_ID:$KEY_SECRET" https://api.clickhouse.cloud/v1/organizations | jq

Organization ID

Next you'll need your organization ID.

1. Select your organization name in the lower left corner of the console.
2. Select Organization details.
3. Hit the copy icon to the right of Organization ID to copy it directly to your clipboard.

CRUD

Let's explore the lifecycle of a Postgres service.

Create

First, create a new one using the create API. It requires the following properties in the JSON body of the request:

• name: Name of the new Postgres service
• provider: Name of the cloud provider
• region: Region within the provider's network in which to deploy the service
• size: The VM size
• storageSize: The storage size for the VM

See the create API docs for the possible values for these properties. In addition, let's specify Postgres 18 rather than the default, 17:

create_data='{
  "name": "my postgres",
  "provider": "aws",
  "region": "us-west-2",
  "postgresVersion": "18",
  "size": "r8gd.large",
  "storageSize": 118
}'

Now use this data to create a new instance; note that it requires the content type header:

curl -s --user "$KEY_ID:$KEY_SECRET" -H 'Content-Type: application/json' \
    "https://api.clickhouse.cloud/v1/organizations/$ORG_ID/postgres" \
    -d "$create_data" | jq

On success, it will create a new instance and return information about it, including connection data:

{
  "result": {
    "id": "pg7myrd1j06p3gx4zrm2ze8qz6",
    "name": "my postgres",
    "provider": "aws",
    "region": "us-west-2",
    "postgresVersion": "18",
    "size": "r8gd.large",
    "storageSize": 118,
    "haType": "none",
    "tags": [],
    "connectionString": "postgres://postgres:vV6cfEr2p_-TzkCDrZOx@my-postgres-6d8d2e3e.pg7myrd1j06p3gx4zrm2ze8qz6.c0.us-west-2.aws.pg.clickhouse-dev.com:5432/postgres?channel_binding=require",
    "username": "postgres",
    "password": "vV6cfEr2p_-TzkCDrZOx",
    "hostname": "my-postgres-6d8d2e3e.pg7myrd1j06p3gx4zrm2ze8qz6.c0.us-west-2.aws.pg.clickhouse-dev.com",
    "isPrimary": true,
    "state": "creating"
  },
  "requestId": "a5957990-dbe5-46fd-b5ce-a7f8f79e50fe",
  "status": 200
}

Read

Use the id from the response to fetch the service again:

PG_ID=pg7myrd1j06p3gx4zrm2ze8qz6
curl -s --user "$KEY_ID:$KEY_SECRET" \
    "https://api.clickhouse.cloud/v1/organizations/$ORG_ID/postgres/$PG_ID" \
    | jq

The output will be similar to the JSON returned for creation, but keep an eye on the state; when it changes to running, the server is ready:

curl -s --user "$KEY_ID:$KEY_SECRET" \
    "https://api.clickhouse.cloud/v1/organizations/$ORG_ID/postgres/$PG_ID" \
    | jq .result.state

"running"

Now you can use the connectionString property to connect, for example via psql:

$ psql "$(
    curl -s --user "$KEY_ID:$KEY_SECRET" \
    "https://api.clickhouse.cloud/v1/organizations/$ORG_ID/postgres/$PG_ID" \
    | jq -r .result.connectionString
)"

psql (18.3)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

postgres=# 

Type \q to exit psql.

Update

The patch API supports updating a subset of the properties of a Managed Postgres service via RFC 7396 JSON Merge Patch. Tags may be of particular interest for complex deployments; simply send them alone in the request:

curl -sX PATCH --user "$KEY_ID:$KEY_SECRET" -H 'Content-Type: application/json' \
    "https://api.clickhouse.cloud/v1/organizations/$ORG_ID/postgres/$PG_ID" \
    -d '{"tags": [{"key": "Environment", "value": "production"}]}' \
    | jq .result

The returned data should include the new tags:

{
  "id": "$PG_ID",
  "name": "my postgres",
  "provider": "aws",
  "region": "us-west-2",
  "postgresVersion": "18",
  "size": "r8gd.large",
  "storageSize": 118,
  "haType": "none",
  "tags": [
    {
      "key": "Environment",
      "value": "production"
    }
  ],
  "connectionString": "postgres://postgres:vV6cfEr2p_-TzkCDrZOx@my-postgres-6d8d2e3e.$PG_ID.c0.us-west-2.aws.pg.clickhouse-dev.com:5432/postgres?channel_binding=require",
  "username": "postgres",
  "password": "vV6cfEr2p_-TzkCDrZOx",
  "hostname": "my-postgres-6d8d2e3e.$PG_ID.c0.us-west-2.aws.pg.clickhouse-dev.com",
  "isPrimary": true,
  "state": "running"
}

Delete

Use the delete API to delete a Postgres service.

Note

Deleting a Postgres service completely removes the service and all of its data. Be sure you have a backup or have promoted a replica to primary before deleting a service.

curl -sX DELETE --user "$KEY_ID:$KEY_SECRET" \
    "https://api.clickhouse.cloud/v1/organizations/$ORG_ID/postgres/$PG_ID" \
    | jq

On success, the response will report status code 200, e.g.:

{
  "requestId": "ac9bbffa-e370-410c-8bdd-bd24bf3d7f82",
  "status": 200
}

Monitoring

Two Prometheus-compatible endpoints expose CPU, memory, I/O, connection, and transaction metrics for Managed Postgres services: one returns metrics for every service in the organization, the other for a single service. See the Prometheus endpoint page for setup and the metrics reference for the full list of metrics.