De-constructing Postgres Into a Cloud Native Platform

Is deploying Postgres in Kubernetes just repackaging it into a container? Can’t Postgres leverage the wide range of Cloud-Native software and integrate well with K8s? Join this journey that will cover and demonstrate, with demos running on StackGres:

https://www.meetup.com/Data-on-Kubernetes-community/events/275567036/

How to structure Postgres into an init-less container, plus several sidecar containers for connection pooling, backups, agents, etc.
Defining high level CRDs as the single API to interact with the Postgres operator.
Using K8s RBAC for user authentication of a web UI management interface.
Using Prometheus for monitoring; bundling a node, Postgres and PgBouncer exporters together.
Proxying Postgres traffic through Envoy. Terminate Postgres SSL with an Envoy plugin, that also exports wire protocol metrics to Prometheus.
Using Fluentbit to capture Postgres logs and forward them to Fluentd, which stores them on a centralized Postgres database.

Transcript

1. De-constructing Postgres into a Cloud Native Platform @ahachete De-constructing Postgres Into a Cloud Native Platform Alvaro Hernandez
2. De-constructing Postgres into a Cloud Native Platform @ahachete ` whoami ` Álvaro Hernández <aht@ongres.com> aht.es • Founder & CEO, OnGres • 20+ years Postgres user and DBA • Mostly doing R&D to create new, innovative software on Postgres • Frequent speaker at Postgres, database conferences • Principal Architect of ToroDB • Founder and President of the NPO Fundación PostgreSQL • AWS Data Hero
3. De-constructing Postgres into a Cloud Native Platform @ahachete The “Stack” Problem
4. De-constructing Postgres into a Cloud Native Platform @ahachete Postgres and Oracle Install Size $ podman images --format "table {{.Repository}} {{.Tag}} {{.Size}}" \ docker.io/library/postgres REPOSITORY TAG SIZE docker.io/library/postgres alpine 76.9 MB docker.io/library/postgres 12.0 356 MB $ podman images --format "table {{.Repository}} {{.Tag}} {{.Size}}" \ docker.io/store/oracle/database-enterprise REPOSITORY TAG SIZE docker.io/store/oracle/database-enterprise 12.2.0.1 3.46 GB
5. De-constructing Postgres into a Cloud Native Platform @ahachete Postgres Is “Just a Kernel” i Postgres is like the Linux kernel Running Postgres in production requires “a RedHat” of Postgres. A curated set of open source components built, verified and packaged together.
6. De-constructing Postgres into a Cloud Native Platform @ahachete The Postgres Ecosystem
7. De-constructing Postgres into a Cloud Native Platform @ahachete An Enterprise-Grade Postgres Stack
8. De-constructing Postgres into a Cloud Native Platform @ahachete Configuration • OS, filesystem tuning • PostgreSQL default configuration is very conservative. • Resources: ◦ https://postgresqlco.nf ◦ PostgreSQL Configuration for Humans
9. De-constructing Postgres into a Cloud Native Platform @ahachete Connection Pooling pg_bench, scale 2000, m4.large (2 vCPU, 8GB RAM, 1k IOPS)
10. De-constructing Postgres into a Cloud Native Platform @ahachete Connection Pooling • PgPool? • PgBouncer? • Odyssey? • Pgagroal? • Where do we place the pool? ◦ Client-side ◦ Server-side ◦ Middle-ware ◦ Some or all of the above
11. De-constructing Postgres into a Cloud Native Platform @ahachete High Availability • Manual? • PgPool? • Repmgr? • Patroni? • pg_autofailover? • PAF? • Stolon?
12. De-constructing Postgres into a Cloud Native Platform @ahachete Backups and DR • pg_dump? • Barman? • Pgbackrest? • Wal-e / Wal-g? • pg_probackup? • To disk? To cloud storage?
13. De-constructing Postgres into a Cloud Native Platform @ahachete Centralized Logging • Logs on every server • There is not a good solution for this • Cloud-native solutions like fluentd or Loki may work • Store the logs on Timescale
14. De-constructing Postgres into a Cloud Native Platform @ahachete Network Proxy. Entrypoint Problem • Entrypoint: how do I locate the master, if it might be changing? • How do I obtain traffic metrics? • Is it possible to manage traffic: duplicate, A/B to test clusters, or even inspect it? • Offload TLS?
15. De-constructing Postgres into a Cloud Native Platform @ahachete Monitoring • Zabbix? • Okmeter? • Pganalyze? • Pgwatch2? • PoWA? • New Relic? • DataDog? • Prometheus?
16. De-constructing Postgres into a Cloud Native Platform @ahachete Management Interface • There are no tools like OEM… • UI oriented towards cluster management • ClusterControl? • Elephant Shed?
17. De-constructing Postgres into a Cloud Native Platform @ahachete Where Do We Deploy The Stack?
18. De-constructing Postgres into a Cloud Native Platform @ahachete StackGres: The Innovative Postgres Platform on Kubernetes
19. De-constructing Postgres into a Cloud Native Platform @ahachete Postgres is… boring But boring and innovation are not at odds
20. De-constructing Postgres into a Cloud Native Platform @ahachete Innovation opportunities There hasn’t been much innovation in Postgres in some areas: Deployment Automation Offloading Functionality to third-party software
21. De-constructing Postgres into a Cloud Native Platform @ahachete Innovation opportunities on Kubernetes Kubernetes APIs / Operators Kubernetes APIs / Operators Sidecars / CNCF ecosystem There hasn’t been much innovation in Postgres in some areas: Deployment Automation Offloading Functionality to third-party software
22. De-constructing Postgres into a Cloud Native Platform @ahachete De-constructing Postgres
23. De-constructing Postgres into a Cloud Native Platform @ahachete Innovation agenda • Envoy: offloading monitoring and SSL • Distributed Logs • Init-less containers. Sidecars. Anatomy of the pod. StackGres Architecture • Monitoring Integration: Prometheus, Grafana • Using K8s RBAC for Web Console AuthN/AuthZ • Cloud-Nativization of Postgres DBA Operations
24. De-constructing Postgres into a Cloud Native Platform @ahachete Envoy: offloading monitoring and SSL What if we could offload from Postgres certain workloads? And avoid restarts? • Envoy is a CNCF Proxy • With a Postgres specific filter • Decodes the wire protocol: protocol metrics -> Prometheus • (coming In 1.18): terminates Postgres SSL • And more functionalities to come in the future… https://www.cncf.io/blog/2020/08/13/envoy-1-15-introduces-a-new-post gres-extension-with-monitoring-support/
25. De-constructing Postgres into a Cloud Native Platform @ahachete Envoy: offloading monitoring and SSL
26. De-constructing Postgres into a Cloud Native Platform @ahachete Envoy: offloading monitoring and SSL
27. De-constructing Postgres into a Cloud Native Platform @ahachete Envoy: offloading monitoring and SSL
28. De-constructing Postgres into a Cloud Native Platform @ahachete Envoy: offloading monitoring and SSL
29. De-constructing Postgres into a Cloud Native Platform @ahachete Envoy: offloading monitoring and SSL
30. De-constructing Postgres into a Cloud Native Platform @ahachete Distributed Logs • On a 12 node cluster, do you ssh, cat /var/log/…/* |grep … | … to read logs, for every node? • What happens when the log partition fills up? • How much effort does it take to export your logs to a database or similar tool for processing… say with SQL?
31. De-constructing Postgres into a Cloud Native Platform @ahachete Distributed Logs
32. De-constructing Postgres into a Cloud Native Platform @ahachete Distributed Logs
33. De-constructing Postgres into a Cloud Native Platform @ahachete Distributed Logs
34. De-constructing Postgres into a Cloud Native Platform @ahachete Distributed Logs
35. De-constructing Postgres into a Cloud Native Platform @ahachete Distributed Logs
36. De-constructing Postgres into a Cloud Native Platform @ahachete Distributed Logs
37. De-constructing Postgres into a Cloud Native Platform @ahachete Distributed Logs Main features: • Postgres, Patroni logs (PgBouncer: WIP). • Multi-tenant: all pods from one or more clusters. • Query with SQL or via the Web Console. Timescale usage: • Open Source (Apache 2) version. • Partitions the logs space by timestamp. • Provide support for partition pruning (logs lifecycle).
38. De-constructing Postgres into a Cloud Native Platform @ahachete Init-less containers. Sidecars Pods are not slim VMs. Pods should have a single process hierarchy where probes happen. No init!
39. De-constructing Postgres into a Cloud Native Platform @ahachete Anatomy of the Pod
40. De-constructing Postgres into a Cloud Native Platform @ahachete Anatomy of the Pod
41. De-constructing Postgres into a Cloud Native Platform @ahachete Anatomy of the Pod
42. De-constructing Postgres into a Cloud Native Platform @ahachete Anatomy of the Pod
43. De-constructing Postgres into a Cloud Native Platform @ahachete Anatomy of the Pod
44. De-constructing Postgres into a Cloud Native Platform @ahachete Anatomy of the Pod
45. De-constructing Postgres into a Cloud Native Platform @ahachete Anatomy of the Pod
46. De-constructing Postgres into a Cloud Native Platform @ahachete Anatomy of the Pod
47. De-constructing Postgres into a Cloud Native Platform @ahachete StackGres Architecture
48. De-constructing Postgres into a Cloud Native Platform @ahachete StackGres Architecture
49. De-constructing Postgres into a Cloud Native Platform @ahachete StackGres Architecture
50. De-constructing Postgres into a Cloud Native Platform @ahachete StackGres Architecture
51. De-constructing Postgres into a Cloud Native Platform @ahachete StackGres Architecture
52. De-constructing Postgres into a Cloud Native Platform @ahachete Monitoring integration: Prometheus, Grafana Integrating Prometheus is relatively easy in K8s. Next steps are: • Postgres exporter • PgBouncer exporter (using Postgres exporter + dblink) • Customized Grafana dashboards • Customizing exporter queries • Providing pre-configured AlertManager alerts • Embed Grafana dashboards into the Web Console!
53. De-constructing Postgres into a Cloud Native Platform @ahachete Monitoring integration: Prometheus, Grafana
54. De-constructing Postgres into a Cloud Native Platform @ahachete Using K8s RBAC for Web Console AuthN/AuthZ
55. De-constructing Postgres into a Cloud Native Platform @ahachete Using K8s RBAC for Web Console AuthN/AuthZ
56. De-constructing Postgres into a Cloud Native Platform @ahachete Using K8s RBAC for Web Console AuthN/AuthZ
57. De-constructing Postgres into a Cloud Native Platform @ahachete Using K8s RBAC for Web Console AuthN/AuthZ
58. De-constructing Postgres into a Cloud Native Platform @ahachete Using K8s RBAC for Web Console AuthN/AuthZ
59. De-constructing Postgres into a Cloud Native Platform @ahachete Using K8s RBAC for Web Console AuthN/AuthZ
60. De-constructing Postgres into a Cloud Native Platform @ahachete Cloud-Nativization of Postgres DBA Operations
61. De-constructing Postgres into a Cloud Native Platform @ahachete Cloud-Nativization of Postgres DBA Operations
62. De-constructing Postgres into a Cloud Native Platform @ahachete Cloud-Nativization of Postgres DBA Operations
63. De-constructing Postgres into a Cloud Native Platform @ahachete Cloud-Nativization of Postgres DBA Operations
64. De-constructing Postgres into a Cloud Native Platform @ahachete Cloud-Nativization of Postgres DBA Operations
65. De-constructing Postgres into a Cloud Native Platform @ahachete Cloud-Nativization of Postgres DBA Operations
66. De-constructing Postgres into a Cloud Native Platform @ahachete Cloud-Nativization of Postgres DBA Operations
67. De-constructing Postgres into a Cloud Native Platform @ahachete Thank you Join our Community in Slack and/or Discord to stay up to date! slack.stackgres.io discord.stackgres.io