== StackGres: Cloud-Native PostgreSQL on Kubernetes ==

An enterprise-grade PostgreSQL requires many complementary technologies to the database core: high availability and automated failover, monitoring and alerting, centralized logging, connection pooling, etc. That is, a stack of components around PostgreSQL.

Kubernetes has enabled a new model to deploy software abstracting away the infrastructure. However, containers are not lightweight VMs, and the packing of software paradigms that work on VMs are not valid on containers/Kubernetes. How should be PostgreSQL and its stack be deployed on Kubernetes?

Enter StackGres. An open source software that is the result of re-engineering PostgreSQL to become cloud native. Join this talk to learn and see.

Speaker:
Alvaro Hernandez
I am a passionate database and software developer. “Serial” entrepreneur, after several unsuccessful ventures, I founded and work as the CEO of OnGres, a PostGres startup set to disrupt the database market. I have been dedicated to PostgreSQL and R&D in databases since two decades ago.

In my heart, I am an open source advocate and developer. I am a well-known member of the PostgreSQL Community, to which I have contributed founding the non-profit Fundación PostgreSQL and the Spanish PostgreSQL User Group. You will find on this website a list of my open source (code) contributions, as well as my work for open source communities, like PostgreSQL.

Transcript

1. CLOUD NATIVE POSTGRESQL IN KUBERNETES CLOUD NATIVE POSTGRESQL IN KUBERNETES ÁLVARO HERNÁNDEZ
2. CLOUD NATIVE POSTGRESQL IN KUBERNETES ` whoami ` • 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 Álvaro Hernández <aht@ongres.com> @ahachete
3. CLOUD NATIVE POSTGRESQL IN KUBERNETES THE “STACK” PROBLEM
4. CLOUD NATIVE POSTGRESQL IN KUBERNETES //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. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Postgres Is “Just a Kernel” 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. CLOUD NATIVE POSTGRESQL IN KUBERNETES //The Postgres Ecosystem
7. CLOUD NATIVE POSTGRESQL IN KUBERNETES //An Enterprise-Grade Postgres Stack
8. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Configuration • OS, filesystem tuning • PostgreSQL default configuration is very conservative. • Resources: ◦ https://postgresqlco.nf ◦ PostgreSQL Configuration for Humans
9. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Connection Pooling pg_bench, scale 2000, m4.large (2 vCPU, 8GB RAM, 1k IOPS)
10. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Connection Pooling • PgPool? • PgBouncer? • Odyssey? • Where do we place the pool? ◦ Client-side ◦ Server-side ◦ Middle-ware ◦ Some or all of the above
11. CLOUD NATIVE POSTGRESQL IN KUBERNETES //High Availability • Manual? • PgPool? • Repmgr? • Patroni? • pg_autofailover? • PAF? • Stolon?
12. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Backups and DR • pg_dump? • Barman? • Pgbackrest? • Wal-e / Wal-g? • pg_probackup? • To disk? To cloud storage?
13. CLOUD NATIVE POSTGRESQL IN KUBERNETES //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. CLOUD NATIVE POSTGRESQL IN KUBERNETES //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. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Monitoring • Zabbix? • Okmeter? • Pganalyze? • Pgwatch2? • PoWA? • New Relic? • DataDog? • Prometheus?
16. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Management Interface • There are no tools like OEM… • UI oriented towards cluster management • ClusterControl? • Elephant Shed?
17. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Where Do We Deploy The Stack?
18. CLOUD NATIVE POSTGRESQL IN KUBERNETES DEPLOYING THE POSTGRES STACK ON KUBERNETES
19. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Why Kubernetes? <Really, really short introduction to Kubernetes /> • K8s is “the JVM” of the architecture of distributed systems: an abstraction layer & API to deploy and automate infrastructure. • K8s provides APIs for nodes and IPs discovery, secret management, network proxying and load balancing, storage allocation, etc • A PostgreSQL deployment can be fully automated!
20. CLOUD NATIVE POSTGRESQL IN KUBERNETES //K8s Operators: Automate Postgres Ops! • Operators are just applications, developed for K8s • Understand Postgres operations • Call K8s APIs to execute the operations • Automate: ◦ Minor version upgrades (rolling strategy) ◦ Explicit vacuums ◦ Repacks / reindex ◦ Health checks
21. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Cloud Native Cloud native applications are: • designed to be packaged in containers • scale and can be orchestrated for high availability And follow cloud-native best practices including: • Single-process hierarchy per container • Sidecar containers to separate concerns • Design for mostly ephemeral containers
22. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Containers Are Not Slim VMs • A container is an abstraction over a process hierarchy, with its own network, process namespaces and virtualized storage. • But it is just a process hierarchy. Not many processes! • No kernel, kernel modules, device drivers, no init system, bare minimum OS. • Should be just the binary of your process and its dynamic libraries and support files it needs.
23. CLOUD NATIVE POSTGRESQL IN KUBERNETES TURNING POSTGRESQL CLOUD NATIVE
24. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Is Postgres for Containers? • Overhead is minimal (1-2%): it is just a wrapper over the processes! • Containers are as ephemeral as the process hierarchy they wrap. • Advantage: they can be restarted somewhere if they fail. • It’s easier with stateless apps. But storage can be easily decoupled from containers: there are many storage persistence technologies. • The entrypoint problem is typically solved by the container orchestration layer.
25. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Minimal Container Image • It’s not about disk space or I/O. It’s about security and good design principles. • PostgreSQL binaries are minimal: container image cannot be huge. Remove: ◦ Non-essential PostgreSQL binaries ◦ Docs, psql ◦ OS non system tools --all but /bin, /sbin, /lib* ◦ Init system if any!
26. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Leverage the Sidecar Pattern If a container should only have a single process hierarchy, how can we add support daemons like monitoring or HA agents? • In K8s a pod is a set of 1+ containers that share the same namespaces, and run side-by-side on the same host. • Sidecar pattern: deploy side functionality (like agents) to side containers (sidecars) on the same pod as PostgreSQL’s container. • Sidecars have the same IP and port space; process space (can send kill signals to processes), see the same persistent volume mount.
27. CLOUD NATIVE POSTGRESQL IN KUBERNETES //High Availability (HA) • HA is a native concept of cloud native. • K8s provides mechanisms for leader election and HA. But are not good for Postgres! • Leader election needs to be replication lag and topology aware. • Also need to run operations after {fail,switch}over. • Use PostgreSQL-specific HA mechanisms. • Use K8s to automatically restart pods if they fail, and scale replicas.
28. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Centralized Logging • A pattern that is not exclusive to containers, but reinforced in K8s. • DBAs need not to “login” to every container to check logs. • Centralized logs allow to: ◦ Correlate events across multiple servers (leader / replicas). ◦ Manage logs persistence once. ◦ Run periodic reporting and alerting processes (like pgBadger). ◦ Correlate with centralized monitoring (like Prometheus).
29. CLOUD NATIVE POSTGRESQL IN KUBERNETES STACKGRES
30. CLOUD NATIVE POSTGRESQL IN KUBERNETES //StackGres: Cloud Native Postgres Running on Kubernetes. Embracing multi-cloud and on-premise. Enterprise-grade, highly opinionated Postgres stack. DB-as-a-Service without vendor lock-in. Root access. Open source!
31. CLOUD NATIVE POSTGRESQL IN KUBERNETES //StackGres Architecture
32. CLOUD NATIVE POSTGRESQL IN KUBERNETES //StackGres Architecture
33. CLOUD NATIVE POSTGRESQL IN KUBERNETES //StackGres Architecture • Storage Class behavior:
34. CLOUD NATIVE POSTGRESQL IN KUBERNETES //StackGres Architecture • Networking
35. CLOUD NATIVE POSTGRESQL IN KUBERNETES DEPLOY A POSTGRESQL-aaS WITH STACKGRES
36. CLOUD NATIVE POSTGRESQL IN KUBERNETES //CRDs: StackGres “API” • CRDs are Kubernetes custom objects (Custom Resource Definition). • StackGres creates the CRDs and uses them extensively. An instance of a CRD is a “CR”. • They define high-level concepts, such as a Postgres Cluster. • No need to install any separate tool or CLI: CRDs are our API, use kubectl to communicate with StackGres. • CRs are bi-directional: you specify in the spec part what you want; StackGres will report in the status field extra information. • Some CRs may be created by StackGres, like automatic backups
37. CLOUD NATIVE POSTGRESQL IN KUBERNETES //SGCluster: Basic Example apiVersion: stackgres.io/v1beta1 kind: SGCluster metadata: name: sg1 spec: postgresVersion: 'latest' instances: 2 pods: persistentVolume: size: '10Gi'
38. CLOUD NATIVE POSTGRESQL IN KUBERNETES //SGInstanceProfile apiVersion: stackgres.io/v1beta1 kind: SGInstanceProfile metadata: name: size-small spec: cpu: "2" memory: "4Gi"
39. CLOUD NATIVE POSTGRESQL IN KUBERNETES //SGPostgresConfig apiVersion: stackgres.io/v1beta1 kind: SGPostgresConfig metadata: name: pgconfig1 spec: postgresVersion: "12" postgresql.conf: shared_buffers: '512MB' random_page_cost: '1.5' password_encryption: 'scram-sha-256' wal_compression: 'on'
40. CLOUD NATIVE POSTGRESQL IN KUBERNETES //SGPoolingConfig apiVersion: stackgres.io/v1beta1 kind: SGPoolingConfig metadata: name: poolconfig1 spec: pgBouncer: pgbouncer.ini: pool_mode: transaction max_client_conn: '200' default_pool_size: '200'
41. CLOUD NATIVE POSTGRESQL IN KUBERNETES //SGBackupConfig: K8s Secret for AWS Credentials apiVersion: v1 kind: Secret metadata: name: aws-creds-secret type: Opaque data: accessKey: TXlBV1NBY2Nlc3NLZXk= secretKey: VHdvY090aEV0N3VsRWNobWFpdG9vd0FmRGFk
42. CLOUD NATIVE POSTGRESQL IN KUBERNETES //SGBackupConfig apiVersion: stackgres.io/v1beta1 kind: SGBackupConfig metadata: name: backupconfig1 spec: baseBackups: cronSchedule: '*/2 * * * *' retention: 6 storage: type: 's3' s3: bucket: 'sgbackups.sgres.io' awsCredentials: secretKeySelectors: accessKeyId: {name: 'aws-creds-secret', key: 'accessKey'} secretAccessKey: {name: 'aws-creds-secret', key: 'secretKey'}
43. CLOUD NATIVE POSTGRESQL IN KUBERNETES //SGCluster: with Backups and Configurations apiVersion: stackgres.io/v1beta1 kind: SGCluster metadata: name: sg2 spec: postgresVersion: '12.2' instances: 2 sgInstanceProfile: 'size-small' pods: persistentVolume: size: '10Gi' storageClass: 'gp2' configurations: sgPostgresConfig: 'pgconfig1' sgPoolingConfig: 'poolconfig1' sgBackupConfig: 'backupconfig1'
44. CLOUD NATIVE POSTGRESQL IN KUBERNETES //SGCluster: Restore from Existing Backup apiVersion: stackgres.io/v1beta1 kind: SGCluster metadata: name: sg3 spec: postgresVersion: '12.1' instances: 2 sgInstanceProfile: 'size-small' pods: persistentVolume: size: '10Gi' storageClass: 'gp2' initialData: restore: fromBackup: '05a57b57-1f74-44d6-9a8b-563dccefa2fa'
45. CLOUD NATIVE POSTGRESQL IN KUBERNETES DEMO kubectl apply -f https://stackgres.io/downloads/stackgres-k8s/stackgres/0.9-alpha1/demo-operator.yml
46. CLOUD NATIVE POSTGRESQL IN KUBERNETES COMING IN v0.9...
47. CLOUD NATIVE POSTGRESQL IN KUBERNETES //Centralized Logging
48. CLOUD NATIVE POSTGRESQL IN KUBERNETES //STACKGRES.IO https://stackgres.io https://gitlab.com/ongresinc/stackgres