AI VOID
DATE: 2026.05.22 00:00 CLEARANCE: PUBLIC

Integrating a Database Service (PostgreSQL)

Docker Docker Compose PostgreSQL Data Persistence Database

// table of contents

▼

Modern applications demand robust data storage. In this chapter, we’ll integrate a PostgreSQL database into our Docker Compose stack, transforming our simple web application into a dynamic system capable of storing and retrieving information persistently. By the end, you’ll have a fully containerized, multi-service application with a reliable database backend, a cornerstone for any production system.

Project Overview: Adding Persistent Data

Our overall project aims to build a production-ready multi-service application using Docker Compose. Until now, our web application has been stateless. This chapter introduces a stateful component: a PostgreSQL database. This allows our application to manage user accounts, store content, or maintain any dynamic state required for its functionality. We will focus on ensuring the database’s data persists across container restarts and updates, a critical aspect for production environments.

Tech Stack: PostgreSQL and Docker Volumes

  • PostgreSQL: We’ve chosen PostgreSQL for its reputation as a powerful, open-source object-relational database system. It offers strong data integrity, advanced features, and high reliability, making it suitable for demanding applications. We’ll use the official postgres Docker image, specifically postgres:16-alpine (checked 2026-05-22, PostgreSQL 16 is the latest stable release), which provides a minimal, secure base.
  • Docker Volumes: Data persistence for databases in containers is achieved through Docker volumes. Volumes are the preferred mechanism for persisting data generated by and used by Docker containers. They are completely managed by Docker, separate from the container’s writable layer, and designed for efficient I/O.
  • Docker Networks: We’ll leverage Docker’s internal networking to allow our web application to communicate securely with the database service without exposing the database port directly to the host machine or public internet.

Build Plan: Integrating the Database

To achieve a persistent and functional database integration, we’ll follow these key steps:

  1. Define the PostgreSQL Service: Add a new db service to our docker-compose.yml, specifying its image, environment variables, and network.
  2. Configure Data Persistence: Attach a named Docker volume to the database service to ensure data is not lost.
  3. Establish Internal Networking: Ensure both the web and database services are on the same custom Docker network for secure communication.
  4. Update Web Application Requirements: Modify the web application’s Dockerfile to include the necessary database client library (e.g., psycopg2-binary for Python).
  5. Connect from the Web Application: Adjust the web application code to use environment variables to connect to the database.

Architecture: Web App with Persistent Database

Our updated architecture introduces a dedicated database container, isolated but networked with our web application. All database files will reside on a Docker volume.

flowchart TD User -->|HTTP Request| Web_App[Web Application Container] Web_App -->|Database Query| DB_Service[PostgreSQL Database Container] DB_Service -->|Stores data| Data_Volume[Docker Volume] subgraph Docker_Network["Docker Network"] Web_App DB_Service end

Key architectural decisions for this milestone:

  • Container Isolation: Each service (web, database) runs in its own isolated container, promoting modularity and easier management.
  • Official Images: Using the postgres:16-alpine official image ensures security, regular updates, and adherence to best practices. The Alpine variant significantly reduces image size, leading to faster pulls and a smaller attack surface.
  • Named Volumes: Docker named volumes (db_data) provide a robust and Docker-managed way to persist database files. This decouples data from the container lifecycle.
  • Internal Network Communication: The app_network ensures that web and db can communicate using service names as hostnames (e.g., db for the database container) within a private, isolated network. This enhances security by preventing direct external access to the database.
  • Environment-based Configuration: Database credentials and connection strings are passed via environment variables, a standard practice for containerized applications.

Step-by-Step Implementation

Let’s modify our docker-compose.yml and web application to integrate PostgreSQL.

1. Defining the PostgreSQL Service

Open your docker-compose.yml file and add a new service named db. We’ll also update the web service to include a placeholder for the database connection string and declare its dependency on db.

# Path: docker-compose.yml
services:
  web:
    build: .
    ports:
      - "8000:8000"
    environment:
      # Placeholder for database connection string, will be updated
      DATABASE_URL: "postgresql://user:password@db:5432/mydatabase"
    depends_on:
      - db # Ensures 'db' service starts before 'web'
    networks:
      - app_network

  db:
    image: postgres:16-alpine # Use specific version and Alpine for smaller size
    environment:
      POSTGRES_DB: mydatabase
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password # Temporary: will be secured later
    volumes:
      - db_data:/var/lib/postgresql/data # Mount a named volume for persistence
    networks:
      - app_network

volumes:
  db_data: # Define the named volume for PostgreSQL data

networks:
  app_network: # Define the custom bridge network
    driver: bridge

Explanation of additions and changes:

  • db: service definition:

    • image: postgres:16-alpine: Specifies the official PostgreSQL Docker image. The alpine tag indicates a minimal, lightweight base image, which is a production best practice for reduced attack surface and faster image pulls.
    • environment:: These variables are used by the postgres image to initialize the database:
      • POSTGRES_DB: The name of the database that will be created on startup.
      • POSTGRES_USER: The username for accessing the database.
      • POSTGRES_PASSWORD: The password for the database user.
      • ⚠️ What can go wrong: Directly hardcoding POSTGRES_PASSWORD in docker-compose.yml is not recommended for production. This is a temporary setup for functionality. We will implement Docker secrets for secure credential management in a future chapter.
    • volumes: - db_data:/var/lib/postgresql/data: This line is crucial for data persistence.
      • db_data: Refers to a named volume defined at the root of docker-compose.yml. Docker manages this volume, storing its data on the host system.
      • /var/lib/postgresql/data: This is the default directory inside the PostgreSQL container where it stores its database files. By mounting db_data to this path, all database changes are written to the persistent volume, ensuring data survives container recreation.
    • networks: - app_network: Connects the db service to our custom app_network. This allows web to resolve db by its service name.

  • web: service updates:

    • environment: DATABASE_URL: "postgresql://user:password@db:5432/mydatabase": A placeholder connection string. The db hostname will resolve to the PostgreSQL container’s IP address within app_network.
    • depends_on: - db: This tells Docker Compose to start the db service before the web service.
      • 🧠 Important: depends_on only guarantees the start order of containers, not that the db service is fully ready to accept connections. We’ll address robust service readiness checks in a later chapter.
    • networks: - app_network: Ensures the web service can communicate with db.

  • Root-level volumes: and networks: definitions:

    • These top-level keys explicitly define the named volume (db_data) and the custom bridge network (app_network). This is the recommended way per the Compose Specification (checked 2026-05-22).
    • ⚡ Quick Note: The Compose Specification recommends not specifying a version field in docker-compose.yml files, as it’s now implicitly handled by the specification itself.

2. Update Web Application Dockerfile (Python Example)

For your web application to connect to PostgreSQL, it needs a database driver. If you’re using Python, this means installing psycopg2-binary.

# Path: Dockerfile (example for a Python application)
# ... previous stages (e.g., build stage for dependencies) ...

# Stage 2: Runtime image
FROM python:3.11-slim-bookworm AS runtime

# Set environment variables for non-root user and Python specifics
ENV PYTHONUNBUFFERED 1
ENV PYTHONDONTWRITEBYTECODE 1

WORKDIR /app

# Copy only necessary files from the build stage or directly
COPY --from=builder /app/requirements.txt /app/requirements.txt
COPY --from=builder /app/your_app.py /app/your_app.py
COPY --from=builder /app/config.py /app/config.py
# ... any other application files ...

# Install runtime dependencies, including the PostgreSQL client
RUN pip install --no-cache-dir -r requirements.txt \
    # Add psycopg2-binary for PostgreSQL connectivity
    && pip install --no-cache-dir psycopg2-binary

# Create a non-root user for security best practices
RUN addgroup --system appgroup && adduser --system --ingroup appgroup appuser
USER appuser

EXPOSE 8000
CMD ["python", "your_app.py"]

Ensure your requirements.txt includes psycopg2-binary:

# Path: requirements.txt
# ... other dependencies ...
psycopg2-binary==2.9.9 # Checked 2026-05-22, latest stable version.

🔥 Optimization / Pro tip: Using a multi-stage build (as hinted by FROM python:3.11-slim-bookworm AS runtime and COPY --from=builder) is a best practice. It helps create smaller, more secure final images by only copying necessary runtime artifacts and avoiding build-time tools. We will cover multi-stage builds in detail in a later chapter.

If you are using a Node.js application, you would install the pg package via npm install pg in your Dockerfile and add it to package.json. The underlying principle remains: install the appropriate database connector for your language/framework.

3. Connecting from the Web Application

Your web application needs to read the DATABASE_URL environment variable and use it to establish a connection. Here’s a minimal Python example:

# Path: your_app.py (example snippet)
import os
import psycopg2 # Make sure this is installed via Dockerfile/requirements.txt

# ... (your existing web app code) ...

def get_db_connection():
    """Establishes a connection to the PostgreSQL database."""
    try:
        # psycopg2 can parse the standard DATABASE_URL format directly
        conn = psycopg2.connect(os.getenv("DATABASE_URL"))
        print("Database connection established successfully.")
        return conn
    except Exception as e:
        print(f"⚠️ Database connection failed: {e}")
        # In a real production application, you'd log this error
        # with a proper logging framework and potentially implement
        # retry logic or graceful degradation.
        return None

# Example usage (e.g., in an application startup routine or API endpoint)
if __name__ == "__main__":
    conn = get_db_connection()
    if conn:
        try:
            with conn.cursor() as cur:
                cur.execute("SELECT 1") # A simple query to test connectivity
                result = cur.fetchone()
                print(f"Database test query result: {result}")
            conn.close()
        except Exception as e:
            print(f"Error during database query: {e}")
    else:
        print("Could not connect to database, skipping test query.")

# ... (rest of your web application logic) ...

🧠 Important: The DATABASE_URL format (postgresql://user:password@db:5432/mydatabase) is a widely adopted standard. Libraries like psycopg2 (Python), pg (Node.js), or go-pg (Go) can parse this string directly, simplifying connection setup. Remember that db resolves to the database container’s internal IP within the Docker network.

Testing & Verification

With the docker-compose.yml and application code updated, let’s verify that PostgreSQL is running correctly and our web application can connect to it.

  1. Build and Run Services: Navigate to your project root (where docker-compose.yml is located) and execute the following command:

    docker compose up --build -d
    • --build: Crucial here, as it rebuilds your web service image to include the psycopg2-binary dependency from your Dockerfile changes.
    • -d: Runs the services in detached mode, allowing them to run in the background.

  2. Check Service Status: Confirm that both your web and db containers are running:

    docker compose ps

    You should see output indicating both services are running and healthy (if health checks were already configured, which we’ll do later):

    NAME                IMAGE               COMMAND                  SERVICE             STATUS              PORTS
myproject-db-1      postgres:16-alpine  "docker-entrypoint.s…"   db                  running             5432/tcp
myproject-web-1     myproject-web       "python your_app.py"     web                 running             0.0.0.0:8000->8000/tcp

  3. Inspect Database Logs: Review the PostgreSQL container logs to ensure it started without errors and is ready to accept connections:

    docker compose logs db

    Look for a line similar to database system is ready to accept connections near the end of the startup sequence.

  4. Connect to PostgreSQL from Host (for advanced verification): You can use docker compose exec to run the psql client directly inside the database container. This is excellent for verifying connectivity and inspecting the database state.

    docker compose exec db psql -U user -d mydatabase
    • docker compose exec db: Executes a command within the db service container.
    • psql -U user -d mydatabase: Invokes the PostgreSQL command-line client, connecting as user to mydatabase.

    If successful, you’ll be dropped into a psql prompt:

    psql (16.2)
Type "help" for help.

mydatabase=# \dt
No relations found. # Expected, as we haven't created any tables yet.
mydatabase=# \q

    Type \q to exit psql. This confirms the database container is healthy and accessible.

  5. Verify Web App Connectivity: If you included the connection test snippet in your your_app.py, check the logs of your web service:

    docker compose logs web

    You should see Database connection established successfully. and Database test query result: (1,) (or similar output from your test query) indicating that the web application successfully connected to PostgreSQL. If you see connection errors, proceed to the “Common Issues” section.

Production Considerations

Integrating a database introduces critical production concerns beyond just getting it to run.

  • Security of Credentials: Hardcoding POSTGRES_PASSWORD is a significant security risk. For production, leverage Docker secrets or a dedicated secret management solution (e.g., HashiCorp Vault, AWS Secrets Manager) to inject credentials securely at runtime. We will cover Docker secrets in the next chapter.

  • Data Backups and Recovery: Docker volumes ensure data persistence, but they are not a backup solution. A comprehensive backup strategy is essential. This might involve:

    • Regularly snapshotting the db_data volume.
    • Using pg_dump to create logical backups that are stored externally.
    • Implementing point-in-time recovery with continuous archiving of WAL (Write-Ahead Log) files.

  • Resource Limits: Databases can be resource-intensive. In production, always define CPU and memory limits for your database container to prevent it from exhausting host resources, which could destabilize other services. You can add a deploy section to your db service:

    # Path: docker-compose.yml (snippet)
db:
  image: postgres:16-alpine
  environment:
    POSTGRES_DB: mydatabase
    POSTGRES_USER: user
    POSTGRES_PASSWORD: password
  volumes:
    - db_data:/var/lib/postgresql/data
  networks:
    - app_network
  deploy: # Define resource constraints for production
    resources:
      limits:
        cpus: '1.0' # Max 1 CPU core
        memory: 2GB # Max 2GB RAM
      reservations:
        cpus: '0.5' # Reserve 0.5 CPU core
        memory: 1GB # Reserve 1GB RAM

    cpus are specified as a fraction of a CPU core (e.g., 0.5 is half a core). memory can use suffixes like B, K, M, G. These values should be tuned based on your application’s actual load and database size.

  • Database Migrations: As your application evolves, its database schema will change. Integrate a database migration tool (e.g., Alembic for Python, Flyway for Java, Knex.js for Node.js) into your deployment pipeline. This ensures schema changes are applied consistently and safely across environments.

  • Monitoring and Alerting: Set up comprehensive monitoring for your database (CPU usage, memory consumption, disk I/O, active connections, query performance, error rates). Implement alerts for critical thresholds or anomalies to ensure operational visibility and proactive issue resolution.

  • Connection Pooling: For high-traffic applications, consider using a connection pooler (like PgBouncer) to manage database connections efficiently, reducing the overhead on the PostgreSQL server.

Common Issues & Solutions

  1. web service fails to connect to db with “Connection refused” or “Host not found”:

    • Issue: The web application started before PostgreSQL was fully initialized and ready to accept connections, or there’s a networking/hostname issue.
    • Solution: While depends_on ensures startup order, it doesn’t guarantee readiness.
      • Immediate Fix: Restart the web service after db is confirmed running: docker compose restart web. Or restart the whole stack: docker compose down && docker compose up --build -d.
      • Long-term Fix: Implement robust health checks (covered in a later chapter) to ensure the web service waits until db is truly healthy.
    • Check:
      • Verify the DATABASE_URL in your web service’s environment section uses db as the hostname and the correct port (5432).
      • Ensure both web and db services are on the same app_network.

  2. Database data is lost after docker compose down:

    • Issue: The named volume db_data was removed. This typically happens if docker compose down --volumes was used, or the volume was never correctly configured.
    • Solution: Ensure volumes: - db_data:/var/lib/postgresql/data is correctly configured for the db service, and db_data: is defined at the root level of docker-compose.yml. To preserve data, always use docker compose down without the --volumes flag.
    • Check: Run docker volume ls to verify that your myproject_db_data volume exists and is managed by Docker.

  3. psql command not found inside container during docker compose exec:

    • Issue: While unlikely with the official postgres image, if you were using a different, highly minimal image, the psql client might not be installed.
    • Solution: The postgres official image does include psql. Double-check the docker compose exec db psql -U user -d mydatabase command for typos. If you were truly using a custom minimal image, you’d need to add postgresql-client to its Dockerfile explicitly.

Summary & Next Step

You have successfully integrated a PostgreSQL database service into your Docker Compose application stack, laying the foundation for a truly dynamic application. You’ve mastered:

  • Defining a database service using the official postgres:16-alpine Docker image.
  • Ensuring critical data persistence with Docker named volumes.
  • Configuring the database via environment variables.
  • Enabling secure, internal communication between services using a custom Docker network.
  • Verifying the database’s operational status and connectivity from your web application.

This milestone significantly enhances our application’s capabilities, making it capable of storing and managing persistent state. The database is now ready to support your application’s data needs reliably.

In the next chapter, we will address the crucial aspect of security by moving beyond hardcoded values to manage sensitive information using environment variables and Docker secrets, making your application more robust and production-ready.

References

This page is AI-assisted and reviewed. It references official documentation and recognized resources where relevant.