> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-3a82795f.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Managed Postgres OpenAPI > Control your Managed Postgres services with our OpenAPI export const galaxyOnClick = eventName => () => { try { if (typeof window !== "undefined" && window.galaxy && eventName) { window.galaxy.track(eventName, { interaction: "click" }); } } catch (e) {} }; export const BetaBadge = ({link, galaxyTrack, galaxyEvent}) => { if (link) { return Beta ; } return
Beta feature.  Learn more.
; }; Use the [ClickHouse OpenAPI](/products/cloud/features/admin-features/api) 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][pg-openapi]. 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: ```bash theme={null} 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: ```bash theme={null} 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: ```bash theme={null} 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: ```json theme={null} { "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: ```bash theme={null} 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: ```bash theme={null} curl -s --user "$KEY_ID:$KEY_SECRET" \ "https://api.clickhouse.cloud/v1/organizations/$ORG_ID/postgres/$PG_ID" \ | jq .result.state ``` ```json theme={null} "running" ``` Now you can use the `connectionString` property to connect, for example via [psql]: ```bash theme={null} $ 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: ```bash theme={null} 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: ```json theme={null} { "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. 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. ```bash theme={null} 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.: ```json theme={null} { "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: ```bash theme={null} 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: ```json theme={null} { "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: ```bash theme={null} 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: ```json theme={null} { "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]. [ClickHouse OpenAPI]: /products/cloud/features/admin-features/api "Cloud API" [OpenAPI]: https://www.openapis.org "OpenAPI Initiative" [API keys]: /products/cloud/features/admin-features/api/openapi "Managing API Keys" [Prometheus endpoint]: /products/managed-postgres/monitoring/prometheus "Managed Postgres Prometheus endpoint" [metrics reference]: /products/managed-postgres/monitoring/metrics "Managed Postgres metrics reference" [pg-openapi]: /api-reference/organization/get-list-of-available-organizations#tag/Postgres "OpenAPI spec for ClickHouse Cloud: Postgres" [list API]: /api-reference/organization/get-list-of-available-organizations#tag/Postgres/operation/postgresServiceGetList "Fetch a list of an organization's Postgres services" [create API]: /api-reference/organization/get-list-of-available-organizations#tag/Postgres/operation/postgresServiceCreate "Create new Postgres service" [psql]: https://www.postgresql.org/docs/current/app-psql.html "PostgreSQL Docs: psql — PostgreSQL interactive terminal" [patch API]: /api-reference/organization/get-list-of-available-organizations#tag/Postgres/operation/postgresServicePatch "Update a PostgreSQL service" [RFC 7396]: https://www.rfc-editor.org/rfc/rfc7396 "RFC 7396: JSON Merge Patch" [Postgres configuration]: https://www.postgresql.org/docs/18/runtime-config.html "PostgreSQL Docs: Server Configuration" [config API]: /api-reference/organization/get-list-of-available-organizations#tag/Postgres/operation/postgresServiceSetConfig "Update a Postgres Service configuration" [delete API]: /api-reference/organization/get-list-of-available-organizations#tag/Postgres/operation/postgresServiceDelete "Delete a PostgreSQL service" [Query Insights]: /products/managed-postgres/monitoring/query-insights "Postgres query insights" [detail flyout]: /products/managed-postgres/monitoring/query-insights#detail "Query insights detail flyout" [per-execution counters]: /products/managed-postgres/monitoring/query-insights#counters "Query insights per-execution counters" [slow patterns API]: /api-reference/organization/get-list-of-available-organizations#tag/Postgres/operation/slowQueryPatternsGetList "List Postgres slow query patterns" [slow pattern API]: /api-reference/organization/get-list-of-available-organizations#tag/Postgres/operation/slowQueryPatternGet "Get a Postgres slow query pattern with recent executions"