Nice tools for linux otel and monitoring

Monitoring PostgreSQL services with Prometheus and Grafana involves using a PostgreSQL Exporter to expose metrics, configuring Prometheus to scrape these metrics, and visualizing them in Grafana. Here’s a detailed step-by-step guide:

1. Install PostgreSQL Exporter

The PostgreSQL Exporter exposes PostgreSQL metrics in a Prometheus-compatible format.

1.1. Download the PostgreSQL Exporter

• For Linux: wget https://github.com/prometheus-community/postgres_exporter/releases/latest/download/postgres_exporter-linux-amd64 -O postgres_exporter chmod +x postgres_exporter sudo mv postgres_exporter /usr/local/bin/

1.2. Create a Monitoring User in PostgreSQL

The exporter requires a user with sufficient privileges to read PostgreSQL metrics.

• Connect to PostgreSQL as a superuser: psql -U postgres
• Create a monitoring user and assign appropriate roles: CREATE USER prometheus WITH PASSWORD 'password'; ALTER USER prometheus SET SEARCH_PATH TO prometheus,pg_catalog; GRANT CONNECT ON DATABASE your_database TO prometheus; GRANT USAGE ON SCHEMA pg_catalog TO prometheus; GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO prometheus; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pg_catalog TO prometheus;
• If needed for additional schemas or objects: GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO prometheus;

1.3. Run the PostgreSQL Exporter

Start the exporter with the PostgreSQL connection details:

postgres_exporter --web.listen-address=:9187 --web.telemetry-path=/metrics --extend.query-path=queries.yaml --data-source-name="postgresql://prometheus:password@localhost:5432/?sslmode=disable"

Alternatively, use a systemd service for automatic startup.

2. Configure Prometheus to Scrape PostgreSQL Metrics

2.1. Update prometheus.yml

Add the PostgreSQL Exporter as a scrape target:

scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['<exporter_host>:9187']  # Replace <exporter_host> with the actual hostname

2.2. Restart Prometheus

Apply the configuration changes by restarting Prometheus:

systemctl restart prometheus

3. Import PostgreSQL Dashboards into Grafana

3.1. Add Prometheus as a Data Source in Grafana

1. Log in to Grafana (http://<grafana_host>:3000/).
2. Navigate to Configuration -> Data Sources -> Add data source.
3. Select Prometheus and configure the URL (e.g., http://<prometheus_host>:9090).

3.2. Import a Pre-Built PostgreSQL Dashboard

1. Navigate to Dashboard -> Import in Grafana.
2. Use a dashboard ID from the Grafana community (e.g., 9628 for a comprehensive PostgreSQL monitoring dashboard).
3. Follow the prompts to select the Prometheus data source.

4. Key Metrics to Monitor

The PostgreSQL Exporter provides several useful metrics:

• Database Connection Metrics:
  • pg_stat_activity_count: Total active connections.
  • pg_database_size_bytes: Size of the database in bytes.
• Query Performance Metrics:
  • pg_stat_user_tables_seq_scan: Sequential scans per table.
  • pg_stat_user_indexes_idx_scan: Index scans per table.
• Replication Metrics:
  • pg_stat_replication: Replication lag and state.
• Locks and Deadlocks:
  • pg_locks: Number of locks held.
  • pg_stat_database_deadlocks: Total deadlocks detected.
• Cache Performance:
  • pg_stat_database_blks_hit: Cache hit ratio.
• Custom Queries: Extend monitoring by creating a queries.yaml file for custom metrics: pg_up: query: "SELECT 1" metrics: - name: pg_up usage: "GAUGE" description: "PostgreSQL is running"

5. Configure Alerts

5.1. Prometheus Alert Rules

Add alert rules for critical metrics (e.g., high connection count):

groups:
  - name: postgresql_alerts
    rules:
      - alert: HighConnectionCount
        expr: pg_stat_activity_count > 100
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "High number of connections"
          description: "PostgreSQL connection count is above 100 for the past 5 minutes."

Link this rule file in prometheus.yml under rule_files.

5.2. Grafana Alerts

Configure alerts in Grafana dashboards by defining thresholds for metrics.

6. Verify Setup

• Check metrics in Prometheus at http://<prometheus_host>:9090/targets.
• View PostgreSQL dashboards in Grafana for visual insights.
• Confirm alerting mechanisms are functional.