Upgrade to Pro — share decks privately, control downloads, hide ads and more …

De-constructing Postgres Into a Cloud Native Platform

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.

OnGres

January 19, 2021
Tweet

More Decks by OnGres

Other Decks in Technology

Transcript

  1. De-constructing Postgres into a Cloud Native Platform
    @ahachete
    De-constructing
    Postgres
    Into a
    Cloud Native Platform
    Alvaro Hernandez

    View full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size slide

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

    View full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size slide