A Complete Guide To Monitor PostgreSQL With Prometheus and Grafana
This story is a complete guide to monitor PostgreSQL with Prometheus and Grafana to have a clear look at system operations, bottlenecks, and limitations.
Now days service monitoring is a must, and Prometheus is one of the most widely used tools to collect data from different services and applications. On the other hand, Grafana is an open source interactive visualization to produce charts, graphs and even alerts for connected services!
Let’s dive in and build a docker compose setup to monitor PostgreSQL with Prometheus and Grafana.
You can find all files and prepared Grafana dashboard in this github repository.
Prepare PostgreSQL
Let’s create a docker-compose.yml in root directory of our workspace and then we will prepare a PostgreSQL container which is up, healthy and ready! we can write our docker compose file, as below:
Prepare Prometheus & PostgreSQL Exporter
After that, we need to get our Prometheus container up and running. Prometheus allows us to use different service exporters to collect vital information from them.
Now, create a directory inside the workspace called docker and within the docker directory, create another one called prometheus. Place prometheus.yml config file inside of it.
We should put our prometheus exporter configurations in prometheus.yml file to determine some necessary aspects such as data collection interval:
In the meantime, let’s go back to our docker-compose.yml file to define Prometheus and PostgreSQL exporter. The Prometheus container could be as follows:
and PostgreSQL exporter to collect data:
Prepare Grafana
Now, We can use Grafana to use it as a visualization service, as shown below: (Note the volumes and networks sections)
After putting .env file to set our environment variables, we can run docker compose up command in terminal:
We should run docker compose up command to see our services are up and running:
If we head to http://localhost:9090, we will see Prometheus dashboard:
If we head to http://localhost:3000, we will see Grafana dashboard:
If we head to http://localhost:9187, we will see PostgreSQL Exporter server:
Prepare Data Source & Grafana Dashboard
We can connect Grafana and PostgreSQL by defining a new data source through Connections -> Data sources:
Don’t forget the server URL must be set as container name with defined port in this case http://prometheus:9090 because all containers are on the same network and can directly reach each others.
Next, we should navigate to the Dashboards section, where you can define any graphs, charts, counters and etc to monitor important metrics. Even better, you can import dashboards into Grafana. By using provided dashboard in Github repository you’ll be able to monitor CRUD operations, locks, connections, interrupts, processes, memory usage, average load and so much more!
All files and Grafana dashboard to import are available in this Github repository.
Some Important Notices:
- Always use a secure connection and authentication to prevent other users access to your monitoring dashboard
- Do not use PostgreSQL root user to collect data, Always define a new monitoring user with minimum privileges
- Using SSL can be very good and ensure your data more safety
That’s it.
READ MORE:
