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
   

   View Slide

2. De-constructing Postgres into a Cloud Native Platform
   @ahachete
   ` whoami `
   Álvaro Hernández
   
   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
   

   View Slide

3. De-constructing Postgres into a Cloud Native Platform
   @ahachete
   The “Stack” Problem
   

   View Slide

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
   

   View Slide

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.
   

   View Slide

6. De-constructing Postgres into a Cloud Native Platform
   @ahachete
   The Postgres Ecosystem
   

   View Slide

7. De-constructing Postgres into a Cloud Native Platform
   @ahachete
   An Enterprise-Grade Postgres Stack
   

   View Slide

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
   

   View Slide

9. De-constructing Postgres into a Cloud Native Platform
   @ahachete
   Connection Pooling
   pg_bench, scale 2000, m4.large
   (2 vCPU, 8GB RAM, 1k IOPS)
   

   View Slide

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
    

    View Slide

11. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    High Availability
    ● Manual?
    ● PgPool?
    ● Repmgr?
    ● Patroni?
    ● pg_autofailover?
    ● PAF?
    ● Stolon?
    

    View Slide

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?
    

    View Slide

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
    

    View Slide

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?
    

    View Slide

15. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Monitoring
    ● Zabbix?
    ● Okmeter?
    ● Pganalyze?
    ● Pgwatch2?
    ● PoWA?
    ● New Relic?
    ● DataDog?
    ● Prometheus?
    

    View Slide

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?
    

    View Slide

17. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Where Do We Deploy The Stack?
    

    View Slide

18. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    StackGres:
    The Innovative Postgres Platform
    on Kubernetes
    

    View Slide

19. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Postgres is… boring
    But boring and innovation are not at odds
    

    View Slide

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
    

    View Slide

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
    

    View Slide

22. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    De-constructing
    Postgres
    

    View Slide

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
    

    View Slide

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/
    

    View Slide

25. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Envoy: offloading monitoring and SSL
    

    View Slide

26. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Envoy: offloading monitoring and SSL
    

    View Slide

27. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Envoy: offloading monitoring and SSL
    

    View Slide

28. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Envoy: offloading monitoring and SSL
    

    View Slide

29. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Envoy: offloading monitoring and SSL
    

    View Slide

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?
    

    View Slide

31. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Distributed Logs
    

    View Slide

32. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Distributed Logs
    

    View Slide

33. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Distributed Logs
    

    View Slide

34. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Distributed Logs
    

    View Slide

35. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Distributed Logs
    

    View Slide

36. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Distributed Logs
    

    View Slide

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).
    

    View Slide

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!
    

    View Slide

39. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Anatomy of the Pod
    

    View Slide

40. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Anatomy of the Pod
    

    View Slide

41. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Anatomy of the Pod
    

    View Slide

42. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Anatomy of the Pod
    

    View Slide

43. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Anatomy of the Pod
    

    View Slide

44. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Anatomy of the Pod
    

    View Slide

45. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Anatomy of the Pod
    

    View Slide

46. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Anatomy of the Pod
    

    View Slide

47. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    StackGres Architecture
    

    View Slide

48. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    StackGres Architecture
    

    View Slide

49. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    StackGres Architecture
    

    View Slide

50. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    StackGres Architecture
    

    View Slide

51. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    StackGres Architecture
    

    View Slide

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!
    

    View Slide

53. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Monitoring integration: Prometheus, Grafana
    

    View Slide

54. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Using K8s RBAC for Web Console AuthN/AuthZ
    

    View Slide

55. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Using K8s RBAC for Web Console AuthN/AuthZ
    

    View Slide

56. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Using K8s RBAC for Web Console AuthN/AuthZ
    

    View Slide

57. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Using K8s RBAC for Web Console AuthN/AuthZ
    

    View Slide

58. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Using K8s RBAC for Web Console AuthN/AuthZ
    

    View Slide

59. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Using K8s RBAC for Web Console AuthN/AuthZ
    

    View Slide

60. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Cloud-Nativization of Postgres DBA Operations
    

    View Slide

61. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Cloud-Nativization of Postgres DBA Operations
    

    View Slide

62. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Cloud-Nativization of Postgres DBA Operations
    

    View Slide

63. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Cloud-Nativization of Postgres DBA Operations
    

    View Slide

64. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Cloud-Nativization of Postgres DBA Operations
    

    View Slide

65. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Cloud-Nativization of Postgres DBA Operations
    

    View Slide

66. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    Cloud-Nativization of Postgres DBA Operations
    

    View Slide

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
    

    View Slide