Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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.

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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?

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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?

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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?

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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/

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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?

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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!

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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!

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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