Knowledge Hub

Getting Started with PostgreSQL on CleanStart

Running PostgreSQL with CleanStart

CleanStart provides secure PostgreSQL base images (cleanstart/postgresql) with PostgreSQL 14, 15, and 16 versions pre-configured for container environments. These images include FIPS 140-3 mode support for regulated industries, support for replication and backup tooling, and Kubernetes-ready health checks for production deployments.

Quick Start: A Simple PostgreSQL Setup

Step 1: Run PostgreSQL Container

docker run -d \    --name postgres \    -e POSTGRES_USER=appuser \    -e POSTGRES_PASSWORD=mysecurepassword \    -e POSTGRES_DB=myapp \    -v postgres-data:/var/lib/postgresql/data \    -p 5432:5432 \    cleanstart/postgresql:16

Step 2: Connect and Initialize Schema

docker exec -it postgres psql -U appuser -d myapp << EOFCREATE TABLE users (    id SERIAL PRIMARY KEY,    email VARCHAR(255) UNIQUE NOT NULL,    name VARCHAR(255),    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); CREATE INDEX idx_users_email ON users(email); INSERT INTO users (email, name) VALUES    ('user@example.com', 'John Doe'),    ('admin@example.com', 'Admin User');EOF

Step 3: Verify Connection

docker exec -it postgres psql -U appuser -d myapp -c "SELECT * FROM users;"

Production Dockerfile Setup

Create a custom PostgreSQL image with your schema:

FROM cleanstart/postgresql:16 # Create initialization scriptCOPY init-db.sql /docker-entrypoint-initdb.d/ # Set security parametersENV POSTGRES_INITDB_ARGS="-c max_connections=200 \  -c shared_buffers=256MB \  -c effective_cache_size=1GB \  -c maintenance_work_mem=64MB \  -c random_page_cost=1.1" # Health checkHEALTHCHECK --interval=10s --timeout=5s --start-period=10s --retries=3 \    CMD pg_isready -U postgres || exit 1

Create init-db.sql:

CREATE TABLE users (    id SERIAL PRIMARY KEY,    email VARCHAR(255) UNIQUE NOT NULL,    name VARCHAR(255),    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); CREATE TABLE sessions (    id UUID PRIMARY KEY,    user_id INTEGER NOT NULL REFERENCES users(id),    token VARCHAR(500) NOT NULL,    expires_at TIMESTAMP NOT NULL); CREATE INDEX idx_users_email ON users(email);CREATE INDEX idx_sessions_user_id ON sessions(user_id);CREATE INDEX idx_sessions_expires_at ON sessions(expires_at); -- Enable extensionsCREATE EXTENSION IF NOT EXISTS "uuid-ossp";CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Build and run:

docker build -t my-postgres:latest .docker run -d \    --name my-postgres \    -e POSTGRES_USER=appuser \    -e POSTGRES_PASSWORD='complex-password-here' \    -e POSTGRES_DB=myapp \    -v postgres-data:/var/lib/postgresql/data \    -p 5432:5432 \    my-postgres:latest

Configuration Management

Environment Variables

CleanStart PostgreSQL supports standard variables:

docker run -d \    -e POSTGRES_USER=dbuser \    -e POSTGRES_PASSWORD=secretpassword \    -e POSTGRES_DB=production_db \    -e POSTGRES_INITDB_ARGS="-c max_connections=200" \    cleanstart/postgresql:16

Memory and Performance Tuning

FROM cleanstart/postgresql:16 ENV POSTGRES_INITDB_ARGS="\  -c shared_buffers=512MB \  -c effective_cache_size=2GB \  -c maintenance_work_mem=256MB \  -c work_mem=50MB \  -c max_connections=300 \  -c max_parallel_workers=4" # Enable useful extensionsCOPY enable-extensions.sql /docker-entrypoint-initdb.d/ HEALTHCHECK --interval=10s --timeout=5s --retries=3 \    CMD pg_isready -U postgres || exit 1

Enable extensions in enable-extensions.sql:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";CREATE EXTENSION IF NOT EXISTS pg_stat_statements;CREATE EXTENSION IF NOT EXISTS pgcrypto;CREATE EXTENSION IF NOT EXISTS btree_gin;CREATE EXTENSION IF NOT EXISTS btree_gist;

Backup Configuration

Create a backup script backup.sh:

#!/bin/bashset -e BACKUP_DIR="/backups"TIMESTAMP=$(date +%Y%m%d_%H%M%S)BACKUP_FILE="$BACKUP_DIR/backup_$TIMESTAMP.sql.gz" mkdir -p "$BACKUP_DIR" # Create backuppg_dump -U $POSTGRES_USER -d $POSTGRES_DB | \    gzip > "$BACKUP_FILE" # Keep only last 7 daysfind "$BACKUP_DIR" -name "backup_*.sql.gz" -mtime +7 -delete echo "Backup completed: $BACKUP_FILE"

Add to Dockerfile:

FROM cleanstart/postgresql:16 COPY backup.sh /usr/local/bin/RUN chmod +x /usr/local/bin/backup.sh # Schedule backup with cronRUN apt-get update && apt-get install -y cron && rm -rf /var/lib/apt/lists/*

Replication Setup (Primary-Replica)

Primary Server

FROM cleanstart/postgresql:16 COPY primary-config.conf /etc/postgresql/postgresql.conf.d/COPY pg_hba.conf /etc/postgresql/ ENV POSTGRES_INITDB_ARGS="-c config_file=/etc/postgresql/postgresql.conf"

primary-config.conf:

wal_level = replicamax_wal_senders = 10max_replication_slots = 10hot_standby = on

pg_hba.conf:

# Allow replication from replicahost    replication     replicator    10.0.0.0/8    scram-sha-256

Replica Server

# Create replicadocker run -d \    --name postgres-replica \    -e PGUSER=postgres \    -e PGPASSWORD=replica_password \    cleanstart/postgresql:16 \    pg_basebackup -h postgres-primary -D /var/lib/postgresql/data -U replicator -v -P -W

Docker Compose Example

version: '3.9' services:  postgres:    image: cleanstart/postgresql:16    container_name: my-postgres    environment:      POSTGRES_USER: appuser      POSTGRES_PASSWORD: ${DB_PASSWORD}      POSTGRES_DB: myapp      POSTGRES_INITDB_ARGS: |        -c max_connections=200        -c shared_buffers=256MB    ports:      - "5432:5432"    volumes:      - postgres-data:/var/lib/postgresql/data      - ./init-db.sql:/docker-entrypoint-initdb.d/init.sql    healthcheck:      test: ["CMD-SHELL", "pg_isready -U appuser"]      interval: 10s      timeout: 5s      retries: 5    networks:      - app-network   pgadmin:    image: dpage/pgadmin4:latest    container_name: pgadmin    environment:      PGADMIN_DEFAULT_EMAIL: admin@example.com      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD}    ports:      - "5050:80"    depends_on:      - postgres    networks:      - app-network volumes:  postgres-data: networks:  app-network:

Run:

export DB_PASSWORD='secure-password'export PGADMIN_PASSWORD='admin-password'docker-compose up -d

FIPS 140-3 Compliance

For regulated environments:

docker run -d \    --name postgres-fips \    cleanstart/postgresql:16-fips \    -e POSTGRES_USER=appuser \    -e POSTGRES_PASSWORD=password

Kubernetes Deployment

Create postgres-deployment.yaml:

apiVersion: apps/v1kind: Deploymentmetadata:  name: postgresspec:  replicas: 1  selector:    matchLabels:      app: postgres  template:    metadata:      labels:        app: postgres    spec:      containers:      - name: postgres        image: cleanstart/postgresql:16        env:        - name: POSTGRES_USER          value: appuser        - name: POSTGRES_PASSWORD          valueFrom:            secretKeyRef:              name: db-credentials              key: password        - name: POSTGRES_DB          value: myapp        ports:        - containerPort: 5432        livenessProbe:          exec:            command:            - /bin/sh            - -c            - pg_isready -U appuser          initialDelaySeconds: 30          periodSeconds: 10        volumeMounts:        - name: data          mountPath: /var/lib/postgresql/data        - name: init-scripts          mountPath: /docker-entrypoint-initdb.d      volumes:      - name: data        persistentVolumeClaim:          claimName: postgres-pvc      - name: init-scripts        configMap:          name: postgres-init ---apiVersion: v1kind: Servicemetadata:  name: postgresspec:  ports:  - port: 5432    targetPort: 5432  selector:    app: postgres  clusterIP: None ---apiVersion: v1kind: PersistentVolumeClaimmetadata:  name: postgres-pvcspec:  accessModes:  - ReadWriteOnce  resources:    requests:      storage: 10Gi

Deploy:

kubectl create secret generic db-credentials --from-literal=password='secure-password'kubectl apply -f postgres-deployment.yaml

Security Best Practices

Strong Passwords

Use randomly generated passwords:

openssl rand -base64 32

Connection Limits

Connection limits control how your PostgreSQL instance handles concurrent client connections and query workloads. The max_connections parameter sets the total number of simultaneous connections the database will accept (typically 200 for moderate workloads). The max_parallel_workers parameter controls how many workers PostgreSQL can use for parallel query execution (usually 4 on modern hardware). The work_mem parameter determines how much memory each query operation can use before spilling to disk (typically 50MB per operation).

max_connections = 200           # Total connectionsmax_parallel_workers = 4         # Parallel querieswork_mem = 50MB                  # Per query memory

Authentication

Change the default password immediately:

docker exec -it postgres psql -U postgres -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

Encryption in Transit

Use SSL/TLS for remote connections:

FROM cleanstart/postgresql:16 # Copy SSL certificatesCOPY server.crt /etc/postgresql/COPY server.key /etc/postgresql/ ENV POSTGRES_INITDB_ARGS="-c ssl=on -c ssl_cert_file=/etc/postgresql/server.crt -c ssl_key_file=/etc/postgresql/server.key"

Monitoring

Basic Health Check

docker exec postgres pg_isready -U appuser

Detailed Status

docker exec -it postgres psql -U appuser -c "SELECT version();"docker exec -it postgres psql -U appuser -c "SELECT datname, numbackends FROM pg_stat_database;"

Performance Monitoring

SELECT    query,    calls,    total_time,    mean_timeFROM pg_stat_statementsORDER BY total_time DESCLIMIT 10;

Troubleshooting

Connection refused errors typically indicate issues with port configuration or network connectivity between services. Verify that port 5432 is properly exposed and accessible from the client. Authentication failures usually stem from mismatched POSTGRES_USER and POSTGRES_PASSWORD values—double-check that the credentials provided at runtime match those configured in your environment variables. Slow query performance can be addressed by checking that appropriate database indexes exist, enabling the pg_stat_statements extension for query analysis, and reviewing execution plans. Out of disk errors require monitoring your data volume size, implementing a retention policy for backups, and cleaning old backup files to reclaim storage space.

Image Options

Image	Use Case
`cleanstart/postgresql:16`	Latest stable (recommended)
`cleanstart/postgresql:15`	Legacy applications
`cleanstart/postgresql:16-fips`	FIPS 140-3 compliance

CleanStart Production Hardening

Read-Only Root Filesystem

Enforce immutable infrastructure with read-only root, with specific writable paths for PostgreSQL data:

apiVersion: v1kind: Podmetadata:  name: postgresqlspec:  containers:  - name: postgres    image: cleanstart/postgresql:16    securityContext:      readOnlyRootFilesystem: true    volumeMounts:    - name: data      mountPath: /var/lib/postgresql/data    - name: run      mountPath: /var/run/postgresql    - name: tmp      mountPath: /tmp  volumes:  - name: data    persistentVolumeClaim:      claimName: postgres-data  - name: run    emptyDir: {}  - name: tmp    emptyDir: {}

Shell-Less ENTRYPOINT

Remove shell for attack surface reduction. Update your Dockerfile:

FROM cleanstart/postgresql:16 # Copy custom PostgreSQL configurationCOPY postgresql.conf /etc/postgresql/postgresql.conf.d/ # Declarative Image Builder: Use cleanimg-init as PID 1ENTRYPOINT ["/cleanimg-init", "--"]CMD ["postgres"]

cleanimg-customize: PostgreSQL Configuration

Inject production-hardened PostgreSQL settings:

FROM cleanstart/postgresql:16 # Create custom configuration directoryRUN mkdir -p /etc/postgresql/conf.d # Inject hardened postgresql.conf settingsRUN echo 'ssl = on' >> /etc/postgresql/postgresql.conf && \    echo 'ssl_protocols = "TLSv1.3"' >> /etc/postgresql/postgresql.conf && \    echo 'max_connections = 100' >> /etc/postgresql/postgresql.conf && \    echo 'shared_buffers = 256MB' >> /etc/postgresql/postgresql.conf ENTRYPOINT ["/cleanimg-init", "--"]CMD ["postgres"]

Security Context

Complete Kubernetes securityContext for hardened PostgreSQL containers:

apiVersion: apps/v1kind: StatefulSetmetadata:  name: postgresqlspec:  template:    spec:      securityContext:        fsGroup: 999        seccompProfile:          type: RuntimeDefault      containers:      - name: postgres        image: cleanstart/postgresql:16        securityContext:          allowPrivilegeEscalation: false          readOnlyRootFilesystem: true          runAsNonRoot: true          runAsUser: 999          runAsGroup: 999          capabilities:            drop:            - ALL            add:            - NET_BIND_SERVICE        resources:          requests:            cpu: 500m            memory: 512Mi          limits:            cpu: 2000m            memory: 2Gi

Next Steps

Advanced PostgreSQL Configuration, PostgreSQL Replication Guide, and CleanStart Image Registry.