Docker Compose PostgreSQL + pgAdmin Setup

A Docker Compose application with PostgreSQL 17 and pgAdmin 4, configured for both internal connectivity and external access.

What is Docker?

Docker is a containerization platform that packages applications and their dependencies into lightweight, portable containers. Think of containers as isolated environments that contain everything needed to run an application.

Docker Compose is a tool for defining and running multi-container Docker applications using a YAML file. It allows you to:

Define multiple services (like database + web interface) in one file
Start/stop all services with single commands
Manage networking between containers automatically
Handle data persistence through volumes

Key Concepts

Container: A running instance of an image - like a mini virtual machine
Image: A template used to create containers (e.g., postgres:17)
Volume: Persistent storage that survives container restarts
Network: Allows containers to communicate with each other
Service: A container definition in docker-compose.yml

Features

PostgreSQL 17 with persistent storage
Latest pgAdmin 4 with web interface
Internal networking for service communication
External access for applications and development
Health checks for reliability
Auto-restart policies

Quick Start

Setup environment variables:
```
cp .env.example .env
```
Edit .env file to customize your configuration if needed.
Start the services:
```
docker-compose up -d
```
Access pgAdmin:
- URL: http://localhost:8080
- Email: admin@example.com
- Password: admin123
Connect to PostgreSQL from pgAdmin:
- The PostgreSQL server is pre-configured in pgAdmin
- Host: postgres (internal Docker network)
- Port: 5432
- Database: myapp
- Username: postgres
- Password: postgres123

External Access

From Host Applications

Connect to PostgreSQL using:

Host: localhost
Port: 5432
Database: myapp
Username: postgres
Password: postgres123

From Other Docker Containers

Add containers to the same network:

networks:
  - app_network

networks:
  app_network:
    external: true
    name: latest_app_network

Then connect using:

Host: postgres
Port: 5432

Configuration

Environment Variables

The project uses environment variables for configuration. Copy the example file and customize as needed:

cp .env.example .env

Environment Variables Explained

Variable	Purpose	Default Value	Description
PostgreSQL Configuration
`POSTGRES_DB`	Database name	`myapp`	The default database created on first startup
`POSTGRES_USER`	Database user	`postgres`	The superuser account for PostgreSQL
`POSTGRES_PASSWORD`	Database password	`postgres123`	Password for the PostgreSQL user
`POSTGRES_PORT`	Host port mapping	`5432`	Port on your computer that maps to container port 5432
pgAdmin Configuration
`PGADMIN_DEFAULT_EMAIL`	Login email	`admin@example.com`	Email used to log into pgAdmin web interface
`PGADMIN_DEFAULT_PASSWORD`	Login password	`admin123`	Password for pgAdmin web interface
`PGADMIN_PORT`	Host port mapping	`8080`	Port on your computer that maps to container port 80

Additional Docker Compose Variables

These are configured directly in docker-compose.yml:

Variable	Purpose	Value	Description
`POSTGRES_INITDB_ARGS`	Database initialization	`"--encoding=UTF-8"`	Sets default encoding for new databases
`PGADMIN_CONFIG_SERVER_MODE`	pgAdmin mode	`'False'`	Runs pgAdmin in desktop mode (simpler setup)
`PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED`	Master password	`'False'`	Disables master password requirement

Security Warning: Change all default passwords before using in production!

Custom Initialization Scripts

Place SQL scripts in ./init-scripts/ to run during database initialization:

mkdir init-scripts
echo "CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100));" > init-scripts/01-create-tables.sql

Docker Commands Guide

Basic Operations

Starting Services

# Start all services in background (detached mode)
docker-compose up -d
# What it does: Creates and starts containers defined in docker-compose.yml

# Start with logs visible (foreground mode)
docker-compose up
# What it does: Same as above but shows real-time logs

# Force recreate containers (useful when config changes)
docker-compose up -d --force-recreate
# What it does: Stops and recreates all containers even if no changes detected

# Build images and start (when Dockerfile changes)
docker-compose up -d --build
# What it does: Rebuilds container images before starting

Stopping Services

# Stop services (containers remain, can be restarted quickly)
docker-compose stop
# What it does: Gracefully stops running containers but keeps them

# Stop and remove containers (but keep volumes/data)
docker-compose down
# What it means: "Down" removes containers completely, but data persists in volumes

# Stop and remove everything including volumes (DESTRUCTIVE!)
docker-compose down -v
# What it does: Removes containers AND deletes all data permanently

Restarting Services

# Restart all services
docker-compose restart
# What it means: Stops and starts containers without recreating them

# Restart specific service
docker-compose restart postgres
# What it does: Restarts only the PostgreSQL container

# Restart with new configuration
docker-compose down && docker-compose up -d
# What it does: Complete restart that picks up configuration changes

Checking Status and Logs

Container Status

# Check status of services defined in docker-compose.yml
docker-compose ps
# Shows: Service name, state (Up/Down/Exited), ports, health

# Check status of all Docker containers on system
docker ps
# Shows: All running containers with details

# Check status including stopped containers
docker ps -a
# Shows: All containers, including those that have stopped

Viewing Logs

# View logs from all services
docker-compose logs

# Follow logs in real-time (like tail -f)
docker-compose logs -f

# View logs from specific service
docker-compose logs postgres
docker-compose logs pgadmin

# View logs with timestamps
docker-compose logs -t

# View last 50 lines of logs
docker-compose logs --tail=50

# Follow logs for specific service
docker-compose logs -f postgres

Management Commands

Accessing Containers

# Access PostgreSQL command line
docker-compose exec postgres psql -U postgres -d myapp
# What it does: Opens interactive PostgreSQL shell inside container

# Access container's bash shell
docker-compose exec postgres bash
# What it does: Opens command line inside the PostgreSQL container

# Access pgAdmin container shell
docker-compose exec pgadmin bash
# What it does: Opens command line inside the pgAdmin container

Data and Volume Management

# List all volumes
docker volume ls
# Shows: All Docker volumes on your system

# Inspect volume details
docker volume inspect pg-docker_postgres_data
# Shows: Volume location, driver, and mount information

# Remove unused volumes (be careful!)
docker volume prune
# What it does: Removes volumes not attached to any container

Build Operations

What Does "Building" Mean?

Building creates Docker images from instructions. This project uses pre-built images (postgres:17, dpage/pgadmin4:latest), so you typically don't need to build anything. Building is needed when:

You have custom Dockerfiles
You modify container configurations
You want to ensure latest image versions

# Pull latest versions of images
docker-compose pull
# What it does: Downloads newest versions of postgres:17 and pgadmin4:latest

# Build services (only if custom Dockerfiles exist)
docker-compose build
# What it does: Creates images from Dockerfiles in your project

# Force rebuild ignoring cache
docker-compose build --no-cache
# What it does: Rebuilds images from scratch

Network Details

Network Name: latest_app_network
Subnet: 172.20.0.0/16
postgres container: Accessible as postgres hostname
pgadmin container: Accessible as pgadmin hostname

Persistent Storage

Data is stored in Docker volumes:

postgres_data: Database files
pgadmin_data: pgAdmin configuration

These volumes persist data between container restarts.

Security Notes

Change default passwords in .env file for production
The .env file is excluded from git via .gitignore
pgAdmin is configured in desktop mode (no master password required)

Advanced Management Operations

Health Checks and Monitoring

# Check container health status
docker-compose ps
# Look for "healthy" status in the State column

# Inspect detailed container information
docker inspect postgres_db
# Shows: Full container configuration, network settings, mounts

# Check resource usage
docker stats
# Shows: Real-time CPU, memory, network usage for all containers

# Check container processes
docker-compose exec postgres ps aux
# Shows: All processes running inside the PostgreSQL container

Network Operations

# List all Docker networks
docker network ls

# Inspect the app network
docker network inspect pg-docker_app_network
# Shows: Connected containers, IP addresses, subnet configuration

# Test connectivity between containers
docker-compose exec postgres ping pgadmin
# Tests if PostgreSQL container can reach pgAdmin container

Database Operations

# Create database backup
docker-compose exec postgres pg_dump -U postgres myapp > backup.sql
# What it does: Exports database to SQL file on host machine

# Restore database from backup
docker-compose exec -T postgres psql -U postgres myapp < backup.sql
# What it does: Imports SQL file into database

# Connect to different database
docker-compose exec postgres psql -U postgres -d postgres
# What it does: Connects to the default 'postgres' database instead of 'myapp'

# List all databases
docker-compose exec postgres psql -U postgres -l
# Shows: All databases in the PostgreSQL instance

Troubleshooting Guide

Common Issues and Solutions

PostgreSQL Won't Start

# Step 1: Check logs for error messages
docker-compose logs postgres

# Step 2: Check if port is already in use
lsof -i :5432
# If another service is using port 5432, either stop it or change POSTGRES_PORT

# Step 3: Check file permissions (macOS/Linux)
ls -la postgres_data/
# Ensure Docker has permission to write to volume

# Step 4: Reset database (DESTRUCTIVE - removes all data)
docker-compose down -v
docker-compose up -d

Can't Connect from External Application

# Check if PostgreSQL is actually running
docker-compose ps
# Should show postgres_db as "Up" and "healthy"

# Verify port mapping
docker port postgres_db
# Should show: 5432/tcp -> 0.0.0.0:5432

# Test connection from host
telnet localhost 5432
# Should connect successfully

# Common connection string format:
# Host: localhost, Port: 5432, Database: myapp, User: postgres, Password: postgres123

pgAdmin Issues

# Can't access pgAdmin web interface
# 1. Check if container is running
docker-compose ps

# 2. Verify port mapping
docker port pgadmin_web
# Should show: 80/tcp -> 0.0.0.0:8080

# 3. Check pgAdmin logs
docker-compose logs pgadmin

# 4. Clear browser cache and try incognito mode

# 5. Restart pgAdmin container
docker-compose restart pgadmin

Container Keeps Restarting

# Check exit code and error logs
docker-compose logs --tail=100 postgres

# Common causes:
# - Incorrect environment variables
# - Permission issues with volumes
# - Insufficient memory/disk space
# - Port conflicts

# Check system resources
df -h  # Disk space
free -h  # Memory (Linux)

Data Not Persisting

# Verify volumes are created
docker volume ls | grep postgres

# Check volume mount points
docker-compose config
# Look for volumes section to verify configuration

# Inspect volume details
docker volume inspect pg-docker_postgres_data

Performance Issues

# Monitor resource usage
docker stats postgres_db pgadmin_web

# Check PostgreSQL performance
docker-compose exec postgres psql -U postgres -d myapp -c "SELECT version();"
docker-compose exec postgres psql -U postgres -d myapp -c "SHOW shared_buffers;"

# Optimize PostgreSQL (add to docker-compose.yml environment section):
# POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=C"

Emergency Recovery

# If containers are completely broken:
# 1. Stop everything
docker-compose down

# 2. Remove containers but keep data
docker-compose rm -f

# 3. Recreate containers
docker-compose up -d

# If that doesn't work, nuclear option (LOSES ALL DATA):
docker-compose down -v
docker system prune -f
docker-compose up -d

Best Practices and Security

Development Best Practices

Environment Management

# Always use .env files for configuration
cp .env.example .env
# Edit .env with your specific values

# Never commit .env files to git
echo ".env" >> .gitignore

# Use different .env files for different environments
# .env.development, .env.staging, .env.production

Container Management

# Always use specific image tags in production
# Instead of: postgres:latest
# Use: postgres:17.0

# Clean up regularly to save disk space
docker system prune -f
docker volume prune -f

# Monitor container logs regularly
docker-compose logs --tail=100 -f

Security Considerations

Password Security

Never use default passwords in production
Use strong, unique passwords for each environment
Store production passwords in secure password managers
Rotate passwords regularly

# Generate strong passwords
openssl rand -base64 32

# Example secure .env for production:
POSTGRES_PASSWORD=your-super-secure-random-password-here
PGADMIN_DEFAULT_PASSWORD=another-secure-password-here

Network Security

# For production, consider these docker-compose.yml changes:

# 1. Don't expose PostgreSQL port to host (remove ports section)
# 2. Use custom networks with encryption
# 3. Enable SSL/TLS for PostgreSQL connections
# 4. Restrict pgAdmin access with reverse proxy

File Permissions

# Ensure proper ownership of volume directories
sudo chown -R 999:999 postgres_data/  # PostgreSQL user ID in container
sudo chown -R 5050:5050 pgadmin_data/ # pgAdmin user ID in container

# Set restrictive permissions on .env file
chmod 600 .env

Production Deployment

Configuration Changes for Production

# Add to docker-compose.yml for production:
services:
  postgres:
    # Remove port exposure for security
    # ports:
    #   - "${POSTGRES_PORT:-5432}:5432"

    # Add resource limits
    deploy:
      resources:
        limits:
          cpus: '2'
          memory: 2G
        reservations:
          cpus: '1'
          memory: 1G

    # Enable logging configuration
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "3"

Backup Strategy

# Automated daily backups
# Add to crontab: 0 2 * * * /path/to/backup-script.sh

#!/bin/bash
# backup-script.sh
DATE=$(date +%Y%m%d_%H%M%S)
docker-compose exec -T postgres pg_dump -U postgres myapp > backup_${DATE}.sql
# Upload to cloud storage or remote backup location

Monitoring

# Health check URLs for monitoring services
# PostgreSQL: Use pg_isready command
# pgAdmin: HTTP check on port 8080

# Set up alerts for:
# - Container restart events
# - High resource usage
# - Failed health checks
# - Disk space running low

Performance Optimization

PostgreSQL Tuning

# Add to postgres environment in docker-compose.yml:
environment:
  # Memory settings (adjust based on your system)
  POSTGRES_SHARED_BUFFERS: "256MB"
  POSTGRES_EFFECTIVE_CACHE_SIZE: "1GB"
  POSTGRES_WORK_MEM: "4MB"
  POSTGRES_MAINTENANCE_WORK_MEM: "64MB"

  # Connection settings
  POSTGRES_MAX_CONNECTIONS: "100"

  # Logging for debugging
  POSTGRES_LOG_STATEMENT: "all"  # Remove in production

Resource Monitoring

# Monitor container performance
docker stats postgres_db pgadmin_web

# Monitor disk usage of volumes
docker system df

# Check PostgreSQL query performance
docker-compose exec postgres psql -U postgres -d myapp -c "SELECT * FROM pg_stat_activity;"

Quick Reference

Essential Commands Cheat Sheet

# Start everything
docker-compose up -d

# Check status
docker-compose ps

# View logs
docker-compose logs -f

# Stop everything (keep data)
docker-compose down

# Stop everything (remove data)
docker-compose down -v

# Restart everything
docker-compose restart

# Access PostgreSQL
docker-compose exec postgres psql -U postgres -d myapp

# Backup database
docker-compose exec postgres pg_dump -U postgres myapp > backup.sql

# Check resource usage
docker stats

Configuration Files Overview

docker-compose.yml: Defines services, networks, and volumes
.env: Environment variables for configuration
pgadmin-servers.json: Pre-configured pgAdmin server connections
init-scripts/: SQL scripts that run on database initialization

Name		Name	Last commit message	Last commit date
Latest commit History 1 Commit
init-scripts		init-scripts
.env.example		.env.example
.gitignore		.gitignore
README.md		README.md
docker-compose.yml		docker-compose.yml
pgadmin-servers.json		pgadmin-servers.json

Folders and files

Latest commit

History

Repository files navigation

Docker Compose PostgreSQL + pgAdmin Setup

What is Docker?

Key Concepts

Features

Quick Start

External Access

From Host Applications

From Other Docker Containers

Configuration

Environment Variables

Environment Variables Explained

Additional Docker Compose Variables

Custom Initialization Scripts

Docker Commands Guide

Basic Operations

Starting Services

Stopping Services

Restarting Services

Checking Status and Logs

Container Status

Viewing Logs

Management Commands

Accessing Containers

Data and Volume Management

Build Operations

What Does "Building" Mean?

Network Details

Persistent Storage

Security Notes

Advanced Management Operations

Health Checks and Monitoring

Network Operations

Database Operations

Troubleshooting Guide

Common Issues and Solutions

PostgreSQL Won't Start

Can't Connect from External Application

pgAdmin Issues

Container Keeps Restarting

Data Not Persisting

Performance Issues

Emergency Recovery

Best Practices and Security

Development Best Practices

Environment Management

Container Management

Security Considerations

Password Security

Network Security

File Permissions

Production Deployment

Configuration Changes for Production

Backup Strategy

Monitoring

Performance Optimization

PostgreSQL Tuning

Resource Monitoring

Quick Reference

Essential Commands Cheat Sheet

Configuration Files Overview

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors

Uh oh!

Packages