Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Monitor PostgreSQL with the Elastic Stack

Monitor PostgreSQL with the Elastic Stack

How to use the Elastic Stack (previously called ELK Stack) to monitor logs is widely known. But it can also give you a complete picture of your PostgreSQL installation:
* System metrics: Keep track of network traffic and system load.
* Logs: Collect and parse PostgreSQL logs.
* PostgreSQL metrics: Gather the most relevant attributes with the dedicated Metricbeat module.
* Queries: Monitor your queries on the wire without instrumenting PostgreSQL with Packetbeat.

And we will do all of that live since it is so easy and much more interactive that way.

Philipp Krenn

July 05, 2017
Tweet

More Decks by Philipp Krenn

Other Decks in Programming

Transcript

  1. Monitor PostgreSQL with the
    Stack
    Philipp Krenn@xeraa
    1

    View full-size slide

  2. Infrastructure | Developer Advocate
    2

    View full-size slide

  3. Disclaimer
    This is not a training
    https://www.elastic.co/training
    3

    View full-size slide

  4. Who Is Using
    Elasticsearch
    Logstash and Kibana
    Beats
    4

    View full-size slide

  5. You Know, for Search
    6

    View full-size slide

  6. Elastic Stack
    14

    View full-size slide

  7. Starting Point
    https://github.com/xeraa/postgresql-monitoring
    18

    View full-size slide

  8. USB Sticks
    19

    View full-size slide

  9. Box
    Vagrant Ansible Provisioner
    20

    View full-size slide

  10. Credentials
    vagrant & vagrant
    21

    View full-size slide

  11. SSH
    $ ssh [email protected] -p 2222 -o
    PreferredAuthentications=password
    Windows: http://www.putty.org
    22

    View full-size slide

  12. Ansible
    $ cd /elastic-stack/
    $ ls
    23

    View full-size slide

  13. REST
    $ curl -XGET -u "elastic:changeme" http://localhost:9200/
    25

    View full-size slide

  14. Login
    http://localhost:5601
    elastic & changeme
    27

    View full-size slide

  15. Filebeat Modules
    30

    View full-size slide

  16. System Dashboards
    31

    View full-size slide

  17. PostgreSQL Logs
    /var/log/postgresql/*.log
    33

    View full-size slide

  18. /etc/filebeat/filebeat.yml
    filebeat.prospectors:
    - input_type: log
    paths:
    - /var/log/postgresql/*.log
    document_type: postgresql
    34

    View full-size slide

  19. Kibana Discover
    Limit Kibana view to the postgresql _type
    35

    View full-size slide

  20. /etc/logstash/conf.d/00-beats-input.conf
    input {
    beats {
    port => 5044
    }
    }
    38

    View full-size slide

  21. /etc/logstash/conf.d/10-postgresql-
    filter.conf
    filter {
    }
    39

    View full-size slide

  22. /etc/logstash/conf.d/20-elasticsearch-
    output.conf
    output {
    elasticsearch {
    hosts => ["localhost:9200"]
    manage_template => false
    index => "%{[@metadata][beat]}-%{+YYYY.MM.dd}"
    document_type => "%{[@metadata][type]}"
    user => "elastic"
    password => "changeme"
    }
    }
    40

    View full-size slide

  23. Grok Patterns
    https://github.com/logstash-plugins/logstash-patterns-
    core/blob/master/patterns/grok-patterns
    41

    View full-size slide

  24. Building Patterns
    https://grokdebug.herokuapp.com
    Part of Kibana 5.5+
    42

    View full-size slide

  25. /etc/logstash/conf.d/10-postgresql-
    filter.conf
    filter {
    if [type] == "postgresql" {
    grok {
    match => { "message" => "%{DATESTAMP:timestamp} %{TZ} (\[%{DATA:group_id}\]) (\[?%{DATA:user}\]?@\[?%{DATA:database}\]? )?%{DATA:level}: %{GREEDYDATA:msg}" }
    }
    }
    }
    44

    View full-size slide

  26. Logstash Restart
    $ sudo service logstash restart
    45

    View full-size slide

  27. /etc/filebeat/filebeat.yml
    #filebeat.modules:
    #- module: system
    46

    View full-size slide

  28. Filebeat System Module in
    Logstash
    https://www.elastic.co/guide/en/logstash/current/
    filebeat-modules.html
    47

    View full-size slide

  29. /etc/filebeat/filebeat.yml
    output.logstash:
    hosts: ["localhost:5044"]
    username: "elastic"
    password: "changeme"
    48

    View full-size slide

  30. /etc/filebeat/filebeat.yml
    #output.elasticsearch:
    # hosts: ["localhost:9200"]
    # username: "elastic"
    # password: "changeme"
    49

    View full-size slide

  31. Filebeat Restart
    $ sudo service filebeat restart
    50

    View full-size slide

  32. Debug Logstash
    $ less /var/log/logstash/logstash-plain.log
    51

    View full-size slide

  33. Test
    $ /opt/sqlsmith/sqlsmith --verbose --target="host=localhost port=5432 dbname=test user=test password=test"
    52

    View full-size slide

  34. Multiline Logs
    53

    View full-size slide

  35. /etc/filebeat/filebeat.yml
    filebeat.prospectors:
    - input_type: log
    paths:
    - /var/log/postgresql/*.log
    document_type: postgresql
    multiline.pattern: '^[[:space:]]'
    multiline.negate: false
    multiline.match: after
    54

    View full-size slide

  36. Filebeat Restart
    $ sudo service filebeat restart
    55

    View full-size slide

  37. Test
    $ /opt/sqlsmith/sqlsmith --verbose --target="host=localhost port=5432 dbname=test user=test password=test"
    56

    View full-size slide

  38. Visualize
    level of log events
    Refresh the index template
    57

    View full-size slide

  39. Metricbeat
    59

    View full-size slide

  40. Metricbeat System
    60

    View full-size slide

  41. Metricbeat Service
    61

    View full-size slide

  42. /etc/metricbeat/metricbeat.yml
    - module: postgresql
    metricsets:
    - database
    - bgwriter
    - activity
    enabled: true
    period: 10s
    hosts: ["postgres://localhost:5432?sslmode=disable"]
    username: elastic
    password: changeme
    62

    View full-size slide

  43. Metricbeat Restart
    $ sudo service metricbeat restart
    63

    View full-size slide

  44. Test
    $ /opt/sqlsmith/sqlsmith --verbose --target="host=localhost port=5432 dbname=test user=test password=test"
    64

    View full-size slide

  45. Visual Builder
    postgresql.database.rows.*
    66

    View full-size slide

  46. Packetbeat
    68

    View full-size slide

  47. Flows
    Application layer: Unsupported / encrypted (TLS) protocols
    IP / TCP / UDP
    Number of packets & bytes
    Retransmissions
    Temporal flow
    70

    View full-size slide

  48. /etc/packetbeat/packetbeat.yml
    packetbeat.protocols.pgsql:
    ports: [5432]
    71

    View full-size slide

  49. Packetbeat Restart
    $ sudo service packetbeat restart
    72

    View full-size slide

  50. Test
    $ /opt/sqlsmith/sqlsmith --verbose --target="host=localhost port=5432 dbname=test user=test password=test"
    73

    View full-size slide

  51. Heartbeat
    ICMP, TCP, HTTP, HTTPS
    76

    View full-size slide

  52. /etc/heartbeat/heartbeat.yml
    heartbeat.monitors:
    - type: tcp
    hosts: ["127.0.0.1:5432"]
    schedule: '@every 10s'
    77

    View full-size slide

  53. Heartbeat Restart
    $ sudo service heartbeat restart
    78

    View full-size slide

  54. Test
    $ sudo service postgresql stop
    $ sudo service postgresql start
    79

    View full-size slide

  55. Visualize
    Up or down
    80

    View full-size slide

  56. Winlogbeat
    82

    View full-size slide

  57. libbeat
    https://github.com/elastic/beats/tree/master/generate/beat
    83

    View full-size slide

  58. X-Pack
    Monitoring
    Graph
    Reporting
    Alerting
    Machine Learning
    85

    View full-size slide

  59. X-Pack Basic
    86

    View full-size slide

  60. Conclusion
    87

    View full-size slide

  61. Thanks!
    Questions?
    Philipp Krenn@xeraa
    91

    View full-size slide