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