A local Postgres setup sounds trivial until you're debugging a schema mismatch between dev and production, or you realize pgvector isn't installed and your migration has already run. The right setup is Docker Compose with an explicit version pin, a seed script, and the extensions you'll use in production baked in from day one. Here's the full setup.
What changed in 2026
- Postgres 16/17 are the current stable branches; Postgres 16 is the safe production default, and 17 is gaining adoption.
- pgvector 0.8+ supports HNSW indexes natively, making local AI/embedding development fast without a separate vector database.
pgvector/pgvector:pg16 is an official Docker image that includes pgvector out of the box — no manual extension install needed.- Neon and Supabase offer Postgres-compatible cloud dev branches, so some teams skip local Docker entirely. Both work well for solo or small-team development.
Docker Compose setup
# docker-compose.yml
services:
postgres:
image: pgvector/pgvector:pg16 # or postgres:16 if you don't need pgvector
container_name: dev_postgres
restart: unless-stopped
environment:
POSTGRES_USER: devuser
POSTGRES_PASSWORD: devpass
POSTGRES_DB: myapp_dev
ports:
- "5432:5432"
volumes:
- postgres_data:/var/lib/postgresql/data
- ./db/init:/docker-entrypoint-initdb.d # SQL files run on first start
volumes:
postgres_data:
Start it:
docker compose up -d postgres
docker compose logs -f postgres # watch for "ready to accept connections"
Stop without losing data:
docker compose stop postgres
Full reset (wipe data):
docker compose down -v
Connection string
# .env
DATABASE_URL=postgresql://devuser:devpass@localhost:5432/myapp_dev
With asyncpg (Python):
DATABASE_URL=postgresql+asyncpg://devuser:devpass@localhost:5432/myapp_dev
With Node (pg / Drizzle / Prisma):
DATABASE_URL=postgresql://devuser:devpass@localhost:5432/myapp_dev
Enable pgvector
If using the pgvector image, enable it in your first migration or init script:
-- db/init/00_extensions.sql
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- for trigram full-text search
CREATE EXTENSION IF NOT EXISTS unaccent; -- helpful for search normalization
Files in docker-entrypoint-initdb.d/ run alphabetically on first container start.
Seed script
// scripts/seed.ts (Node/Drizzle example)
import { db } from '../src/db/client.js';
import { users } from '../src/db/schema.js';
async function seed() {
await db.insert(users).values([
{ email: 'alice@example.com', name: 'Alice' },
{ email: 'bob@example.com', name: 'Bob' },
]).onConflictDoNothing();
console.log('Seeded users');
process.exit(0);
}
seed().catch((e) => { console.error(e); process.exit(1); });
Add to package.json:
"scripts": {
"db:seed": "tsx scripts/seed.ts",
"db:reset": "docker compose down -v && docker compose up -d postgres && sleep 2 && npm run db:migrate && npm run db:seed"
}
GUI tools comparison
| Tool |
Platform |
Best for |
| TablePlus |
macOS/Windows |
Fast, native, paid (~$90) |
| DBeaver |
All |
Free, full-featured, heavier UI |
| pgAdmin 4 |
Web/all |
Official, free, verbose |
| DataGrip |
All |
JetBrains ecosystem, best SQL editor |
| Postico 2 |
macOS |
Minimal, fast, affordable |
For quick queries, psql in the container works fine:
docker exec -it dev_postgres psql -U devuser -d myapp_dev
How to keep dev and prod in sync
- Pin the same Postgres major version in both Docker Compose and your managed DB (RDS, Cloud SQL, Neon).
- Run migrations in CI against a fresh Docker Compose DB to catch schema issues before they hit production.
- Never run manual SQL in dev and skip writing a migration — future you will be confused.
- Use
pg_dump occasionally to snapshot your local schema and compare against production.
Common mistakes
Using latest as the image tag. A docker pull six months later upgrades your Postgres silently. Pin to pgvector/pgvector:pg16.
Forgetting volumes: for data persistence. Without a named volume, every docker compose down wipes your database.
Opening port 5432 on a VM or cloud instance. For dev-only, keep 5432 on 127.0.0.1 only. In Docker, "127.0.0.1:5432:5432" binds only to localhost.
Not running ANALYZE after bulk seed inserts. The query planner uses stale statistics until you run ANALYZE or VACUUM ANALYZE.
Sharing one dev DB across the team. Each developer should have their own local container; shared dev DBs become a coordination problem.
What to skip
- Native Postgres installs on macOS/Linux for new projects — Docker is portable and matches the prod OS.
- Homebrew Postgres unless you specifically need it; version upgrades are manual and messy.
- Port-forwarding a staging DB as your local DB — mutations in dev will corrupt staging data.
FAQ
How do I connect a Prisma app to the local DB?
Set DATABASE_URL in .env, run npx prisma db push for schema sync in dev, or npx prisma migrate dev for migration-tracked changes.
What Postgres version should I use?
Match your production version exactly. If production is RDS Postgres 16, use postgres:16 or pgvector/pgvector:pg16 locally.
How do I run psql inside the container?
docker exec -it dev_postgres psql -U devuser -d myapp_dev
Does this work on Apple Silicon (M-series Macs)?
Yes. The official Postgres and pgvector images publish linux/arm64 variants; Docker Desktop handles the architecture automatically.
Where to go next
See How to write a database migration in 2026, How to add full-text search in 2026, and How to cache with Redis in 2026.