Skip to main content
Skip to main content
Edit this page

Managed Postgres OpenAPI

Beta

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.

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:

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": "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, 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": "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"
    }
  ],
  "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"
}

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.

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.

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
}

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.