/infra-postgres

Local Postgres for development

Local Postgres instances are Docker containers managed by clickhousectl. Follow these steps in order.

Docker must be installed and running — verify first:

docker info >/dev/null 2>&1 && echo ok || echo "Docker is not running"

If Docker is not running, ask the user to start Docker Desktop (or the Docker daemon) before continuing.

Step 1: Start a Postgres instance

clickhousectl local postgres start --json

Defaults: name default, Postgres 18, port 5432, user postgres, database postgres, and a random 24-character password. The image is pulled automatically if missing. If port 5432 is taken, a free port is auto-assigned — always read the actual port and password from the JSON output rather than assuming defaults:

{
  "name": "default",
  "port": 5433,
  "user": "postgres",
  "password": "n2efVm0c4nL5dstCyIFxqTSa",
  "database": "postgres"
}

Useful options:

• --name <name> — named instances let you run several side by side (if default is already running and no name is given, a random name is generated)
• -v, --version <tag> — Postgres image tag: 17 or 18 (e.g. 17, 17-alpine, 18.1). Default: 18
• --port, --user, --password, --database — override defaults
• -e KEY=VALUE — extra container env vars (repeatable)

Data persists across restarts at .clickhouse/servers/<name>-pg<major>/data/ in the project directory. Instances are per-project (keyed on the working directory).

Step 2: Run SQL

clickhousectl local postgres client wraps psql — it looks up the port and credentials of a named instance automatically. If psql is not on the host PATH, it runs psql inside the container instead, so no local Postgres install is required.

Single query:

clickhousectl local postgres client --name default --query "SELECT version()"

Apply a SQL file (e.g. schema or seed data):

clickhousectl local postgres client --name default --queries-file schema.sql

Interactive psql session (only when the user asks for one — it blocks the terminal):

clickhousectl local postgres client --name default

Extra psql arguments pass through after --.

Step 3: Wire up the application

Write connection env vars to .env (or .env.local with --local):

clickhousectl local postgres dotenv --name default

This writes POSTGRES_HOST, POSTGRES_PORT, POSTGRES_USER, POSTGRES_PASSWORD, and POSTGRES_DATABASE, replacing any existing POSTGRES_* vars in place. Make sure .env is gitignored, then have the application read these variables (or compose them into a postgres:// connection string).

Managing local instances

clickhousectl local server list          # lists ClickHouse and Postgres instances together
clickhousectl local postgres stop <name>
clickhousectl local postgres stop-all
clickhousectl local postgres remove <name>   # deletes the data directory too

stop keeps data for a later start; remove is destructive — confirm with the user before removing an instance that may hold data they care about. Use -v <version> with stop/remove to disambiguate when two instances share a name.

Going to production

When the user is ready to move from local development to a managed Postgres service in ClickHouse Cloud, read cloud.md — it covers authentication, creating the service, connecting with TLS, and applying the same schema there.