Connect Grafana to Postgres with Docker Compose

Learn how to connect Grafana to Postgres with Docker Compose using provisioning. We will go over provisioning, container healthchecks, and container configuration.

Table of Contents 📖

• Grafana and Postgres
• Environment Variables
• The Postgres Datasource
• Initialization Script
• Docker Compose
• Running the Program

Grafana and Postgres

Grafana has a built in Postgres data source plugin for querying and visualizing data from Postgres. Even better, Grafana's provisioning system allows us to configure data sources with configuration files.

Environment Variables

PROJECT_NAME=postgres-grafana

GRAFANA_CONTAINER_NAME=g-c
GRAFANA_PORT=3000
GF_SECURITY_ADMIN_USER=wittcode
GF_SECURITY_ADMIN_PASSWORD=subscribe

POSTGRES_CONTAINER_NAME=p-c
POSTGRES_PORT=5432
POSTGRES_USER=wittcepter
POSTGRES_PASSWORD=theBestChromeExtension
POSTGRES_DB=my_db

The keys of most of these environment variables are required as they are used by the Grafana and Postgres images under the hood for configuration.

• GF_SECURITY_ADMIN_USER - Sets the Grafana admin username.
• GF_SECURITY_ADMIN_PASSWORD - Sets the Grafana admin password.
• POSTGRES_USER - Sets the Postgres username.
• POSTGRES_PASSWORD - Sets the Postgres password.
• POSTGRES_DB - Sets the default Postgres database name.

The Postgres Datasource

Grafana offers configuration through provisioning. Provisioning is when you push configurations into a Grafana instance with conifugration files. The following file will create a Postgres datasource inside the Grafana container.

apiVersion: 1

datasources:
  # The datasource name. How it is refered to in panels and queries.
  - name: Postgres
    # Sets the datasource type
    type: postgres
    # URL to the datasource, including the port
    url: ${POSTGRES_CONTAINER_NAME}:${POSTGRES_PORT}
    # Database user's login/username
    user: ${POSTGRES_USER}
    # Fields to encrypt before storing in jsonData. This data is encrypted with a secret key from the Grafana configuration.
    secureJsonData:
      # Database user's password
      password: ${POSTGRES_PASSWORD}
    # Fields to convert to JSON and store in jsonData.
    jsonData:
      # Sets the database name
      database: ${POSTGRES_DB}
      # Disable SSL mode
      sslmode: 'disable'
      # Max number of open connections to DB (default is 100)
      maxOpenConns: 100
      # Max number of connections in the idle connection pool (default is 100)
      maxIdleConns: 100
      # Max amount of time in seconds a connection may be reused (default is 14400/4 hours)
      connMaxLifetime: 14400
      # Postgres version as a number (903 = 9.3, 1000 = 10)
      postgresVersion: 1600
      # When true Grafana will create a time-series database built as a PostgresSQL extension
      timescaledb: false

Initialization Script

CREATE TABLE extension (
  extension_id SERIAL PRIMARY KEY,
  extension_name VARCHAR(255) NOT NULL,
  extension_email VARCHAR(255) UNIQUE NOT NULL
);

INSERT INTO extension (extension_name, extension_email) VALUES
('WittCepter', 'wittcepter@a.com');

INFO: Initialization scripts are files that are executed by Postgres when the container starts.

Docker Compose

The code below spins up both the Grafana and Postgres containers. Note that the volumes commented out are for data persistence. In this demonstration we will not use persistence. You can uncomment them to use persistence.

name: ${PROJECT_NAME}

services:

  grafana:
    pull_policy: always
    image: grafana/grafana
    container_name: ${GRAFANA_CONTAINER_NAME}
    env_file: .env
    volumes:
      - ./datasources.yml:/etc/grafana/provisioning/datasources/datasources.yml
      # - grafana_data:/var/lib/grafana
    ports:
      - ${GRAFANA_PORT}:${GRAFANA_PORT}
    depends_on:
      database:
        condition: service_healthy

  database:
    pull_policy: always
    image: postgres:16-alpine
    container_name: ${POSTGRES_CONTAINER_NAME}
    env_file: .env
    ports:
      - ${POSTGRES_PORT}:${POSTGRES_PORT}
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
      # - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
      start_period: 0s
      interval: 5s
      timeout: 5s
      retries: 5

# volumes:
#   grafana_data:
#     name: grafana_data
#   postgres_data:
#     name: postgres_data

INFO: Our Grafana container will only start up when the Postgres container is healthy.

Running the Program

To run the program, execute the following command:

docker compose up

Now visit localhost:3000 and enter the Grafana username and password. Then navigate to http://localhost:3000/connections/datasources and inspect the Postgres datasource. We can click "Explore data" and enter a query like the following:

select * from extension