Managed Postgres OpenAPI

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/managed-postgres/openapi.md)

[Private preview](https://clickhouse.com/cloud/postgres)

Use the [ClickHouse OpenAPI](/docs/cloud/manage/cloud-api) to programmatically
control your Managed Postgres services just like ClickHouse services. Already
familiar with [OpenAPI](https://www.openapis.org "OpenAPI Initiative")? Get your [API keys](/docs/cloud/manage/openapi "Managing API Keys") and jump right to the [Managed
Postgres API reference](https://clickhouse.com/docs/cloud/manage/api/swagger#tag/Postgres "OpenAPI spec for ClickHouse Cloud: Postgres"). Otherwise, follow along for a quick
run-through.

## API Keys[​](#api-keys "Direct link to API Keys")

Using the ClickHouse OpenAPI requires authentication; see [API keys](/docs/cloud/manage/openapi "Managing 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[​](#organization-id "Direct link to 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[​](#crud "Direct link to CRUD")

Let's explore the lifecycle of a Postgres service.

### Create[​](#create "Direct link to Create")

First, create a new one
using the [create API](https://clickhouse.com/docs/cloud/manage/api/swagger#tag/Postgres/operation/postgresServiceCreate "Create new Postgres service"). 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](https://clickhouse.com/docs/cloud/manage/api/swagger#tag/Postgres/operation/postgresServiceCreate "Create new Postgres service") 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[​](#read "Direct link to 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](https://www.postgresql.org/docs/current/app-psql.html "PostgreSQL Docs: psql — PostgreSQL interactive terminal"):

```
$ 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](https://www.postgresql.org/docs/current/app-psql.html "PostgreSQL Docs: psql — PostgreSQL interactive terminal").

### Update[​](#update "Direct link to Update")

The [patch API](https://clickhouse.com/docs/cloud/manage/api/swagger#tag/Postgres/operation/postgresServicePatch "Update a PostgreSQL service") supports updating a subset of the properties of a Managed
Postgres service via [RFC 7396](https://www.rfc-editor.org/rfc/rfc7396 "RFC 7396: JSON Merge Patch") 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[​](#delete "Direct link to Delete")

Use the [delete API](https://clickhouse.com/docs/cloud/manage/api/swagger#tag/Postgres/operation/postgresServiceDelete "Delete a PostgreSQL service") 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
}
```

- [API Keys](#api-keys)- [Organization ID](#organization-id)- [CRUD](#crud)
      - [Create](#create)- [Read](#read)- [Update](#update)- [Delete](#delete)

Was this page helpful?