/infra-postgres

ClickHouse Cloud Postgres (beta)

Managed Postgres services in ClickHouse Cloud, controlled with clickhousectl cloud postgres. Follow these steps in order; the day-2 operations table at the end covers ongoing management.

Step 1: Authenticate

Cloud Postgres write operations (create, update, delete, restore, etc.) require API key authentication — OAuth login is read-only. If the user has no ClickHouse Cloud account, point them to https://clickhouse.cloud (or clickhousectl cloud auth signup) first.

Guide the user to create an API key in the ClickHouse Cloud console (Settings → API Keys → Create API Key, Admin role, copy both the Key ID and Secret — the secret is shown once). Then ask them to run the login in a separate terminal so the secret stays out of the chat session:

clickhousectl cloud auth login --api-key <key> --api-secret <secret>

Alternatively, credentials can come from CLICKHOUSE_CLOUD_API_KEY / CLICKHOUSE_CLOUD_API_SECRET env vars (also picked up from a .env file in the current directory).

Verify:

clickhousectl cloud auth status
clickhousectl cloud org list

Step 2: Create a Postgres service

--name, --region, and --size are required:

clickhousectl cloud postgres create \
  --name <service-name> \
  --region us-east-1 \
  --size m7i.2xlarge

Options to surface when relevant:

• --provider <provider> — cloud provider (default: aws)
• --pg-version 18|17 — Postgres major version
• --ha-type none|async|sync — high availability
• --tag key=value — resource tags (repeatable)
• --pg-config-file <path> / --pg-bouncer-config-file <path> — JSON files with initial runtime config

The size is validated server-side; if the user is unsure what sizes or regions are available, check the ClickHouse Cloud console or docs rather than guessing.

Then poll until the service is running (service IDs come from postgres list):

clickhousectl cloud postgres list --json
clickhousectl cloud postgres get <postgres-id> --json

The get output includes the connection endpoint details.

Step 3: Connect

Set a known password (ClickHouse cannot reveal the current one):

clickhousectl cloud postgres reset-password <postgres-id> --generate

--generate prints a random compliant password; store it in .env immediately. (Or pass --password <pw>: minimum 12 characters with upper, lower, and digit.)

For TLS verification, fetch the CA bundle:

clickhousectl cloud postgres certs get <postgres-id> > ca.pem

Connect with psql using the endpoint from postgres get. If psql is not installed locally, reuse the local wrapper's explicit-host mode (runs psql via Docker if needed):

clickhousectl local postgres client --host <endpoint-host> --port <port> -- -U <user> -d <database>

Step 4: Apply the schema

If the user developed locally first (see local.md), apply the same schema files to the cloud service, e.g.:

clickhousectl local postgres client --host <endpoint-host> --port <port> \
  -- -U <user> -d <database> -f schema.sql

Step 5: Runtime configuration (as needed)

clickhousectl cloud postgres config get <postgres-id>          # pgConfig + pgBouncerConfig
clickhousectl cloud postgres config patch <postgres-id> ...    # change selected fields
clickhousectl cloud postgres config replace <postgres-id> ...  # replace entire config

Prefer patch over replace for targeted changes. Check --help on each subcommand for the exact flags.

Day-2 operations

restore creates a new service from the source's backups at the given RFC 3339 timestamp; it does not modify the source. delete, promote, and switchover are disruptive — confirm with the user before running them, and never run delete unless the user explicitly asked to delete that specific service.