> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Quickstart for ClickHouse Managed Postgres

> Experience NVMe-powered Postgres performance and add real-time analytics with native ClickHouse integration

This page covers provisioning ClickHouse Managed Postgres, loading data, replicating it to ClickHouse, and querying it, all from the command line with [clickhousectl](https://clickhouse.com/docs/concepts/features/interfaces/cli.md) and `psql`. Apart from the optional `--interactive` login, commands are non-interactive. `clickhousectl` emits JSON with `--json`, and automatically when it detects a coding agent; pass `--format` to `cloud service query` to get plain values.

## Prerequisites

Install clickhousectl:

```bash theme={null}
curl -fsSL https://clickhouse.com/cli | sh
```

The script installs `~/.local/bin/clickhousectl` and a `chctl` alias. Make sure `~/.local/bin` is on your `PATH`.

You also need `psql` (PostgreSQL client tools; on macOS, `brew install libpq`) and `jq`.

Write operations (create, delete) need an [API key](https://clickhouse.com/docs/products/cloud/features/admin-features/api/openapi.md); OAuth login (`clickhousectl cloud auth login` with no flags) is read-only. Log in with your key; `--interactive` prompts for the key and secret, which keeps the secret out of your shell history:

```bash theme={null}
clickhousectl cloud auth login --interactive
```

Or pass the key and secret to `clickhousectl cloud auth login` with `--api-key` and `--api-secret`, or set the `CLICKHOUSE_CLOUD_API_KEY` and `CLICKHOUSE_CLOUD_API_SECRET` environment variables. Login verifies the credentials and prints the identity; `clickhousectl cloud auth whoami` checks it again later. If you belong to more than one organization, add `--org-id <id>` (or `--org-name <name>`) to each command.

## Part 1: Create Postgres and load data

### Create a Postgres service

Create the service and save the response; the password is shown only once:

```bash theme={null}
clickhousectl cloud postgres create \
  --name quickstart-pg \
  --region us-east-1 \
  --size m6gd.large \
  --pg-version 18 \
  --json > pg.json
```

AWS is the default provider. GCP is in Private Preview; to use it, pass `--provider gcp` with a GCP region and size, for example `--region us-central1 --size c4-standard-lssd-4`.

Without access flags, the service accepts connections from any IPv4 or IPv6 address on Postgres (5432) and PgBouncer (6432). To restrict it, pass one `--ip-allow` rule per CIDR and port range, for example `--ip-allow '203.0.113.0/24:5432..6432=office'`. Once you pass a rule, only the listed rules apply. To change rules later, run `clickhousectl cloud postgres update <postgres-id>` with `--add-ip-allow <rule>` or `--remove-ip-allow <rule-id>`; `cloud postgres get` lists the rule IDs.

The response includes the service ID, hostname, and a ready-to-use connection string (trimmed):

```json theme={null}
{
  "connectionString": "postgres://postgres:vV6cfEr2p_-TzkCDrZOx@quickstart-pg-c959cbf0.pgn9sre8vvsp3qnej7awh00y0a.c0.us-east-1.aws.pg.clickhouse.cloud:5432/postgres?sslmode=require&channel_binding=require",
  "createdAt": "2026-10-23T11:13:21Z",
  "haType": "none",
  "hostname": "quickstart-pg-c959cbf0.pgn9sre8vvsp3qnej7awh00y0a.c0.us-east-1.aws.pg.clickhouse.cloud",
  "id": "aa738723-7bcd-86d0-bd5d-23ab91003c05",
  "ipAccessList": [
    {"description": "", "id": "frshr7cvsgke0h42aphabk7zc2", "portRange": "5432", "source": "0.0.0.0/0"},
    {"description": "", "id": "fr5tjdsywqpj4qhhe76dzjv3a6", "portRange": "6432", "source": "0.0.0.0/0"}
  ],
  "isPrimary": true,
  "name": "quickstart-pg",
  "password": "vV6cfEr2p_-TzkCDrZOx",
  "postgresVersion": "18",
  "provider": "aws",
  "region": "us-east-1",
  "size": "m6gd.large",
  "state": "creating",
  "storageSize": 118,
  "tags": [],
  "username": "postgres"
}
```

Extract what the rest of this guide needs:

```bash theme={null}
PG_ID=$(jq -r .id pg.json)
PG_URL="$(jq -r .connectionString pg.json | sed 's/sslmode=require/sslmode=verify-full/')&sslrootcert=pg-ca.pem"
```

Switching `sslmode` to `verify-full` and adding `sslrootcert` makes `psql` verify the server certificate against the CA you download below. With the default `sslmode=require`, `psql` encrypts the connection but doesn't check the server's identity.

If the password is lost, generate a new one with `clickhousectl cloud postgres reset-password "$PG_ID" --generate`.

### Wait for the service to provision

Provisioning takes a few minutes. Poll until the state is `running`:

```bash theme={null}
while [ "$(clickhousectl cloud postgres get "$PG_ID" --json | jq -r .state)" != "running" ]; do
  sleep 15
done
```

### Download the CA certificate

Each ClickHouse Managed Postgres instance has its own CA, which isn't publicly trusted. Save it to a file; `psql` and the ClickPipe in Part 2 both use it to verify TLS:

```bash theme={null}
clickhousectl cloud postgres certs get "$PG_ID" --output pg-ca.pem
```

### Load sample data

Create two tables and insert 1 million events over `psql`:

```bash theme={null}
psql "$PG_URL" <<'SQL'
\timing
CREATE TABLE events (
   event_id SERIAL PRIMARY KEY,
   event_name VARCHAR(255) NOT NULL,
   event_type VARCHAR(100),
   event_timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
   event_data JSONB,
   user_id INT,
   user_ip INET,
   is_active BOOLEAN DEFAULT TRUE,
   created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
   updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
   user_id SERIAL PRIMARY KEY,
   name VARCHAR(100),
   country VARCHAR(50),
   platform VARCHAR(50)
);

INSERT INTO events (event_name, event_type, event_timestamp, event_data, user_id, user_ip)
SELECT
   'Event ' || gs::text AS event_name,
   CASE
       WHEN random() < 0.5 THEN 'click'
       WHEN random() < 0.75 THEN 'view'
       WHEN random() < 0.9 THEN 'purchase'
       WHEN random() < 0.98 THEN 'signup'
       ELSE 'logout'
   END AS event_type,
   NOW() - INTERVAL '1 day' * (gs % 365) AS event_timestamp,
   jsonb_build_object('key', 'value' || gs::text, 'additional_info', 'info_' || (gs % 100)::text) AS event_data,
   GREATEST(1, LEAST(1000, FLOOR(POWER(random(), 2) * 1000) + 1)) AS user_id,
   ('192.168.1.' || ((gs % 254) + 1))::inet AS user_ip
FROM
   generate_series(1, 1000000) gs;

INSERT INTO users (name, country, platform)
SELECT
    first_names[first_idx] || ' ' || last_names[last_idx] AS name,
    CASE
        WHEN random() < 0.25 THEN 'India'
        WHEN random() < 0.5 THEN 'USA'
        WHEN random() < 0.7 THEN 'Germany'
        WHEN random() < 0.85 THEN 'China'
        ELSE 'Other'
    END AS country,
    CASE
        WHEN random() < 0.2 THEN 'iOS'
        WHEN random() < 0.4 THEN 'Android'
        WHEN random() < 0.6 THEN 'Web'
        WHEN random() < 0.75 THEN 'Windows'
        WHEN random() < 0.9 THEN 'MacOS'
        ELSE 'Linux'
    END AS platform
FROM
    generate_series(1, 1000) AS seq
    CROSS JOIN LATERAL (
        SELECT
            array['Alice', 'Bob', 'Charlie', 'Diana', 'Eve', 'Frank', 'Grace', 'Hank', 'Ivy', 'Jack', 'Liam', 'Olivia', 'Noah', 'Emma', 'Sophia', 'Benjamin', 'Isabella', 'Lucas', 'Mia', 'Amelia', 'Aarav', 'Riya', 'Arjun', 'Ananya', 'Wei', 'Li', 'Huan', 'Mei', 'Hans', 'Klaus', 'Greta', 'Sofia'] AS first_names,
            array['Smith', 'Johnson', 'Williams', 'Brown', 'Jones', 'Garcia', 'Miller', 'Davis', 'Martinez', 'Taylor', 'Anderson', 'Thomas', 'Jackson', 'White', 'Harris', 'Martin', 'Thompson', 'Moore', 'Lee', 'Perez', 'Sharma', 'Patel', 'Gupta', 'Reddy', 'Zhang', 'Wang', 'Chen', 'Liu', 'Schmidt', 'Müller', 'Weber', 'Fischer'] AS last_names,
            1 + (seq % 32) AS first_idx,
            1 + ((seq / 32)::int % 32) AS last_idx
    ) AS names;
SQL
```

```text theme={null}
Timing is on.
CREATE TABLE
Time: 86.029 ms
CREATE TABLE
Time: 80.962 ms
INSERT 0 1000000
Time: 7120.357 ms (00:07.120)
INSERT 0 1000
Time: 84.807 ms
```

The 1M-row insert takes about 20 seconds on `m6gd.large` (one of the smallest sizes) thanks to NVMe storage. Verify with a query; row counts vary between runs because the data is generated with `random()`:

```bash theme={null}
psql "$PG_URL" -c "SELECT event_type, COUNT(*) FROM events GROUP BY event_type ORDER BY 2 DESC;"
```

## Part 2: Replicate to ClickHouse

### Create a ClickHouse service

Create a service in the same region and save the response; the password appears only in the create response:

```bash theme={null}
clickhousectl cloud service create \
  --name quickstart-ch \
  --region us-east-1 \
  --json > ch.json

CH_ID=$(jq -r .service.id ch.json)
CH_PASSWORD=$(jq -r .password ch.json)
```

Wait until it's running; the ClickPipe requires a running destination:

```bash theme={null}
while [ "$(clickhousectl cloud service get "$CH_ID" --json | jq -r .state)" != "running" ]; do
  sleep 15
done
```

To use an existing service instead, set `CH_ID` from `clickhousectl cloud service list` and `CH_PASSWORD` to its `default` user password, which the `pg_clickhouse` step needs.

### Replicate the tables to ClickHouse

Create a Postgres CDC ClickPipe on the ClickHouse service, pointing at the ClickHouse Managed Postgres hostname. The pipe copies the existing rows, then keeps ClickHouse in sync with ongoing changes. ClickPipes verifies the source certificate by default, so pass the CA you downloaded in Part 1 with `--ca-certificate`:

```bash theme={null}
PG_HOST=$(jq -r .hostname pg.json)
PG_PASSWORD=$(jq -r .password pg.json)

clickhousectl cloud clickpipe create postgres "$CH_ID" \
  --name quickstart-sync \
  --host "$PG_HOST" \
  --pg-database postgres \
  --username postgres \
  --password "$PG_PASSWORD" \
  --ca-certificate pg-ca.pem \
  --table-mapping public.events:public_events \
  --table-mapping public.users:public_users \
  --json > pipe.json

PIPE_ID=$(jq -r .id pipe.json)
```

Notes:

* The replicated tables land in the `default` database on the ClickHouse service, named by the `--table-mapping` targets. Pass `--destination-database <name>` to use another database
* `--table-mapping` creates `ReplacingMergeTree` tables. Updates and deletes arrive as new row versions, so read the current state with `FINAL` and `WHERE _peerdb_is_deleted = 0`, as the queries below do
* The publication and replication slot are created automatically, with the publication scoped to the mapped tables; pass `--publication-name` to use one you manage yourself
* Use the direct Postgres hostname; replication isn't supported via PgBouncer

### Wait for the pipe to reach Running

The pipe moves through `Provisioning`, `Setup`, and (for larger tables) `Snapshot` before reaching `Running`, which takes about 4 minutes for the first pipe on a service. `Failed` and `InternalError` are terminal:

```bash theme={null}
while :; do
  STATE=$(clickhousectl cloud clickpipe get "$CH_ID" "$PIPE_ID" --json | jq -r .state)
  case "$STATE" in
    Running) break ;;
    Failed|InternalError) echo "ClickPipe entered terminal state: $STATE" >&2; break ;;
  esac
  sleep 15
done
```

### Query the replicated data in ClickHouse

Run SQL against the ClickHouse service directly from the CLI. On the first call, clickhousectl adds your API key to the service's Query API endpoint, creating the endpoint if needed; no new key is created. Pass `--no-auto-enable` to fail instead. `--format TabSeparated` prints the bare value, which the comparison below relies on:

```bash theme={null}
clickhousectl cloud service query "$CH_ID" --format TabSeparated \
  --query "SELECT count() FROM public_events FINAL WHERE _peerdb_is_deleted = 0"
```

The first line goes to stderr, and only appears when clickhousectl changes the endpoint:

```text theme={null}
Binding the authenticated API key to the Query API endpoint for service 'quickstart-ch'...
1000000
```

New writes to Postgres replicate continuously. Insert a row and poll until the count reaches 1,000,001 (typically under a minute):

```bash theme={null}
psql "$PG_URL" -c "INSERT INTO events (event_name, event_type, user_id, user_ip) VALUES ('cdc-test', 'click', 42, '10.0.0.1');"

while [ "$(clickhousectl cloud service query "$CH_ID" --format TabSeparated \
  --query "SELECT count() FROM public_events FINAL WHERE _peerdb_is_deleted = 0")" != "1000001" ]; do
  sleep 10
done
```

### Query ClickHouse from Postgres

The [`pg_clickhouse`](https://clickhouse.com/docs/products/managed-postgres/extensions/pg_clickhouse/introduction) extension lets Postgres act as a unified query layer for both transactional and analytical data. Grab the ClickHouse HTTPS hostname, then set up the extension over `psql`:

```bash theme={null}
CH_HOST=$(clickhousectl cloud service get "$CH_ID" --json \
  | jq -r '.endpoints[] | select(.protocol=="https") | .host')

psql "$PG_URL" <<SQL
CREATE EXTENSION pg_clickhouse;
CREATE SERVER ch FOREIGN DATA WRAPPER clickhouse_fdw
       OPTIONS(driver 'http', host '$CH_HOST', dbname 'default', port '8443');
CREATE USER MAPPING FOR CURRENT_USER SERVER ch
       OPTIONS (user 'default', password '$CH_PASSWORD');
CREATE SCHEMA organization;
IMPORT FOREIGN SCHEMA "default" FROM SERVER ch INTO organization;
SQL
```

The heredoc is unquoted on purpose, so the shell substitutes `$CH_HOST` and `$CH_PASSWORD` before the SQL reaches Postgres. The replicated tables are now visible as foreign tables in the `organization` schema; queries against them execute in ClickHouse.

Measured on `m6gd.large` with this dataset, analytical queries run 6-9x faster through the foreign tables (for example, a 5-aggregation GROUP BY: 176 ms via ClickHouse vs 1,133 ms locally; a JOIN with aggregations: 298 ms vs 2,764 ms).

## Cleanup

Delete the ClickPipe first, then the Postgres service. Deleting a service removes all of its data permanently. A Postgres service can be deleted while it's running:

```bash theme={null}
clickhousectl cloud clickpipe delete "$CH_ID" "$PIPE_ID"
clickhousectl cloud postgres delete "$PG_ID"
```

A running ClickHouse service can't be deleted directly. `--force` stops it, waits for the stop to finish, then deletes it:

```bash theme={null}
clickhousectl cloud service delete "$CH_ID" --force
```
