Comprehensive Guide to Connecting a Docker Container to a Local PostgreSQL Database

This guide details the process of setting up a connection from a Docker container to a local PostgreSQL database, including configuration adjustments, network settings, volume management, and log inspection for troubleshooting.

Comprehensive Guide for PostgreSQL with Docker

This README provides instructions for setting up a Node.js application with PostgreSQL in two scenarios:

1. Connecting a Node.js application in a Docker container to a PostgreSQL database also running in Docker.
2. Connecting a Dockerized Node.js application to a local PostgreSQL database.

Scenario 1: Dockerized PostgreSQL Database

For development environments, it's common to containerize both the application and the database for ease of setup and portability.

docker-compose-dev.yml Overview

The provided docker-compose-dev.yml file specifies both the Node.js application (app) and the PostgreSQL database (db) services:

version: '3'
services:
  app:
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "5000:5000"
    depends_on:
      - db
    environment:
      - PORT=5000
      - DB_HOST=db
      - DB_USER=postgres
      - DB_NAME=postgres
      - DB_PASSWORD=postgres
      - DB_PORT=5432

  db:
    image: postgres:latest
    restart: on-failure
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres
    ports:
      - "5432:5432"
    volumes:
      - pg-data:/var/lib/postgresql/data
      - ./database.sql:/docker-entrypoint-initdb.d/database.sql

volumes:
  pg-data:

Setup and Execution

1. Ensure Docker and Docker Compose are installed on your development machine.
2. Place your Node.js application code and Dockerfile in the same directory as your docker-compose-dev.yml.
3. Start the services by running:

docker-compose -f docker-compose-dev.yml up --build

This command builds the application image, starts the containers for both the application and PostgreSQL, and sets up a network for them to communicate.

Database Initialization

• The PostgreSQL service is configured to initialize with data from ./database.sql. Place your SQL schema or seed data in this file.

Volume Persistence

• The pg-data volume ensures your database data persists across container restarts.

Scenario 2: Connecting to a Local PostgreSQL Database

For cases where the PostgreSQL database runs locally on your host machine, and you want to connect a Dockerized Node.js application to it.

Prerequisites

• Docker and Docker Compose installed on your machine.
• PostgreSQL installed and running locally.
• Basic familiarity with Docker, Docker Compose, and PostgreSQL administration.

Configuring PostgreSQL for Docker Connectivity

Step 1: Adjust postgresql.conf

Ensure PostgreSQL listens on all interfaces:

listen_addresses = '*'

Step 2: Modify pg_hba.conf for Network Range

Allow a broader range of IP addresses to enable connections from Docker containers:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5

For a more secure setup, replace 0.0.0.0/0 with your Docker subnet, e.g., 172.17.0.0/16.

Step 3: Restart PostgreSQL

Apply the configuration changes:

sudo systemctl restart postgresql

Docker Configuration for Database Connectivity

Using host.docker.internal

For Docker containers to connect to the host machine:

environment:
  DB_HOST: host.docker.internal

Use a More Inclusive Network Range in pg_hba.conf

If experiencing connectivity issues, ensure your pg_hba.conf includes the network range used by Docker containers.

Use Docker's Network Features

Custom Bridge Network

Create a custom bridge network for predictable IP assignments:

docker network create --driver bridge --subnet=172.25.0.0/16 my_bridge_network

Connect your containers to this network and adjust pg_hba.conf to allow connections from 172.25.0.0/16.

Use Host Networking

For direct access to the host network, bypassing the need for port mappings:

network_mode: "host"

Note: This method makes host.docker.internal unnecessary, as containers can access the host using localhost or 127.0.0.1.

Volume Persistence

Ensure data persistence for PostgreSQL by defining a volume in your docker-compose.yml:

volumes:
  - pg-data:/var/lib/postgresql/data

This setup retains your database data across container restarts and rebuilds.

Inspecting the Logs

If encountering issues, inspect Docker and PostgreSQL logs for errors:

• Docker Logs: docker logs <container_id>
• PostgreSQL Logs: Located within the PostgreSQL data directory or as specified in postgresql.conf.

Security Considerations

• Utilize strong, unique passwords for database access.
• Consider network security policies and firewall settings, especially when exposing PostgreSQL to wider networks.
• For production deployments, follow best practices for securing Docker containers and PostgreSQL databases.