{"id":752,"date":"2024-12-09T08:07:30","date_gmt":"2024-12-09T14:07:30","guid":{"rendered":"https:\/\/kop.lat\/blog\/?p=752"},"modified":"2024-12-15T13:02:58","modified_gmt":"2024-12-15T19:02:58","slug":"prometheus-and-grafana-for-postgresql","status":"publish","type":"post","link":"https:\/\/kop.lat\/blog\/prometheus-and-grafana-for-postgresql\/","title":{"rendered":"Nice tools for linux otel and monitoring"},"content":{"rendered":"\n

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

\n\n\n\n

1. Install PostgreSQL Exporter<\/strong><\/h3>\n\n\n\n

The PostgreSQL Exporter exposes PostgreSQL metrics in a Prometheus-compatible format.<\/p>\n\n\n\n

1.1. Download the PostgreSQL Exporter<\/strong><\/h4>\n\n\n\n
    \n
  • 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\/<\/code><\/li>\n<\/ul>\n\n\n\n

    1.2. Create a Monitoring User in PostgreSQL<\/strong><\/h4>\n\n\n\n

    The exporter requires a user with sufficient privileges to read PostgreSQL metrics.<\/p>\n\n\n\n

      \n
    • Connect to PostgreSQL as a superuser: psql -U postgres<\/code><\/li>\n\n\n\n
    • 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;<\/code><\/li>\n\n\n\n
    • If needed for additional schemas or objects: GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO prometheus;<\/code><\/li>\n<\/ul>\n\n\n\n

      1.3. Run the PostgreSQL Exporter<\/strong><\/h4>\n\n\n\n

      Start the exporter with the PostgreSQL connection details:<\/p>\n\n\n\n

      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\"\n<\/code><\/pre>\n\n\n\n
      Alternatively, use a systemd service for automatic startup.<\/p>\n\n\n\n
      \n\n\n\n
      2. Configure Prometheus to Scrape PostgreSQL Metrics<\/strong><\/h3>\n\n\n\n
      2.1. Update prometheus.yml<\/code><\/strong><\/h4>\n\n\n\n
      Add the PostgreSQL Exporter as a scrape target:<\/p>\n\n\n\n
      scrape_configs:\n  - job_name: 'postgresql'\n    static_configs:\n      - targets: ['<exporter_host>:9187']  # Replace <exporter_host> with the actual hostname\n<\/code><\/pre>\n\n\n\n
      2.2. Restart Prometheus<\/strong><\/h4>\n\n\n\n
      Apply the configuration changes by restarting Prometheus:<\/p>\n\n\n\n
      systemctl restart prometheus\n<\/code><\/pre>\n\n\n\n
      \n\n\n\n
      3. Import PostgreSQL Dashboards into Grafana<\/strong><\/h3>\n\n\n\n
      3.1. Add Prometheus as a Data Source in Grafana<\/strong><\/h4>\n\n\n\n
        \n
      1. Log in to Grafana (http:\/\/<grafana_host>:3000\/<\/code>).<\/li>\n\n\n\n
      2. Navigate to Configuration<\/strong> -> Data Sources<\/strong> -> Add data source<\/strong>.<\/li>\n\n\n\n
      3. Select Prometheus<\/strong> and configure the URL (e.g., http:\/\/<prometheus_host>:9090<\/code>).<\/li>\n<\/ol>\n\n\n\n
        3.2. Import a Pre-Built PostgreSQL Dashboard<\/strong><\/h4>\n\n\n\n
          \n
        1. Navigate to Dashboard<\/strong> -> Import<\/strong> in Grafana.<\/li>\n\n\n\n
        2. Use a dashboard ID from the Grafana community (e.g., 9628<\/strong> for a comprehensive PostgreSQL monitoring dashboard).<\/li>\n\n\n\n
        3. Follow the prompts to select the Prometheus data source.<\/li>\n<\/ol>\n\n\n\n
          \n\n\n\n
          4. Key Metrics to Monitor<\/strong><\/h3>\n\n\n\n
          The PostgreSQL Exporter provides several useful metrics:<\/p>\n\n\n\n
            \n
          • Database Connection Metrics<\/strong>:\n
              \n
            • pg_stat_activity_count<\/code>: Total active connections.<\/li>\n\n\n\n
            • pg_database_size_bytes<\/code>: Size of the database in bytes.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
            • Query Performance Metrics<\/strong>:\n
                \n
              • pg_stat_user_tables_seq_scan<\/code>: Sequential scans per table.<\/li>\n\n\n\n
              • pg_stat_user_indexes_idx_scan<\/code>: Index scans per table.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
              • Replication Metrics<\/strong>:\n
                  \n
                • pg_stat_replication<\/code>: Replication lag and state.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                • Locks and Deadlocks<\/strong>:\n
                    \n
                  • pg_locks<\/code>: Number of locks held.<\/li>\n\n\n\n
                  • pg_stat_database_deadlocks<\/code>: Total deadlocks detected.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                  • Cache Performance<\/strong>:\n
                      \n
                    • pg_stat_database_blks_hit<\/code>: Cache hit ratio.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                    • Custom Queries<\/strong>: Extend monitoring by creating a queries.yaml<\/code> file for custom metrics: pg_up: query: \"SELECT 1\" metrics: - name: pg_up usage: \"GAUGE\" description: \"PostgreSQL is running\"<\/code><\/li>\n<\/ul>\n\n\n\n
                      \n\n\n\n
                      5. Configure Alerts<\/strong><\/h3>\n\n\n\n
                      5.1. Prometheus Alert Rules<\/strong><\/h4>\n\n\n\n
                      Add alert rules for critical metrics (e.g., high connection count):<\/p>\n\n\n\n
                      groups:\n  - name: postgresql_alerts\n    rules:\n      - alert: HighConnectionCount\n        expr: pg_stat_activity_count > 100\n        for: 5m\n        labels:\n          severity: warning\n        annotations:\n          summary: \"High number of connections\"\n          description: \"PostgreSQL connection count is above 100 for the past 5 minutes.\"\n<\/code><\/pre>\n\n\n\n
                      Link this rule file in prometheus.yml<\/code> under rule_files<\/code>.<\/p>\n\n\n\n
                      5.2. Grafana Alerts<\/strong><\/h4>\n\n\n\n
                      Configure alerts in Grafana dashboards by defining thresholds for metrics.<\/p>\n\n\n\n
                      \n\n\n\n
                      6. Verify Setup<\/strong><\/h3>\n\n\n\n
                        \n
                      • Check metrics in Prometheus at http:\/\/<prometheus_host>:9090\/targets<\/code>.<\/li>\n\n\n\n
                      • View PostgreSQL dashboards in Grafana for visual insights.<\/li>\n\n\n\n
                      • Confirm alerting mechanisms are functional.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
                        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 1.2. Create a Monitoring User […]<\/p>\n","protected":false},"author":1,"featured_media":473,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[4],"tags":[],"_links":{"self":[{"href":"https:\/\/kop.lat\/blog\/wp-json\/wp\/v2\/posts\/752"}],"collection":[{"href":"https:\/\/kop.lat\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kop.lat\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kop.lat\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kop.lat\/blog\/wp-json\/wp\/v2\/comments?post=752"}],"version-history":[{"count":2,"href":"https:\/\/kop.lat\/blog\/wp-json\/wp\/v2\/posts\/752\/revisions"}],"predecessor-version":[{"id":759,"href":"https:\/\/kop.lat\/blog\/wp-json\/wp\/v2\/posts\/752\/revisions\/759"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kop.lat\/blog\/wp-json\/wp\/v2\/media\/473"}],"wp:attachment":[{"href":"https:\/\/kop.lat\/blog\/wp-json\/wp\/v2\/media?parent=752"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kop.lat\/blog\/wp-json\/wp\/v2\/categories?post=752"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kop.lat\/blog\/wp-json\/wp\/v2\/tags?post=752"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}