Elking your PostgreSQL Database Infrastructure Infrastructure at - PowerPoint PPT Presentation

Infrastructure at your Service. Elking your PostgreSQL Database Infrastructure

Infrastructure at your Service. About me Arnaud Berbier Senior Consultant +41 79 128 91 45 arnaud.berbier@dbi-services.com Page 2 Elking your PostgreSQL Database Infrastructure 30.06.17

Agenda 1.Playground Infrastructure 2.Elastic Search Stack 3.PostgreSQL Monitoring 4.Conclusion Page 3 Elking your PostgreSQL Database Infrastructure 30.06.17

Playground Infrastructure > Big Picture > Use case and deployment process Page 4 Elking your PostgreSQL Database Infrastructure 30.06.17

Playground Infrastructure Big Picture Version 2.3.0 SSH 192.168.56.0/24 Version 5.1.18 r114002 (Qt5.6.2) Postgres Clusters 10.0 Elastic Stack 5.4.1 PGPool II vmtestpgpool01 vmtestpgsql01 vmtestelastic01 192.168.56.220/32 192.168.56.200/32 192.168.56.100/32 Beats PG Primary Node PGPool Node 0 Elastic Node 0 Health Ship Streaming Replication Heartbeat Check Ingest Beats Visualize Ship Kibana Elastic Search PGPool Node 1 PG Standby Node Beats vmtestpgpool02 vmtestpgsql02 192.168.56.230/32 192.168.56.210/32 Page 5 Elking your PostgreSQL Database Infrastructure 30.06.17

Playground Infrastructure Use case and deployment process Database Elastic Stack Clusters Server Dashboard Metric Agent import Deployment Metric Agent PGPool Deployment Page 6 Elking your PostgreSQL Database Infrastructure 30.06.17

Elastic Search Stack > What does ELK mean ? > What’s a Beat ? > Is all the stack Open Source ? > How much costs the cloud offering ? Page 7 Elking your PostgreSQL Database Infrastructure 30.06.17

Elastic Search Stack What does ELK means ? > ELK is a complete monitoring platform - Elastic Search Stack > At the beginning Elastic Stack was commonly known as ELK > E - Elastic Search > Store, Search and Analyze DATA DATA DATA > L – Logstach INFORMATION DATA DATA > Collect, Enrich and Transport DATA TRANS INFORMATION > K – Kibana > Explore, Visualize and Share Page 8 Elking your PostgreSQL Database Infrastructure 30.06.17

Elastic Search Stack What does ELK means ? > It evolved to be a full Monitoring Stack User Interface X-Pack Kibana Visualization Security Alerting Store, Index & Elastic Search Analyzse Monitoring Reporting Ingest Logstash Beats Graph Elastic Cloud Page 9 Elking your PostgreSQL Database Infrastructure 30.06.17

Elastic Search Stack What’s a Beat ? > Lightweight Data Shippers to ingest data into Elastic Page 10 Elking your PostgreSQL Database Infrastructure 30.06.17

Elastic Search Stack Is all the stack open source ? Page 11 Elking your PostgreSQL Database Infrastructure 30.06.17

Elastic Search Stack Is all the stack open source ? > Components support over subscriptions Open Source Basic Gold Platinum Elasticsearch Elastic Search Kibana Beats Logstash Monitoring X-Pack Multicluster Configurable Data retention Authentication + Encryption X-Security Role-Based access ctrl Audit Encryption at rest support Page 12 Elking your PostgreSQL Database Infrastructure 30.06.17

Elastic Search Stack Is all the stack open source ? > Components support over subscriptions Open Source Basic Gold Platinum Alerting via Watcher Reporting X-Pack Graph Analytics & Visualization Dev Tools – Search Profiler Support Coverage BH 24x7 Support Crit. 4h Crit. 1h Response Times L2: 1d L2: 4h L3: 2d L3: 1d Unlimited # of Incidents > Details : https://www.elastic.co/subscriptions Page 13 Elking your PostgreSQL Database Infrastructure 30.06.17

Elastic Search Stack How much cost the cloud offering ? > Elastic Cloud Offering – Host Elastic Search and Kibana Page 14 Elking your PostgreSQL Database Infrastructure 30.06.17

PostgreSQL Monitoring > Postgres Cluster Settings Page 15 Elking your PostgreSQL Database Infrastructure 30.06.17

PostgreSQL Monitoring Postgres Cluster Settings > What’s need to be done on the Postgres Clusters > Filebeat log harvesting > The elastic stack process owner needs access to the Postgres log files > log_file_mode in pg_settings parameters postgres=# select name, setting, unit from pg_settings where name like '%log_file%' or name like '%log_dir%'; name | setting | unit ----------+---------------------------------------------+------ log_directory | /app/postgres/admin/dbixchange01/pg_log | log_file_mode | 0776 | log_filename | postgresql-%a.log |(3 rows) > This correspond to file mode 666 Page 16 Elking your PostgreSQL Database Infrastructure 30.06.17

PostgreSQL Monitoring Postgres Cluster Settings > What’s need to be done on the Postgres Clusters > Metric Beat postgresql > A dedicated user for DB connection > Allowed access to the database > Elastic servers need to be accessible by the deployed beat agents > Firewall > Network Page 17 Elking your PostgreSQL Database Infrastructure 30.06.17

Conclusion Pro & contra > Easy to deploy > Ingest lots of data of any log type > Already have predefined metric such as > Most of the required features needs subscription Page 18 Elking your PostgreSQL Database Infrastructure 30.06.17

Infrastructure at your Service. Any questions? Please do ask Arnaud Berbier Senior Consultant +41 79 128 91 45 arnaud.berbier@dbi-services.com We look forward to working with you! Page 19 Elking your PostgreSQL Database Infrastructure 30.06.17