PostgreSQL + pgvector Docker Setup

This skill helps you maintain a Dockerized PostgreSQL 17 instance (Debian trixie) with the pgvector extension compiled and available by default.

What This Skill Does

Key Files

Instructions

General Principles

1. **Minimal Changes**: Keep modifications scoped to the specific task. Do not introduce unrelated tools, refactors, or unnecessary dependencies.

2. **Consistent Versioning**: Both builder and final stages must use the same PostgreSQL major version and OS tag (`postgres:17-trixie`).

3. **Respect Existing Configuration**: Preserve existing environment variables and defaults (`POSTGRES_DB`, `POSTGRES_USER`, `POSTGRES_PASSWORD`).

4. **Init Semantics**: Scripts in `db/init/` run only on first container initialization. Do not remove or break this behavior.

Dockerfile Guidelines

#### Multi-Stage Build Structure

**Builder Stage:**

**Final Stage:**

- `vector.so` (shared library)

- Extension SQL files (`vector--*.sql`)

- Extension control file (`vector.control`)

**Key Arguments:**

Docker Compose Guidelines

**Configuration:**

**Environment Variables:**

Extension Management

**Default Extensions (`db/init/001_extensions.sql`):**

```sql

CREATE EXTENSION IF NOT EXISTS vector;

```

**Optional Extensions** (include as commented examples):

Version Upgrades

**To Bump PostgreSQL Version:**

1. Update both `FROM` lines in Dockerfile (builder and final stages)

2. Update `ARG PG_MAJOR` to match new version

3. Ensure `postgresql-server-dev-{VERSION}` package name is correct

4. Update base image tag (e.g., `postgres:18-trixie`)

**To Bump pgvector Version:**

1. Update `ARG PGVECTOR_VERSION` in Dockerfile

2. Verify compatibility with current PostgreSQL version

3. Test build and extension creation

What NOT to Do

1. **Do not add application code** — this is infrastructure only

2. **Do not introduce ORMs or database clients** — keep it focused on the database container

3. **Do not add external services** — no Redis, message queues, or other dependencies

4. **Do not change licensing or repository structure** — respect existing project organization

5. **Do not remove init script behavior** — preserve the `db/init/` first-run semantics

Common Tasks

**Adding a New Extension:**

1. Add SQL statement to `db/init/001_extensions.sql`

2. If extension requires compilation, add build steps to Dockerfile builder stage

3. Copy compiled artifacts to final stage if needed

**Optimizing Build:**

1. Ensure builder stage cleans up after compilation

2. Use `--no-install-recommends` with apt-get

3. Remove apt caches: `rm -rf /var/lib/apt/lists/*`

4. Keep final image lean — no build tools

**Debugging:**

1. Check builder stage: `docker build --target builder -t pgvector-builder .`

2. Verify extension files exist in final image: `docker run --rm image-name ls /usr/share/postgresql/17/extension/`

3. Test extension creation: connect to database and run `CREATE EXTENSION vector;`

Example Usage

**Building the Image:**

```bash

docker-compose build

```

**Starting the Database:**

```bash

docker-compose up -d

```

**Verifying pgvector Installation:**

```bash

docker-compose exec db psql -U postgres -c "CREATE EXTENSION IF NOT EXISTS vector; SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';"

```

**Upgrading pgvector:**

```dockerfile

In Dockerfile, change:

ARG PGVECTOR_VERSION=v0.6.0

```

Notes