ClickHouse Managed Postgres OpenAPI

Beta

Use the ClickHouse OpenAPI to programmatically control your ClickHouse 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 ClickHouse 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.

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

Now can use it in your requests, like so:

ORG_ID=myorgid

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

Now you’ve made your first Postgres API request: list API above lists all of the Postgres servers in your organization. The output should be something like:

{
  "result": [
    {
      "id": "ee2fef9f-b443-8ad0-8c9b-724390cdb826",
      "name": "oltp",
      "provider": "aws",
      "region": "eu-west-2",
      "postgresVersion": "18",
      "size": "r6gd.medium",
      "storageSize": 59,
      "haType": "none",
      "tags": [],
      "isPrimary": true,
      "state": "running",
      "createdAt": "2026-05-25T16:42:16+00:00"
    }
  ],
  "requestId": "c128d830-5769-4c82-8235-f79aa69d1ebf",
  "status": 200
}

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

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"
}'

Now use this data to create a new instance; note that it requires the content type header. Save the response: it is the only one that includes the service credentials (the password reset response also returns them when the request does not supply a password):

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

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

{
  "result": {
    "id": "67b4bc12-8582-45d0-8806-fe9b2e5a54e6",
    "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=67b4bc12-8582-45d0-8806-fe9b2e5a54e6
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, minus the credentials, 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 saved from the create response to connect, for example via psql:

$ psql "$(echo "$pg_created" | 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": "67b4bc12-8582-45d0-8806-fe9b2e5a54e6",
  "name": "my postgres",
  "provider": "aws",
  "region": "us-west-2",
  "postgresVersion": "18",
  "size": "r8gd.large",
  "storageSize": 118,
  "haType": "none",
  "tags": [
    {
      "key": "Environment",
      "value": "production"
    }
  ],
  "username": "postgres",
  "hostname": "my-postgres-6d8d2e3e.$PG_ID.c0.us-west-2.aws.pg.clickhouse-dev.com",
  "isPrimary": true,
  "state": "running"
}

The OpenAPI provides additional endpoints to update properties not supported by the patch API. For example, to update the Postgres configuration, use the config API:

curl -s --user "$KEY_ID:$KEY_SECRET" -H 'Content-Type: application/json' \
    "https://api.clickhouse.cloud/v1/organizations/$ORG_ID/postgres/$PG_ID/config" \
    -d '{"pgConfig": {"max_connections": "42"}, "pgBouncerConfig": {}}' | jq

The output will show the updated configuration as well as a message describing the consequences of the change:

{
  "result":{
    "pgConfig": {
      "max_connections": "42"
    },
    "pgBouncerConfig": {},
    "message": "The changes in the following parameters require a database restart to take effect: max_connections. You can restart the database by using the restart endpoint."
  },
  "requestId":"fdec06f2-66f7-45b4-9f82-0c051aba20aa",
  "status": 200
}

Delete

Use the delete API to delete a Postgres 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 ClickHouse 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.

Query insights

The per-statement telemetry behind the Query Insights tab in the cloud console is also available programmatically. Two endpoints expose the slowest query patterns on a service: one lists every pattern ranked by impact, the other returns a single pattern with its recent executions.

List slow query patterns

The slow patterns API returns aggregate metrics for the slowest query patterns observed over a time window. The window is required — pass from_date and to_date as RFC 3339 timestamps:

FROM=2026-05-25T00:00:00Z
TO=2026-05-26T00:00:00Z

curl -s --user "$KEY_ID:$KEY_SECRET" \
    "https://api.clickhouse.cloud/v1/organizations/$ORG_ID/postgres/$PG_ID/slowQueryPatterns?from_date=$FROM&to_date=$TO" \
    | jq

Results default to the costliest patterns first, sorted by total_duration descending. Sort by a different counter with sort_by (for example p99_duration, call_count, or total_wal_bytes) and flip the direction with sort_order. Narrow the set with the db_name, db_user, db_operation, and app filters, and page through it with limit and offset.

Each result is one normalized pattern, with literals stripped out and durations reported in microseconds:

{
  "result": [
    {
      "queryId": "-4748036479882663975",
      "queryText": "SELECT * FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT $2",
      "dbName": "sales",
      "dbUser": "orders_service",
      "dbOperation": "SELECT",
      "app": "orders-api",
      "callCount": 84213,
      "errorCount": 0,
      "totalDurationUs": 1012384556,
      "avgDurationUs": 12021,
      "maxDurationUs": 482915,
      "p50DurationUs": 9874,
      "p95DurationUs": 28431,
      "p99DurationUs": 41200,
      "totalRows": 842130,
      "totalSharedBlksRead": 19284,
      "totalSharedBlksHit": 48217734,
      "totalCpuTimeUs": 938472113,
      "totalWalBytes": 0
    }
  ],
  "requestId": "c128d830-5769-4c82-8235-f79aa69d1ebf",
  "status": 200
}

The queryId is a signed 64-bit hash of the normalized statement, so it’s often negative. Pass it back verbatim — leading - and all — to fetch a single pattern.

Get a slow query pattern

Pass a queryId from the list response to the slow pattern API to get that pattern’s aggregate metrics alongside its most recent individual executions. The db_name, db_user, and db_operation that identify the pattern are required:

QUERY_ID=-4748036479882663975

curl -s --user "$KEY_ID:$KEY_SECRET" \
    "https://api.clickhouse.cloud/v1/organizations/$ORG_ID/postgres/$PG_ID/slowQueryPatterns/$QUERY_ID?db_name=sales&db_user=orders_service&db_operation=SELECT" \
    | jq

The response carries the same aggregate as the list endpoint under aggregate, plus a recentExecutions array. Each execution includes the full per-execution counters — shared and temp block I/O, CPU user and system time, parallel workers, JIT, and WAL — the same counters the detail flyout breaks down in the console:

{
  "result": {
    "aggregate": {
      "queryId": "-4748036479882663975",
      "queryText": "SELECT * FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT $2",
      "dbName": "sales",
      "dbUser": "orders_service",
      "dbOperation": "SELECT",
      "callCount": 84213,
      "avgDurationUs": 12021,
      "p99DurationUs": 41200
    },
    "recentExecutions": [
      {
        "timestamp": "2026-05-25T16:42:09Z",
        "durationUs": 41200,
        "rows": 10,
        "sharedBlksHit": 412,
        "sharedBlksRead": 3,
        "tempBlksWritten": 0,
        "cpuUserTimeUs": 38211,
        "cpuSysTimeUs": 1044,
        "parallelWorkersPlanned": 0,
        "parallelWorkersLaunched": 0,
        "walBytes": 0,
        "serverRole": "primary"
      }
    ]
  },
  "requestId": "a5957990-dbe5-46fd-b5ce-a7f8f79e50fe",
  "status": 200
}

The example trims both objects for brevity; the API returns the complete counter set documented under per-execution counters.

Server logs

The PostgreSQL server logs behind the logs viewer in the cloud console are also available programmatically. The logs API returns individual log entries for a service over a time window. As with query insights, the window is required, so pass from_date and to_date as RFC 3339 timestamps. The range must not exceed 30 days, and to_date must be after from_date:

FROM=2026-05-25T00:00:00Z
TO=2026-05-26T00:00:00Z

curl -s --user "$KEY_ID:$KEY_SECRET" \
    "https://api.clickhouse.cloud/v1/organizations/$ORG_ID/postgres/$PG_ID/logs?from_date=$FROM&to_date=$TO" \
    | jq

Entries come back newest first; flip the direction with sort_order (asc or desc). Filter to a single severity with severity (for example ERROR, WARNING, or LOG), match a case-sensitive substring of the log body with body_contains, and page through the results with limit and offset.

Each entry carries its timestamp, severity, and raw body. The body is always a string: structured log lines are returned JSON-encoded, plain lines verbatim:

{
  "result": [
    {
      "timestamp": "2026-05-25T16:42:09.512844Z",
      "severity": "ERROR",
      "body": "{\"message\":\"deadlock detected\",\"detail\":\"Process 42 waits for ShareLock\"}"
    },
    {
      "timestamp": "2026-05-25T16:41:58.004120Z",
      "severity": "LOG",
      "body": "connection received: host=10.0.0.7 port=54210 user=orders_service database=sales"
    }
  ],
  "limit": 50,
  "offset": 0,
  "requestId": "b0d5c3a1-9f2e-4a7c-8b1d-3e6f0a2c4d55",
  "status": 200
}

The endpoint pages with limit and offset rather than returning a total count; advance offset until a page returns fewer than limit entries.