Save 37% off PRO during our Black Friday Sale! »

De-constructing Postgres Into a Cloud Native Platform

E084eb5b13255d30b3800d7afb251147?s=47 OnGres
January 19, 2021

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.

E084eb5b13255d30b3800d7afb251147?s=128

OnGres

January 19, 2021
Tweet

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