Skip to content

README.md

Latest commit

 

History

History
160 lines (120 loc) · 6.51 KB

README.md

File metadata and controls

160 lines (120 loc) · 6.51 KB

🐘 PostgreSQL FDW Dev

A production-shaped, laptop-friendly PostgreSQL development environment. Foreign Data Wrappers + vector search + scheduling + partitioning + auditing — all pre-installed, tuned to coexist peacefully with Elasticsearch, Redis and NestJS.

CI PostgreSQL Docker License: MIT PRs Welcome

✨ What you get

  • PostgreSQL 17 on the official Debian image, fully reproducible.
  • 15+ extensions ready to use the moment the container is up — including postgres_fdw, mysql_fdw, tds_fdw, file_fdw, pgvector, pg_cron, pg_partman, pg_stat_statements, pgaudit, pg_repack, hypopg, pg_hint_plan, orafce, and the usual uuid-ossp/pgcrypto/pg_trgm toolbox.
  • Laptop-safe by design — hard Docker memory & CPU caps so Postgres can never starve your other heavy services.
  • One-command workflow via make (up, down, psql, backup, restore, stats…).
  • Documented end-to-end — a checklist you can tick off from requirements all the way to deployment.

🚀 Quick start (TL;DR)

# 1. Configure (edit the password!)
cp .env.example .env

# 2. Build the image (first build compiles mysql_fdw — grab a coffee ☕)
make build

# 3. Start
make up

# 4. Verify extensions are live
make extensions

Then connect:

postgresql://local_dev:<your-password>@127.0.0.1:15409/local_db

Host port 15409 mirrors the original reference connection string; the generic local_dev / local_db names keep this reusable. Change anything in .env.

📚 Documentation — read in order

Each document is a self-contained checklist. Tick the boxes as you go.

# Document What it covers
0 Getting Started 👈 New here? Start with this — friendly step-by-step walkthrough
1 Requirements Hardware/software prerequisites, your laptop's budget
2 Installation Step-by-step from clone to running container
3 Configuration Every .env & postgresql.conf knob explained
4 Extensions What each extension does + usage snippets
5 Resource Tuning Why the limits are what they are; how to rebalance
6 NestJS Integration Wiring this DB into a NestJS/TypeORM/Prisma app
7 Backup & Restore Dump, restore, and scheduled backups
8 Deployment Promoting the same setup toward staging/prod
9 Troubleshooting Common errors and fixes
10 Security Hardening checklist for dev and beyond
11 Import from Source Safely migrate data from an existing PostgreSQL
12 Optional Features Opt-in pooler / backups / UI / metrics — zero cost when off

🗂️ Repository layout

postgresql-fdw/
├── docker-compose.yml          # the stack + hard resource caps
├── Dockerfile                  # PG17 + extensions (compiles mysql_fdw)
├── .env.example                # copy to .env and edit
├── Makefile                    # make up / down / psql / backup ...
├── config/
│   └── postgresql.conf         # laptop-tuned server config
├── scripts/
│   ├── init/                   # auto-run on first boot
│   │   ├── 00-extensions.sql   #   CREATE EXTENSION for everything
│   │   └── 01-roles-and-grants.sql
│   └── fdw-examples/           # copy-paste FDW recipes (not auto-run)
├── backups/                    # pg_dump output lands here
└── docs/                       # the numbered guides above

✅ Master checklist

A bird's-eye view. Detailed steps live in the linked docs.

  • Requirements met — Docker Engine + Compose v2, ≥2 GB free RAM budget → doc 1
  • .env created and password changed → doc 2
  • Image built (make build) → doc 2
  • Container healthy (make health) → doc 2
  • Extensions verified (make extensions) → doc 4
  • Resource caps reviewed for your machine → doc 5
  • App connected (NestJS) → doc 6
  • Backup tested at least once → doc 7
  • Security checklist reviewed → doc 10

🧩 Optional features (off by default, zero cost when off)

A plain make up starts only PostgreSQL. Everything else is opt-in via Docker Compose profiles, each hard-capped so it never slows your laptop:

make up-pooler    # + PgBouncer connection pooler   (port 6432)
make up-backup    # + scheduled rotated backups
make up-ui        # + Adminer web UI                (http://localhost:8080)
make up-metrics   # + postgres-exporter             (http://localhost:9187)
make up-all       # + everything above

Full matrix & tuning: doc 12 — Optional Features.

🧰 Everyday commands

make up           # start ONLY core Postgres
make down         # stop everything (data kept)
make psql         # open a psql shell
make extensions   # list installed extensions
make stats        # live CPU/RAM of the container
make backup       # one-off dump to ./backups (manual)
make logs         # follow logs
make destroy      # stop AND delete data (careful!)
make help         # everything

👤 Author

programmerShinobi · GitHub

If this project helps you, a ⭐ on the repo is appreciated!

📄 License

Copyright © 2026 programmerShinobi. Released under the MIT License.