SLIDE 1
Infrastructure at your Service.
30.06.17
Elking your PostgreSQL Database Infrastructure
Page 2
Elking your PostgreSQL Database Infrastructure Infrastructure at - - PowerPoint PPT Presentation
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
SLIDE 2
SLIDE 3
Agenda
1.Playground Infrastructure 2.Elastic Search Stack 3.PostgreSQL Monitoring 4.Conclusion
30.06.17 Page 3
Elking your PostgreSQL Database Infrastructure
SLIDE 4
Playground Infrastructure
30.06.17
Elking your PostgreSQL Database Infrastructure
Page 4
> Big Picture > Use case and deployment
process
SLIDE 5
Big Picture
30.06.17
Playground Infrastructure
Page 5
Elking your PostgreSQL Database Infrastructure
Elastic Stack 5.4.1 Postgres Clusters 10.0 PGPool II
PG Standby Node PG Primary Node PGPool Node 0 PGPool Node 1
Heartbeat
SSH 192.168.56.0/24
Version 5.1.18 r114002 (Qt5.6.2)
Health Check
Version 2.3.0
Streaming Replication
vmtestpgsql02 192.168.56.210/32 vmtestpgpool02 192.168.56.230/32 vmtestpgpool01 192.168.56.220/32 Kibana
Elastic Node 0
Elastic Search Beats Beats Beats vmtestpgsql01 192.168.56.200/32 vmtestelastic01 192.168.56.100/32 Ship Ship
Ingest
Visualize
SLIDE 6
Use case and deployment process
30.06.17
Playground Infrastructure
Page 6
Elking your PostgreSQL Database Infrastructure
Database Clusters Elastic Stack Server Dashboard import Metric Agent Deployment PGPool Metric Agent Deployment
SLIDE 7
Elastic Search Stack
30.06.17
Elking your PostgreSQL Database Infrastructure
Page 7
> What does ELK mean ? > What’s a Beat ? > Is all the stack Open Source ? > How much costs the cloud
- ffering ?
SLIDE 8
> 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
> L – Logstach
> Collect, Enrich and Transport
> K – Kibana
> Explore, Visualize and Share
What does ELK means ?
30.06.17
Elastic Search Stack
Page 8
Elking your PostgreSQL Database Infrastructure DATA DATA DATA DATA
INFORMATION
DATA DATA
TRANS INFORMATION
SLIDE 9
What does ELK means ?
30.06.17
Elastic Search Stack
Page 9
Elking your PostgreSQL Database Infrastructure
Kibana Elastic Search Logstash Beats
Elastic Cloud
Graph Alerting Monitoring Reporting Security
User Interface Visualization
> It evolved to be a full Monitoring Stack
Store, Index & Analyzse Ingest X-Pack
SLIDE 10
> Lightweight Data Shippers to ingest data into Elastic
What’s a Beat ?
30.06.17
Elastic Search Stack
Page 10
Elking your PostgreSQL Database Infrastructure
SLIDE 11
Is all the stack open source ?
30.06.17
Elastic Search Stack
Page 11
Elking your PostgreSQL Database Infrastructure
SLIDE 12
> Components support over subscriptions
Is all the stack open source ?
30.06.17
Elastic Search Stack
Page 12
Elking your PostgreSQL Database Infrastructure Open Source Basic Gold Platinum
Elastic Search Elasticsearch Kibana Beats Logstash
X-Pack
Monitoring Multicluster Configurable Data retention
X-Security
Authentication + Encryption Role-Based access ctrl Audit Encryption at rest support
SLIDE 13
> Components support over subscriptions > Details : https://www.elastic.co/subscriptions
Is all the stack open source ?
30.06.17
Elastic Search Stack
Page 13
Elking your PostgreSQL Database Infrastructure Open Source Basic Gold Platinum
X-Pack Alerting via Watcher Reporting Graph Analytics & Visualization Dev Tools – Search Profiler
Support
Support Coverage
BH 24x7
Response Times
- Crit. 4h
L2: 1d L3: 2d
- Crit. 1h
L2: 4h L3: 1d
Unlimited # of Incidents
SLIDE 14
> Elastic Cloud Offering – Host Elastic Search and Kibana
How much cost the cloud offering ?
30.06.17
Elastic Search Stack
Page 14
Elking your PostgreSQL Database Infrastructure
SLIDE 15
PostgreSQL Monitoring
30.06.17
Elking your PostgreSQL Database Infrastructure
Page 15
> Postgres Cluster Settings
SLIDE 16
> 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 Cluster Settings
30.06.17
PostgreSQL Monitoring
Page 16
Elking your PostgreSQL Database Infrastructure
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
SLIDE 17
> 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
Postgres Cluster Settings
30.06.17
PostgreSQL Monitoring
Page 17
Elking your PostgreSQL Database Infrastructure
SLIDE 18
> Easy to deploy > Ingest lots of data of any log type > Already have predefined metric such as
Pro & contra
30.06.17
Conclusion
> Most of the required features needs subscription
Page 18
Elking your PostgreSQL Database Infrastructure
SLIDE 19
Infrastructure at your Service.
30.06.17
We look forward to working with you!
Page 19