LiorCohen/sdd / plugin/fullstack-typescript/skills/components/database/postgresql
postgresql
FlaggedPostgreSQL database agent: deploy locally (Docker/K8s), manage schemas, run queries, analyze performance, configure permissions, and seed data.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 4.1 KB |
| references/common-errors.md | markdown | 12.6 KB |
| references/deployment.md | markdown | 12.0 KB |
| references/introspection-queries.md | markdown | 13.1 KB |
| references/performance-tuning.md | markdown | 13.2 KB |
| references/permissions-setup.md | markdown | 15.6 KB |
| references/schema-management.md | markdown | 12.4 KB |
| references/seed-data.md | markdown | 11.3 KB |
| references/system-views.md | markdown | 14.2 KB |
| resources/administration.md | markdown | 2.1 KB |
| resources/deployment.md | markdown | 911 B |
| resources/monitoring.md | markdown | 1.9 KB |
| resources/schema-management.md | markdown | 2.1 KB |
references/deployment.md
View on GitHub# PostgreSQL Deployment
Local deployment patterns for Docker, Docker Compose, and Kubernetes.
---
## Docker
### Basic Container
```bash
docker run -d \
--name postgres \
-e POSTGRES_USER=app \
-e POSTGRES_PASSWORD=secret \
-e POSTGRES_DB=myapp \
-p 5432:5432 \
postgres:16
```
### With Persistent Volume
```bash
docker run -d \
--name postgres \
-e POSTGRES_USER=app \
-e POSTGRES_PASSWORD=secret \
-e POSTGRES_DB=myapp \
-p 5432:5432 \
-v pgdata:/var/lib/postgresql/data \
postgres:16
```
### With Init Scripts
Mount SQL files to `/docker-entrypoint-initdb.d/` for automatic execution on first start:
```bash
docker run -d \
--name postgres \
-e POSTGRES_USER=app \
-e POSTGRES_PASSWORD=secret \
-e POSTGRES_DB=myapp \
-p 5432:5432 \
-v pgdata:/var/lib/postgresql/data \
-v ./init:/docker-entrypoint-initdb.d:ro \
postgres:16
```
Init script directory structure:
```
init/
├── 01-schema.sql
├── 02-permissions.sql
└── 03-seed.sql
```
Scripts execute in alphabetical order. Only runs on fresh database (empty data volume).
### Connect to Container
```bash
# Interactive psql
docker exec -it postgres psql -U app -d myapp
# Run single command
docker exec postgres psql -U app -d myapp -c "SELECT version();"
# Run SQL file
docker exec -i postgres psql -U app -d myapp < script.sql
```
---
## Docker Compose
### Basic Setup
```yaml
# docker-compose.yml
services:
db:
image: postgres:16
container_name: postgres
environment:
POSTGRES_USER: app
POSTGRES_PASSWORD: secret
POSTGRES_DB: myapp
ports:
- "5432:5432"
volumes:
- pgdata:/var/lib/postgresql/data
volumes:
pgdata:
```
### With Health Check
```yaml
services:
db:
image: postgres:16
environment:
POSTGRES_USER: app
POSTGRES_PASSWORD: secret
POSTGRES_DB: myapp
ports:
- "5432:5432"
volumes:
- pgdata:/var/lib/postgresql/data
healthcheck:
test: ["CMD-SHELL", "pg_isready -U app -d myapp"]
interval: 5s
timeout: 5s
retries: 5
start_period: 10s
volumes:
pgdata:
```
### App with Database Dependency
```yaml
services:
app:
build: .
depends_on:
db:
condition: service_healthy
environment:
DATABASE_URL: postgresql://app:secret@db:5432/myapp
db:
image: postgres:16
environment:
POSTGRES_USER: app
POSTGRES_PASSWORD: secret
POSTGRES_DB: myapp
ports:
- "5432:5432"
volumes:
- pgdata:/var/lib/postgresql/data
healthcheck:
test: ["CMD-SHELL", "pg_isready -U app -d myapp"]
interval: 5s
timeout: 5s
retries: 5
volumes:
pgdata:
```
### With Init Scripts
```yaml
services:
db:
image: postgres:16
environment:
POSTGRES_USER: app
POSTGRES_PASSWORD: secret
POSTGRES_DB: myapp
ports:
- "5432:5432"
volumes:
- pgdata:/var/lib/postgresql/data
- ./init:/docker-entrypoint-initdb.d:ro
healthcheck:
test: ["CMD-SHELL", "pg_isready -U app -d myapp"]
interval: 5s
timeout: 5s
retries: 5
volumes:
pgdata:
```
### Connect via Compose
```bash
# Interactive psql
docker compose exec db psql -U app -d myapp
# Run command
docker compose exec db psql -U app -d myapp -c "SELECT version();"
# View logs
docker compose logs db
```
---
## Kubernetes
### Simple Pod (Testing Only)
For quick testing, not production use:
```yaml
# postgres-pod.yaml
apiVersion: v1
kind: Pod
metadata:
name: postgres
labels:
app: postgres
spec:
containers:
- name: postgres
image: postgres:16
ports:
- containerPort: 5432
env:
- name: POSTGRES_USER
value: app
- name: POSTGRES_PASSWORD
valueFrom:
secretKeyRef:
name: postgres-secret
key: password
- name: POSTGRES_DB
value: myapp
readinessProbe:
exec:
command: ["pg_isready", "-U", "app", "-d", "myapp"]
initialDelaySeconds: 5
periodSeconds: 5
---
apiVersion: v1
kind: Secret
metadata:
name: postgres-secret
type: Opaque
stringData:
password: secret
```
```bash
kubectl apply -f postgres-pod.yaml
kubectl port-forward pod/postgres 5432:5432
```
### StatefulSet with PVC (Persistent)
```yaml
# postgres-statefulset.yaml
apiVersion: v1
kind: Secret
metadata:
name: postgres-secret
type: Opaque
stringData:
password: secret
---
apiVersion: v1
kind: Service
metadata:
name: postgres
labels:
app: postgres
spec:
type: ClusterIP
ports:
- port: 5432
targetPort: 5432
selector:
app: postgres
---
apiVersion: v1
kind: Service
metadata:
name: postgres-headless
labels:
app: postgres
spec:
type: ClusterIP
clusterIP: None
ports:
- port: 5432
targetPort: 5432
selector:
app: postgres
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: postgres
spec:
serviceName: postgres-headless
replicas: 1
selector:
matchLabels:
app: postgres
template:
metadata:
labels:
app: postgres
spec:
containers:
- name: postgres
image: postgres:16
ports:
- containerPort: 5432
env:
- name: POSTGRES_USER
value: app
- name: POSTGRES_PASSWORD
valueFrom:
secretKeyRef:
name: postgres-secret
key: password
- name: POSTGRES_DB
value: myapp
- name: PGDATA
value: /var/lib/postgresql/data/pgdata
volumeMounts:
- name: postgres-data
mountPath: /var/lib/postgresql/data
readinessProbe:
exec:
command: ["pg_isready", "-U", "app", "-d", "myapp"]
initialDelaySeconds: 5
periodSeconds: 5
resources:
requests:
memory: "256Mi"
cpu: "250m"
limits:
memory: "512Mi"
cpu: "500m"
volumeClaimTemplates:
- metadata:
name: postgres-data
spec:
accessModes: ["ReadWriteOnce"]
resources:
requests:
storage: 10Gi
```
```bash
kubectl apply -f postgres-statefulset.yaml
```
### With Init Scripts via ConfigMap
```yaml
# postgres-init-configmap.yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: postgres-init
data:
01-schema.sql: |
CREATE SCHEMA IF NOT EXISTS app;
CREATE TABLE app.users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
02-permissions.sql: |
CREATE ROLE app_role;
GRANT USAGE ON SCHEMA app TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_role;
```
Add to StatefulSet spec:
```yaml
spec:
containers:
- name: postgres
# ... other config
volumeMounts:
- name: postgres-data
mountPath: /var/lib/postgresql/data
- name: init-scripts
mountPath: /docker-entrypoint-initdb.d
readOnly: true
volumes:
- name: init-scripts
configMap:
name: postgres-init
```
### Connect via Kubernetes
```bash
# Port forward for local access
kubectl port-forward svc/postgres 5432:5432
# Then connect locally
psql -h localhost -U app -d myapp
# Or exec into pod
kubectl exec -it postgres-0 -- psql -U app -d myapp
# Run command
kubectl exec postgres-0 -- psql -U app -d myapp -c "SELECT version();"
```
### NodePort Service (External Access)
```yaml
apiVersion: v1
kind: Service
metadata:
name: postgres-external
spec:
type: NodePort
ports:
- port: 5432
targetPort: 5432
nodePort: 30432
selector:
app: postgres
```
### Minikube Specifics
```bash
# Start minikube with enough resources
minikube start --cpus=2 --memory=4096
# Get service URL
minikube service postgres-external --url
# Or use tunnel
minikube tunnel
# Then connect to localhost:5432
```
### Kind Specifics
```yaml
# kind-config.yaml
kind: Cluster
apiVersion: kind.x-k8s.io/v1alpha4
nodes:
- role: control-plane
extraPortMappings:
- containerPort: 30432
hostPort: 5432
protocol: TCP
```
```bash
kind create cluster --config kind-config.yaml
# Apply postgres with NodePort 30432
# Connect to localhost:5432
```
---
## Initialization Scripts
### Directory Structure
```
init/
├── 01-schema.sql # Table definitions
├── 02-permissions.sql # Roles and grants
└── 03-seed.sql # Initial data
```
### Example: 01-schema.sql
```sql
-- Create application schema
CREATE SCHEMA IF NOT EXISTS app;
-- Users table
CREATE TABLE app.users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_users_email ON app.users (email);
-- Audit timestamps trigger
CREATE OR REPLACE FUNCTION app.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_updated_at
BEFORE UPDATE ON app.users
FOR EACH ROW
EXECUTE FUNCTION app.update_updated_at();
```
### Example: 02-permissions.sql
```sql
-- Create application role
CREATE ROLE app_role;
-- Schema permissions
GRANT USAGE ON SCHEMA app TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_role;
-- Default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT USAGE, SELECT ON SEQUENCES TO app_role;
-- Create application user
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT app_role TO app_user;
-- Set default schema
ALTER ROLE app_user SET search_path TO app, public;
```
### Example: 03-seed.sql
```sql
-- Seed initial data
INSERT INTO app.users (email, name, password_hash) VALUES
('admin@example.com', 'Admin', '$2a$10$...')
ON CONFLICT (email) DO NOTHING;
```
---
## Connection Strings
### Format
```
postgresql://[user]:[password]@[host]:[port]/[database]?[options]
```
### Examples by Environment
```bash
# Docker (from host)
postgresql://app:secret@localhost:5432/myapp
# Docker Compose (from another container)
postgresql://app:secret@db:5432/myapp
# Kubernetes (via port-forward)
postgresql://app:secret@localhost:5432/myapp
# Kubernetes (in-cluster)
postgresql://app:secret@postgres.default.svc.cluster.local:5432/myapp
# With SSL
postgresql://app:secret@localhost:5432/myapp?sslmode=require
```
### Connection Options
| Option | Values | Description |
|--------|--------|-------------|
| `sslmode` | disable, allow, prefer, require, verify-ca, verify-full | SSL mode |
| `connect_timeout` | seconds | Connection timeout |
| `application_name` | string | Application identifier |
| `options` | string | Server options |
---
## Troubleshooting
### Container Won't Start
```bash
# Check logs
docker logs postgres
# Common issues:
# - Port already in use: stop other postgres or use different port
# - Permission denied: check volume permissions
# - Init script error: check SQL syntax
```
### Can't Connect
```bash
# Verify container is running
docker ps | grep postgres
# Check port binding
docker port postgres
# Test connectivity
nc -zv localhost 5432
# Check pg_hba.conf allows connections
docker exec postgres cat /var/lib/postgresql/data/pg_hba.conf
```
### Init Scripts Not Running
Init scripts only run when the data directory is empty (first start):
```bash
# Reset to run init scripts again
docker compose down -v # Removes volumes
docker compose up -d
```
### Kubernetes Pod Pending
```bash
# Check events
kubectl describe pod postgres-0
# Common issues:
# - PVC not bound: check storage class
# - Insufficient resources: check node capacity
```
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deployment | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved. | Stars: 197k | Updated | Claude CodeCodex | Verified | Install |
| shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |