MONITORING POSTGRESQL WITH GRAFANA AND PROMETHEUS IN DOCKER
6 min read
pgBackRest Exporter Dashboard
Dashboard works with pgbackrest_exporter
Grafana dashboard for pgbackrest_exporter
In my previous article I setup an environment including grafana and prometheus in my docker Virtualbox VM, you can find details about my setup from this link.
In this article I start to monitor my Postgresql Database with prometheus and graphana which is on the same docker host.
This is the databases found in my Postgresql:
I am using postgres-exporter from prometheus-community, which can be found from this link.
The following quickstart provides setup instructions and preconfigured dashboards and alerting rules for the Postgres Exporter.
Let’s start with running docker image for postgresql-exporter
alcalab@wolf:~$ sudo docker run --net host -d -e DATA_SOURCE_NAME="postgresql://postgres:1qaz2wsx@@localhost:5432/postgres?sslmode=disable" quay.io/prometheuscommunity/postgres-exporter:v0.11.0
6505d1ec15f0b4680d6c5fa3f8774a0d881859c9c6fc7d681342bce7038fe1b9Now I have postgresql-exporter run as a docker container
alcalab@wolf:~$ sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
6505d1ec15f0 quay.io/prometheuscommunity/postgres-exporter:v0.11.0 "/bin/postgres_expor…" 38 minutes ago Up 38 minutes intelligent_swanson
afd21974a135 grafana/grafana "/run.sh" 11 days ago Up 2 hours 0.0.0.0:3000->3000/tcp, :::3000->3000/tcp stoic_neumann2
d380f074ee78 quay.io/prometheus/node-exporter "/bin/node_exporter" 11 days ago Up 2 hours confident_cartwright2
05552735f5ad prom/prometheus "/bin/prometheus --c…" 11 days ago Up 46 minutes 0.0.0.0:9090->9090/tcp, :::9090->9090/tcp jovial_spence2
41a816217096 postgres:15.2 "docker-entrypoint.s…" 2 months ago Up 2 hours 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp postgresql1Here is my network configs for docker containers:
alcalab@wolf:~$ sudo docker inspect 6505d1ec15f0 -f "{{json .NetworkSettings.Networks }}"
{"host":{"IPAMConfig":null,"Links":null,"Aliases":null,"NetworkID":"16d7dce47598bcc1791088e76b5fcc4395c281879da1ceaa2823cf7b0d6acbef","EndpointID":"dfbc1f33b0db770a5a1ca99e50647bd031f1e9ef92afb34b0ec0e7812199fd48","Gateway":"","IPAddress":"","IPPrefixLen":0,"IPv6Gateway":"","GlobalIPv6Address":"","GlobalIPv6PrefixLen":0,"MacAddress":"","DriverOpts":null}}
alcalab@wolf:~$ sudo docker inspect afd21974a135 -f "{{json .NetworkSettings.Networks }}"
{"bridge":{"IPAMConfig":null,"Links":null,"Aliases":null,"NetworkID":"6ebcbc3202942ed47b36f1ce219cb735a725dd8c41fd48ccfd182eba718daeb1","EndpointID":"deeac5d398c8bc58ce8aa2e0c0bd2b8e3b811b79234830dee0e5c7a96aef75c1","Gateway":"172.17.0.1","IPAddress":"172.17.0.2","IPPrefixLen":16,"IPv6Gateway":"","GlobalIPv6Address":"","GlobalIPv6PrefixLen":0,"MacAddress":"02:42:ac:11:00:02","DriverOpts":null}}
alcalab@wolf:~$ sudo docker inspect d380f074ee78 -f "{{json .NetworkSettings.Networks }}"
{"host":{"IPAMConfig":null,"Links":null,"Aliases":null,"NetworkID":"16d7dce47598bcc1791088e76b5fcc4395c281879da1ceaa2823cf7b0d6acbef","EndpointID":"d152d9975076c5f7f2607c4675c1183f04354d0748b88b7fb29d87fc558e5199","Gateway":"","IPAddress":"","IPPrefixLen":0,"IPv6Gateway":"","GlobalIPv6Address":"","GlobalIPv6PrefixLen":0,"MacAddress":"","DriverOpts":null}}
alcalab@wolf:~$ sudo docker inspect 05552735f5ad -f "{{json .NetworkSettings.Networks }}"
{"bridge":{"IPAMConfig":null,"Links":null,"Aliases":null,"NetworkID":"6ebcbc3202942ed47b36f1ce219cb735a725dd8c41fd48ccfd182eba718daeb1","EndpointID":"bbf6067e1b7959a68880fdb9e3bea855914c9f8d4c4bb1347959199375767ff8","Gateway":"172.17.0.1","IPAddress":"172.17.0.5","IPPrefixLen":16,"IPv6Gateway":"","GlobalIPv6Address":"","GlobalIPv6PrefixLen":0,"MacAddress":"02:42:ac:11:00:05","DriverOpts":null}}
alcalab@wolf:~$ sudo docker inspect 41a816217096 -f "{{json .NetworkSettings.Networks }}"
{"bridge":{"IPAMConfig":{},"Links":null,"Aliases":[],"NetworkID":"6ebcbc3202942ed47b36f1ce219cb735a725dd8c41fd48ccfd182eba718daeb1","EndpointID":"582cbd04172bec16f6799260a0510bb72a2129de0a51dba5e24228d0e4d1ebfb","Gateway":"172.17.0.1","IPAddress":"172.17.0.4","IPPrefixLen":16,"IPv6Gateway":"","GlobalIPv6Address":"","GlobalIPv6PrefixLen":0,"MacAddress":"02:42:ac:11:00:04","DriverOpts":{}}}Add postgresql-exporter to prometheus.yaml and restart prometheus container.10.0.2.15 is the IP of my Oracle VirtualBox VM
alcalab@wolf:~$ cat prometheus.yml
global:
scrape_interval: 15s
scrape_configs:
- job_name: 'prometheus'
static_configs:
- targets: ['localhost:9090']
- job_name: 'node-exporter'
static_configs:
- targets: ['10.0.2.15:9100']
- job_name: 'docker'
static_configs:
- targets: ['10.0.2.15:9323']
- job_name: 'postgres-exporter'
static_configs:
- targets: ['10.0.2.15:9187']
alcalab@wolf:~$ sudo docker stop jovial_spence2
alcalab@wolf:~$ sudo docker start jovial_spence2You should see postgresql-exporter UP from prometheus targets:
Prometheus data source is already added to Grafana, now it is time to import PostgreSQL Dashboard for Prometheus.
For this, Login to Grafana, I forgot password for admin , so If you want to reset admin password please follow below steps:
# First login to grafana docker container
alcalab@wolf:~$ sudo docker exec -it afd21974a135 bash
afd21974a135:/usr/share/grafana$ grafana-cli admin reset-admin-password admin
Deprecation warning: The standalone 'grafana-cli' program is deprecated and will be removed in the future. Please update all uses of 'grafana-cli' to 'grafana cli'
INFO [12-28|18:42:27] Starting Grafana logger=settings version= commit= branch= compiled=1970-01-01T00:00:00Z
INFO [12-28|18:42:27] Config loaded from logger=settings file=/usr/share/grafana/conf/defaults.ini
INFO [12-28|18:42:27] Config overridden from Environment variable logger=settings var="GF_PATHS_DATA=/var/lib/grafana"
INFO [12-28|18:42:27] Config overridden from Environment variable logger=settings var="GF_PATHS_LOGS=/var/log/grafana"
INFO [12-28|18:42:27] Config overridden from Environment variable logger=settings var="GF_PATHS_PLUGINS=/var/lib/grafana/plugins"
INFO [12-28|18:42:27] Config overridden from Environment variable logger=settings var="GF_PATHS_PROVISIONING=/etc/grafana/provisioning"
INFO [12-28|18:42:27] Target logger=settings target=[all]
INFO [12-28|18:42:27] Path Home logger=settings path=/usr/share/grafana
INFO [12-28|18:42:27] Path Data logger=settings path=/var/lib/grafana
INFO [12-28|18:42:27] Path Logs logger=settings path=/var/log/grafana
INFO [12-28|18:42:27] Path Plugins logger=settings path=/var/lib/grafana/plugins
INFO [12-28|18:42:27] Path Provisioning logger=settings path=/etc/grafana/provisioning
INFO [12-28|18:42:27] App mode production logger=settings
INFO [12-28|18:42:28] Connecting to DB logger=sqlstore dbtype=sqlite3
INFO [12-28|18:42:28] Starting DB migrations logger=migrator
INFO [12-28|18:42:28] migrations completed logger=migrator performed=0 skipped=517 duration=234.651µs
INFO [12-28|18:42:28] Envelope encryption state logger=secrets enabled=true current provider=secretKey.v1
Admin password changed successfully ✔Now password is resetted to admin again, you can change with new password when try to login with admin/admin:
For importing Postgresql dashboard
You have to type 9628 for JSON code https://grafana.com/grafana/dashboards/9628
Now Postgresql dashboard is ready:
Now let’s do some load tests using pgbench.
Connecting to our docker postgresql server
alcalab@wolf:~$ sudo docker exec -it postgresql1 bash
root@41a816217096:/# which pgbench
/usr/bin/pgbench
# If pgbench is not found you need to install postgresql-contrib
root@41a816217096:/# su - postgres
postgres@41a816217096:~$ psql
psql (15.2 (Debian 15.2-1.pgdg110+1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
----------------+----------+----------+------------+------------+------------+-----------------+-----------------------
baseball | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
dvdrental | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
exercises | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
samples_nullif | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
school | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
teams | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
örnekler | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
(10 rows)
postgres=# CREATE DATABASE deneme;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
----------------+----------+----------+------------+------------+------------+-----------------+-----------------------
baseball | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
deneme | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
dvdrental | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
exercises | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
samples_nullif | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
school | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
teams | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
örnekler | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
(11 rows)
postgres=# \q
postgres@41a816217096:~$ pgbench -i -s 50 deneme
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
5000000 of 5000000 tuples (100%) done (elapsed 4.43 s, remaining 0.00 s)
vacuuming...
creating primary keys...
pgbench: error: query failed: ERROR: could not extend file "base/17897/17918": wrote only 4096 of 8192 bytes at block 9834
HINT: Check free disk space.
pgbench: detail: Query was: alter table pgbench_accounts add primary key (aid)
# I have not enough disk space but let's continueNow from grafana you should see the database named “deneme” stats
Conclusion:
Note that depending on its configuration, Postgres Exporter may collect and publish far more metrics than this default set. To learn more about configuring Postgres Exporter and toggling its collectors, please see the Postgres Exporter Github Repo
Tidak ada komentar:
Posting Komentar