Monitoring databases is a crucial part of maintaining system health and performance. In this post, I'll guide you through setting up PostgreSQL in Docker, connecting it to Grafana, and monitoring database metrics effectively.
Why Use Docker for PostgreSQL and Grafana?
Docker simplifies the deployment and management of applications, providing an isolated environment for PostgreSQL and Grafana. By using Docker, you can ensure that both applications run seamlessly on any platform, making your environment consistent.
Approach 1: Using network_mode: host (Linux Only)
Step 1: Run PostgreSQL with host Network
Using the host network mode means that both PostgreSQL and Grafana share the same network interface as the host machine. Here’s how you can run PostgreSQL in Docker:
docker run -d --name postgres \
--network host \
-e POSTGRES_USER=admin \
-e POSTGRES_PASSWORD=admin \
-e POSTGRES_DB=mydb \
postgres
Step 2: Run Grafana with host Network
After running PostgreSQL, start Grafana with the host network:
docker run -d --name grafana_container \
--network host \
-e GF_SECURITY_ADMIN_PASSWORD=admin \
grafana/grafana
Step 3: Configure Data Source in Grafana
In Grafana’’s configuration for PostgreSQL:
Host:
localhost:5432User:
adminPassword:
adminDatabase:
mydbTLS/SSL Mode:
disable
Click save and test connection
Now, Grafana and PostgreSQL share the host’s network interface.
Approach 2: Using a Custom Bridge Network (Recommended)
A bridge network is the default Docker network driver, providing network isolation while allowing containers to communicate with each other.
Step 1: Create a Docker Network
To start, create a custom bridge network:
docker network create my_custom_network
Step 2: Run PostgreSQL in the Custom Network
docker run -d --name postgres \
--network my_custom_network \
-e POSTGRES_USER=admin \
-e POSTGRES_PASSWORD=admin \
-e POSTGRES_DB=mydb \
postgres
Step 3: Run Grafana in the Same Network
Make sure Grafana is connected to the same network:
bashCopyEditdocker run -d --name grafana_container \
--network my_custom_network \
-e GF_SECURITY_ADMIN_PASSWORD=admin \
grafana/grafana
Step 4: Configure Data Source in Grafana
In Grafana, configure the PostgreSQL data source:
Host:
postgres_container:5432User:
adminPassword:
adminDatabase:
mydb
Now, both PostgreSQL and Grafana can communicate with each other within the custom bridge network.
Note:
By using Docker’s host or bridge networking modes, you can easily set up PostgreSQL and Grafana in a containerized environment. The host mode provides a simpler configuration but lacks isolation, while the bridge mode offers better isolation and is platform-independent. The choice depends on your specific use case and environment.
Adding data into Postgres
For simplicity, we used the 1st approach for demo purposes.
Now, We have created a container for PostgreSQL let’s connect and create a table users and some sample data.
Connect to PostgreSQL: Connect to PostgreSQL using psql inside the container:
docker exec -it postgres psql -U admin -d mydb
This command will switch to the MySQL prompt.
Create users Table Schema
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
login_time TIME NOT NULL,
is_active BOOLEAN DEFAULT TRUE
);
Insert Sample Data into table
INSERT INTO users (name, email, last_login, login_time, is_active) VALUES
('Sachin', 'sachin@example.com', '2025-02-08 10:00:00', '10:00:00', TRUE),
('John Doe', 'john@example.com', '2025-02-08 17:00:00', '17:00:00', FALSE),
('Alice Smith', 'alice@example.com', '2025-02-08 14:30:00', '14:30:00', TRUE),
('Bob Johnson', 'bob@example.com', '2025-02-08 09:45:00', '09:45:00', TRUE),
('Emma Watson', 'emma@example.com', '2025-02-08 10:15:00', '10:15:00', TRUE),
('Liam Brown', 'liam@example.com', '2025-02-08 12:30:00', '12:30:00', TRUE),
('Olivia Green', 'olivia@example.com', '2025-02-08 15:00:00', '15:00:00', FALSE),
('Noah Wilson', 'noah@example.com', '2025-02-08 18:45:00', '18:45:00', TRUE),
('Ava Lee', 'ava@example.com', '2025-02-08 07:30:00', '07:30:00', TRUE),
('James Taylor', 'james@example.com', '2025-02-08 11:00:00', '11:00:00', TRUE),
('Sophia Harris', 'sophia@example.com', '2025-02-08 16:00:00', '16:00:00', FALSE),
('William Martin', 'william@example.com', '2025-02-08 20:00:00', '20:00:00', TRUE),
('Mia Clark', 'mia@example.com', '2025-02-08 08:00:00', '08:00:00', TRUE),
('Benjamin Lewis', 'benjamin@example.com', '2025-02-08 06:30:00', '06:30:00', FALSE),
('Charlotte Walker', 'charlotte@example.com', '2025-02-08 10:45:00', '10:45:00', TRUE),
('Ethan Hall', 'ethan@example.com', '2025-02-08 21:30:00', '21:30:00', TRUE),
('Amelia Allen', 'amelia@example.com', '2025-02-08 05:00:00', '05:00:00', TRUE),
('Michael Scott', 'michael@example.com', '2025-02-08 12:15:00', '12:15:00', TRUE),
('Isabella King', 'isabella@example.com', '2025-02-08 23:00:00', '23:00:00', FALSE),
('Daniel White', 'daniel@example.com', '2025-02-08 14:45:00', '14:45:00', TRUE);
Query our created table select * from users;
Create a Dashboard in Grafana
Go to Dashboards > New Dashboard.
Add a new Panel and select PostgreSQL as the data source.
Write an SQL query, such as:
SELECT id, name FROM users;
Choose a visualization type (e.g., Table, Graph).
Click Save.
Monitor PostgreSQL Performance:Optional
You can use pg_stat_statements or pgmetrics for monitoring.
- Enable
pg_stat_statementsin PostgreSQL:
CREATE EXTENSION pg_stat_statements;
- Query slow queries:
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
- Add these queries to Grafana for real-time monitoring.
Now you have a working setup to run a PostgreSQL container, query data inside it, and visualize it using Grafana. 🚀 Let me know if you need more details!
Issues faced:
Can’t connect Postgres container with Grafana data source.
References: https://community.grafana.com/t/dial-tcp-127-0-0-1-connect-connection-refused/13071/17
To address this issue, Enabled network mode as ‘host’