Running PostgreSQL with Docker Compose: An In-Depth Guide

Docker Compose makes deploying complex multi-container apps a breeze. With just a configuration file, you can launch an entire environment with orchestrated containers, volumes and networks.

In this comprehensive 2600+ word guide, we will explore the ins and outs of running PostgreSQL in Docker Compose, tailored for production environments.

Topics covered:

• Compose Overview
• Compose for PostgreSQL
• Storage Considerations
• Backup & Recovery
• Monitoring & Logs
• Compose in Production
• Alternative Orchestrators
• Troubleshooting
• Conclusion

Let‘s get started!

Docker Compose Overview

Docker Compose is a tool that defines and runs multi-container Docker apps in an easy-to-use YAML format. Here is an example:

version: "3.7"

services:

  webapp:
    image: nginx:alpine    
    ports:
      - "80:80"

  db:
    image: postgres:12
    volumes:
      - dbdata:/var/lib/postgresql/data

volumes:
  dbdata:

With a single docker-compose up, it will spin up an Nginx container and PostgreSQL container linked together.

Much more concise than having to run individual docker commands!

Some key advantages of using Compose:

• Simpler app management – No need to manually spin up containers and link together. Declarative setup in YAML.

• Multi-container networking – Containers share networks for easy access between services.

• Environment consistency – Runs the same from dev, test, staging to prod environments.

• Orchestration – Scales up containers across multiple hosts.

And importantly for databases like PostgreSQL, Compose makes volume management easy for persistent storage.

So how does Compose help when running PostgreSQL in particular?

Docker Compose for PostgreSQL

Relational databases have challenging infrastructure needs like:

• Persistent storage
• High availibility
• Backups
• Resource allocation

Docker Compose alleviates these complexities for running PostgreSQL:

• Declarative volume binding ensures data persists across container restarts

• Scaling replicas and load balancing improves availability

• Managing backups reduces risk of data loss

• Dedicating resources (CPU/RAM) prevents noisy neighbor issues

By incorporating all these needs natively into a Compose file, you can setup production-grade PostgreSQL with ease!

Let‘s go through a sample setup.

Sample PostgreSQL Compose File

Here is an example Docker Compose file for PostgreSQL.

version: ‘3.7‘

services:

  postgres:
    image: postgres:12
    ports:
      - 5432:5432
    env_file:
      - postgres.env
    volumes:
      - db-data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "postgres"]
      timeout: 45s
      interval: 10s
      retries: 10
    deploy:  
      resources:
        limits:
          cpus: 2
          memory: 2G
      restart_policy:
        condition: on-failure

  pgadmin:
    image: dpage/pgadmin4
    env_file: 
      - pgadmin.env 
    ports:
      - 8080:80
    depends_on:
      - postgres

volumes:
  db-data:

Breaking this down:

• Base PostgreSQL 12 image
• Persistent volume for /var/lib/postgresql/data
• Environment variables for creds
• Healthchecks to ensure its running
• Resources limits for 2 CPU cores and 2GB RAM
• pgAdmin 4 container for GUI management

This sets up a production-ready PostgreSQL instance with just a few lines!

But deployment needs advance beyond a single container. How do manage more complex environments?

Storage Drivers & IOPS Provisioning

Persistent volumes used in Compose can leverage a variety of storage drivers under the hood.

Based on the infrastructure PostgreSQL runs on, different drivers offer different performance profiles:

| Storage Driver | Infrastructure | Performance Profile |
| --------------------------- | -------------------------------------|-------------------------------------|
| local | Server disks | Lower latency storage |
| nfs | Network attached storage | Nerwork filesystem arxchitecture|   
| efs | AWS Elastic Filesystem | Multi-AZ network filesystem |

And beyond just picking the storage driver, attention needs to be paid to:

• IOPS Provisioning – Picking SSD-backed storage for fast random IOPS performance. A PostgreSQL 10k RPM volume handles 20x more IOPS than 5.4k RPM.

• Throughput – Having enough bandwidth to read data without bottlenecks. This varies across network storage vs local or directly attached disks.

So while Compose makes volume mounting simple, additional consideration to storage performance is vital in production.

Now that we have performant volumes, we next need to make sure data integrity is not compromised…

Backup & Recovery Tools

A well-designed backup & recovery strategy is crucial for data protection and disaster recovery.

The wal-g tool specialized for PostgreSQL can carry out different types of backups depending on your RPO/RTO objectives:

Backup type	Description	RPO + RTO
Full Base Backup	Initial complete snapshot	24h
Continuous Archiving	Ongoing WAL segments archiving	< 5 min RPO, 1h RTO
Differential Backup	Regular incremental deltas	1h RPO, 1h RTO

And these PostgreSQL backups can be orchestrated right within Compose:

services:

  postgres:
    //...

  walg:
    image: store/repo/wal-g:latest  
    env_file:  
      - walg.env
    volumes:
      - archive_location:/backups
    depends_on:
      - postgres
    entrypoint: "./backup.sh" 

Where backup.sh executes the wal-g backup commands monthly full + nightly differential backups.

Integrating these Compose configured backups improves PostgreSQL resiliency!

Monitoring & Logs

In container setups, visibility is key but can be lacking. Compose allows aggregating monitoring and logs across all services.

For metrics, this sample uses Prometheus to poll PostgreSQL metrics:

services:

  postgres_exporter:
    image: postgres_exporter
    depends_on:
      - postgres
    // exports PG metrics  

  prometheus:  
    image: prom/prometheus:v2.0.0
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    // scrapes metrics

And for logging, Filebeat ships PostgreSQL logs:

  filebeat:
    image: elastic/filebeat:6.3.0 
    volumes:
     - /var/lib/docker/containers:/var/lib/docker/containers
     - /var/log/postgresql:/var/log/postgresql
    // ships logs to Elasticsearch    

Centralized monitoring and logging avoids blindspots and offers cross-service visibility.

Now with backups, monitoring, and logging squared away – could this setup work at scale in true production environments?

Running PostgreSQL Compose in Production

For production-grade workloads, exclusively relying on Compose has some limitations:

• Limited cluster/swarm management capabilities
• No autoscaling based on load
• Dropping containers leads to downtime
• Single container failures impact service availability

So while Compose gets you 85% there for running production PostgreSQL, the last 15% requires robust orchestration capabilities.

This is where Kubernetes shines…

Compose vs Kubernetes vs Swarm

For advanced container orchestration needs, Kubernetes is the go-to choice while Swarm has receiving waning support.

Here is a high-level comparison between the three:

	Docker Compose	Docker Swarm	Kubernetes
Orchestration Features	Basic	Minimal	Fully-featured
Persistent Volumes	Yes	Yes	Yes
Multi-host Networking	No	Yes	Yes
Scaling	Manual	Auto	Auto
Health Checks	Yes	Yes	Yes
Dashboard	No	Yes	Yes

The verdict?

• Use Compose for dev, test, CI environments
• Use Kubernetes for staging, production
• Avoid Swarm

PostgreSQL can utilize Kubernetes‘ auto-scaling, load balancing, self-healing and advanced features to run large production workloads.

But operating Kubernetes is an order of magnitude more complex than using Compose. So start simple, then evolve!

Troubleshooting Tips

Here are some common issues faced in production Compose environments and how to troubleshoot them:

Containers crashing randomly

This typically happens when containers exceeds their CPU and memory limits, and the OOM killer terminates them.

Solutions:

• Check if CPU or RAM is spiking via monitoring
• Increase limits allocated in Compose file
• Tune PostgreSQL settings to reduce memory usage

Host filesytem ran out of inodes

Persistent volumes hosted on the server can index all available inodes.

Solutions:

• Use EFS/EBS volumes instead for unlimited inodes
• Periodically cleanup old logs from host volumes

Orderly Failover Did Not Occur Between Primaries

Healthchecks might be incorrectly promoting new primary servers.

Solutions:

• Check promotion settings in Patroni‘s configuration
• Verify health check thresholds not too aggressive

So while everything should run smoothly in Compose, being prepared to track down errors is key!

Conclusion

In closing, Docker Compose provides an excellent platform for running PostgreSQL in both dev and simple production environments.

Compose makes configuring persistence, networking, orchestration and monitoring intuitive and integrated for PostgreSQL. This simplicity abstracts away many complexities traditionally associated with running databases at scale.

For advanced production use cases, graduation to Kubernetes is recommended over time to enable auto-scaling, high availability and effortless maintenance.

Overall, Docker Compose + PostgreSQL is a combination that pairs a robust relational database with simple yet powerful orchestration. Together they enable appliations backed by Postgres to be run anywhere from a developer‘s laptop to scalable multi-cloud production environments!