Postgres Database Monitoring using Grafana Dashboard using template
1. We need to make sure that Grafana is installed already.
2. We need following postgresql dashboard to be imported in Grafana.
https://grafana.com/dashboards/4164
3. Please download and install influxDB in postgresql server to be added in Grafana monitoring.
wget https://dl.influxdata.com/influxdb/releases/influxdb-1.7.4.x86_64.rpm
sudo yum localinstall influxdb-1.7.4.x86_64.rpm
4. Please configure and restart influxDB
Please change influxdb.conf
/etc/influxdb/influxdb.conf
We need to enable it
# Determines whether user authentication is enabled over HTTP/HTTPS.
auth-enabled = true
Please restart influxdb
# systemctl start influxdb
# systemctl status influxdb
# systemctl enable influxdb
5. Please configure and restart Telegraf
Please create telegraf user and create the database in influxdb
#curl -XPOST “http://localhost:8086/query” –data-urlencode “q=CREATE USER telegraf WITH PASSWORD ‘metricsmetricsmetricsmetrics’ WITH ALL PRIVILEGES”
#influx -username ‘telegraf’ -password ‘metricsmetricsmetricsmetrics’
[root@xxx ~]# influx -username ‘telegraf’ -password ‘metricsmetricsmetricsmetrics’
Connected to http://localhost:8086 version 1.7.4
InfluxDB shell version: 1.7.4
Enter an InfluxQL query
>create database telegraf
> SHOW DATABASES
name: databases
name
—-
_internal
Telegraf
Please install telegraf now
wget https://dl.influxdata.com/telegraf/releases/telegraf-1.9.4-1.x86_64.rpm
sudo yum localinstall telegraf-1.9.4-1.x86_64.rpm
- Please configure telegraf.conf to enable postgresql monitoring. You need to change IP to postgres server accordingly.
/etc/telegraf/telegraf.conf
###############################################################################
# OUTPUT PLUGINS #
###############################################################################
# Configuration for sending metrics to InfluxDB
[[outputs.influxdb]]
## The full HTTP or UDP URL for your InfluxDB instance.
##
## Multiple URLs can be specified for a single cluster, only ONE of the
## urls will be written to each interval.
# urls = [“unix:///var/run/influxdb.sock”]
# urls = [“udp://127.0.0.1:8089”]
urls = [“http://10.x.x.x:8086”]
## The target database for metrics; will be created as needed.
database = “telegraf”
## If true, no CREATE DATABASE queries will be sent. Set to true when using
## Telegraf with a user without permissions to create databases or when the
## database already exists.
# skip_database_creation = false
## Name of existing retention policy to write to. Empty string writes to
## the default retention policy. Only takes effect when using HTTP.
# retention_policy = “”
## Write consistency (clusters only), can be: “any”, “one”, “quorum”, “all”.
## Only takes effect when using HTTP.
write_consistency = “any”
## Timeout for HTTP messages.
# timeout = “5s”
## HTTP Basic Auth
username = “telegraf”
password = “metricsmetricsmetricsmetrics”
# # Read metrics from one or many postgresql servers
[[inputs.postgresql]]
# ## specify address via a url matching:
# ## postgres://[pqgotest[:password]]@localhost[/dbname]\
# ## ?sslmode=[disable|verify-ca|verify-full]
# ## or a simple string:
# ## host=localhost user=pqotest password=… sslmode=… dbname=app_production
# ##
# ## All connection parameters are optional.
# ##
# ## Without the dbname parameter, the driver will default to a database
# ## with the same name as the user. This dbname is just for instantiating a
# ## connection with the server and doesn’t restrict the databases we are trying
# ## to grab metrics for.
# ##
address = “host=10.x.x.x user=pg password=pgdbpass dbname=postgres port=1523 sslmode=disable”
- Please restart telegraf now
#telegraf
6. Now we need to login to Grafana and create 3 data sources.
For influxDB following should be data source configuration
- PostgreSQL Master node configuration
- PostgreSQL Slave node configuration
- Please import the dashboard now
