PostgreSQL On Kubernetes Experiences
Published on August 29, 2022 by Stefanie Janine Stölting
PostgreSQL postgres Kubernetes K8s pgo postgres-operator
8 min READ
The current hype is to put PostgreSQL databases into Kubernetes. Obviously it is adding another layer on top of the stack.
That is something, that one always should have in mind, especially, if it is planned, to have it running in production.
It adds to the stack of your DBAs, that they no have to handle K8s YAML configuration files as implemented by an operator, too.
Also the access to the database server differs. What a DBA has learned about how to do proper backups and, more important, test backups and restore backups, does not work as learned.
Changing a configuration by just some Ansible scripts, or using shell scripts, is not possible anymore. The changes have always to be done in the YAML files, which are either, plain Kustomize, or by declarative configuration with Helm charts.
Important Note
I have changed some information previously posted in this blog. It is stated with date and by whom I got that information. The information partly came from a thread at LinkedIn and by email.
None of these new information has been tested, only added links pointing to where to find these information.
History
As K8s is originally about fire up a pod containing apps, it didn’t have permanent storage at the beginning. But without the option of storing data permanently, having databases in pods has not been an option. That changed, when Persistent Volumes have been added.
The promise seems to be, that it’s easier to just setup something, where PostgreSQL is running and not to care about the database. Which, obviously, is not how it works.
PostgreSQL Operators
Currently there are several open sourced operators for PostgreSQL available. Though, lets have a look to them. The sort order of the operators is alphabetical.
The ones compared here are first of all available under open source licenses. In addition all of them do implement high availability.
CloudNativePG
It has been originally developed by EDB, but has been outsourced into a public organization.
The repository is on github.com/cloudnative-pg/cloudnative-pg.
Percona Operator For PostgreSQL
Percona forked PGO from Crunchy Data, the source is available at github.com/percona/percona-postgresql-operator.
PGO by Crunchy Data
You’ll find the repository at github.com/CrunchyData/postgres-operator.
postgres-operator By Zalando
The repository is available available on GitHub at github.com/zalando/postgres-operator.
StackGres
OnGres developed another operator, that is also available on GitHub, github.com/ongres/stackgres.
Operator Feature Comparison
| Operator | PostgreSQL Version | High Availability | Backups | Connection Pooler | Extensions | Minor Upgrades | Major Upgrades |
|---|---|---|---|---|---|---|---|
| CloudNativePG | 14 | Postgres instance manager | Barman | PgBouncer | - auto_explain - pg_stat_statements - pgAudit - PostGIS1 | ✔ | ✔ |
| Percona Operator For PostgreSQL | 14 | Patroni | pgBackRest | PgBouncer | - Contrib Modules - pgAudit - wal2json - set_user | ✔ | |
| PGO | 14 | Patroni | pgBackRest | PgBouncer | - Contrib Modules - pgAudit - wal2json - set_user - pgAudit Analyze - pg_cron - pg_partman - pgnodemx - TimescaleDB2 - PostGIS | ✔ | ✔ |
| postgres-operator | 14 | Patroni | pg_basebackup / WAL-E3 & WAL-G via Spilo | PgBouncer | - 4 - pg_partman - PostGIS - pg_cron - pgAudit - TimescaleDB - wal2json - pg_stat_kcache - pg_permissions - pg_repack - pglogical - postgresql-hll - pldebugger - pgextwlist - plantuner - plpgsql_chec - pg_dirtyread - hypopg - plproxy | ||
| ✔ | ✔ | ||||||
| StackGres | 14 | Patroni | WAL-G | PgBouncer | listed here 5 | ✔ | ✔ |
Footnodes:
- Via postgis-containers
- Apache-licensed community edition
- WAL-E is obsolete
- I’ve found some mentioned in the documentation, but no list of supported extensions, some have been mentioned by Alexander Kukushkin
- Supports more extensions, as what I’ve found for the other operators, including some foreign data wrapper
Conclusions
So far I took a look at all operators, except the one by Percona, but as it is based on PGO, it should be alike that one.
No matter which one you prefer, there are some things to always have in mind: You need to adjust the settings for PostgreSQL as it is nearly unusable with its default configuration. It happened to me, that I didn’t adjust the WAL settings and running pgbench resulted in incomplete WAL files with errors in the PostgreSQL log file.
Also keep in mind, that it is forbidden to create databases outside of the YAML configuration, I ended up with a database, that could not be restored from a backup because of missing entries in the pg_hba.conf file. Though, with tWAL-Ghat knowledge, as soon as your PostgreSQL is running in K8s, remove create database from your valid SQL statements.
All of the operators have gaps in their documentations, or sometimes it’s hard, to find the correct documentation.
One can get commercial support for all operators, except the one by Zalando.
CloudNativePG
With CloudNativePG I see a problem when it comes to high availability. The de-facto standard here nowadays is Patroni, as it’s by far the most used one, AFAIK. A big advantage of Patroni is, that it takes care, that all nodes are running the same configuration.
Changed on 2022-08-30
I haven’t found any information about how to do minor PostgreSQL updates, nor how to do major ones.
I’ve got a hint from Lætitia Avrot about where to find the documentation about updates, therefore I changed it. It is also in the pipeline to support major PostgreSQL updates.
Changed on 2022-09-01
According to Gabriele Bartolini CloudNativePG does support PostgreSQL major upgrades, it is done with importing Postgres databases.
PGO
So far the only disadvantage is, that it doesn’t support PosgreSQL major upgrades, but Crunchy Data announced some time ago, that they are working on it.
Changed on 2022-08-31
Andrew L’Ecuyer from Crunchy Data contacted me and gave me the link to his blog post where it is stated, thtat PGO supports PostgreSQL major upgrades since 5.1.
He also told me, that Crunchy Data will look into the problem to restore a database, that has been created with a SQL statement instead of created through the operator.
postgres-operator
The postgres-operator by Zalando has one problem for me, it does not come with proper backup tool. pg_basebackup is better than pg_dump, which shouldn’t be considered a backup at all. As WAL-E has been obsolete, it should not be used. One can’t be sure, whether it will work on with newer PostgreSQL versions.
Changed on 2022-09-01
According to Alexander Kukushkin Zalando has committed to maintain a fork of WAL-E to keep it alive. In addition, the operator does also support WAL-G for backups.
pg_basebackup is automatically used by Patroni to build replicas.
The operater does also support additional PostgreSQL extensions, they have been added.
StackGres
StackGres has the same problem to me, as the postgres-operator: It uses pg_basebackup. In additon it does not support the current version as of writing is 14, while 15 is already on the brink.
Changed on 2022-09-01
I haven’t found any information about how to do minor PostgreSQL updates, nor how to do major ones.
According to Sebastian Webber in a comment on LnkedOn is stating, that StackGres is supporting both, minor and major PostgreSQL upgrades. It is documented deep in their documentation: SGDbOps.
From what I have seen there, there are some objects, that can be filled with values, but I have not seen any examples.
According to Álvaro Hernández Tortosa StackGres does support PostgreSQL 14 even while the GitLab and GitHub README pages of the project explicitly stating to support only PostgreSQL 12 and 13.
It is hidden in a release note of a beta version released on 5th of November 2021.
He also stated, that StackGres is using WAL-G for backups. The documentation at SGBackupConfig says “basebackup”, WAL-G is not mentioned in the section about backups, nor in the documentation.
Changed on 2022-09-10
Álvaro Hernández Tortosa believes that it was not enough to correct the supported version information and that I should change my blog post. He stated, that this information is very easy to find in the documentation of StackGres, “- Documentation -> Introduction -> Versions - Documentation -> Search -> “Postgres versions””. He shared it on LinkedIn with several comments, that I should “correct” my blog post. I should also not have used search engines as the documentation supports searching directly. You can read them here: LinkedIn 2022-09-06, LinkedIn 2022-09-09, LinkedIn 2022-09-09.
In my personal experiences while having written this blog post, I have not seen, that this operator supports PostgreSQL 14, nor have I found any information while using different search engines. And while writing this, the README on GitHub still states, that StackGres is supporting PostgreSQL 12 and 13.
After having this personal experiences, I will not include StackGres in any future blog posts.
Author: Stefanie Janine Stölting