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.

Transcript

1. Monitor PostgreSQL with the Stack Philipp Krenn
   
   
   
   
   @xeraa 1
2. Infrastructure | Developer Advocate 2
3. Disclaimer This is not a training https://www.elastic.co/training 3
4. Who Is Using Elasticsearch Logstash and Kibana Beats 4
6. You Know, for Search 6
9. ELK Stack 9
14. Elastic Stack 14
18. Starting Point https://github.com/xeraa/postgresql-monitoring 18
19. USB Sticks 19
20. Box Vagrant Ansible Provisioner 20
21. Credentials vagrant & vagrant 21
22. SSH $ ssh vagrant@127.0.0.1 -p 2222 -o PreferredAuthentications=password Windows: http://www.putty.org 22
23. Ansible $ cd /elastic-stack/ $ ls 23
25. REST $ curl -XGET -u "elastic:changeme" http://localhost:9200/ 25
27. Login http://localhost:5601 elastic & changeme 27
29. Filebeat 29
30. Filebeat Modules 30
31. System Dashboards 31
33. PostgreSQL Logs /var/log/postgresql/*.log 33
34. /etc/filebeat/filebeat.yml filebeat.prospectors: - input_type: log paths: - /var/log/postgresql/*.log document_type: postgresql 34
35. Kibana Discover Limit Kibana view to the postgresql _type 35
38. /etc/logstash/conf.d/00-beats-input.conf input { beats { port => 5044 } } 38
39. /etc/logstash/conf.d/10-postgresql- filter.conf filter { } 39
40. /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
41. Grok Patterns https://github.com/logstash-plugins/logstash-patterns- core/blob/master/patterns/grok-patterns 41
42. Building Patterns https://grokdebug.herokuapp.com Part of Kibana 5.5+ 42
44. /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
45. Logstash Restart $ sudo service logstash restart 45
46. /etc/filebeat/filebeat.yml #filebeat.modules: #- module: system 46
47. Filebeat System Module in Logstash https://www.elastic.co/guide/en/logstash/current/ filebeat-modules.html 47
48. /etc/filebeat/filebeat.yml output.logstash: hosts: ["localhost:5044"] username: "elastic" password: "changeme" 48
49. /etc/filebeat/filebeat.yml #output.elasticsearch: # hosts: ["localhost:9200"] # username: "elastic" # password: "changeme" 49
50. Filebeat Restart $ sudo service filebeat restart 50
51. Debug Logstash $ less /var/log/logstash/logstash-plain.log 51
52. Test $ /opt/sqlsmith/sqlsmith --verbose --target="host=localhost port=5432 dbname=test user=test password=test" 52
53. Multiline Logs 53
54. /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
55. Filebeat Restart $ sudo service filebeat restart 55
56. Test $ /opt/sqlsmith/sqlsmith --verbose --target="host=localhost port=5432 dbname=test user=test password=test" 56
57. Visualize level of log events Refresh the index template 57
59. Metricbeat 59
60. Metricbeat System 60
61. Metricbeat Service 61
62. /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
63. Metricbeat Restart $ sudo service metricbeat restart 63
64. Test $ /opt/sqlsmith/sqlsmith --verbose --target="host=localhost port=5432 dbname=test user=test password=test" 64
66. Visual Builder postgresql.database.rows.* 66
68. Packetbeat 68
69. Protocols 69
70. Flows Application layer: Unsupported / encrypted (TLS) protocols IP / TCP / UDP Number of packets & bytes Retransmissions Temporal flow 70
71. /etc/packetbeat/packetbeat.yml packetbeat.protocols.pgsql: ports: [5432] 71
72. Packetbeat Restart $ sudo service packetbeat restart 72
73. Test $ /opt/sqlsmith/sqlsmith --verbose --target="host=localhost port=5432 dbname=test user=test password=test" 73
75. Heartbeat 75
76. Heartbeat ICMP, TCP, HTTP, HTTPS 76
77. /etc/heartbeat/heartbeat.yml heartbeat.monitors: - type: tcp hosts: ["127.0.0.1:5432"] schedule: '@every 10s' 77
78. Heartbeat Restart $ sudo service heartbeat restart 78
79. Test $ sudo service postgresql stop $ sudo service postgresql start 79
80. Visualize Up or down 80
82. Winlogbeat 82
83. libbeat https://github.com/elastic/beats/tree/master/generate/beat 83
85. X-Pack Monitoring Graph Reporting Alerting Machine Learning 85
86. X-Pack Basic 86
87. Conclusion 87
91. Thanks! Questions? Philipp Krenn
    
    
    
    
    @xeraa 91