Skip to main content
Skip to main content
Edit this page

Managed Postgres OpenAPI

Private preview in ClickHouse Cloud

Use the ClickHouse OpenAPI to programmatically control your Managed Postgres services just like ClickHouse services. 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
}