Running Postgres in production requires “a RedHat” of PostgreSQL. A curated set of open source components built, verified and packaged together. In this sense Postgres is like the Linux kernel, it needs many components around it to provide what a Linux distribution provide.
Exists an ecosystem of tools built around Postgres that can be used to build a Postgres distribution. This is what we call the stack of components.
Chosing the right component of this stack is an hard task. Exists many components that overlap functionalities or have pros and cons to take into account before chosing one over another. It is required an high understanding of all the components in order to chose the ones that fit together and provide a production ready Postgres distribution.
Our Postgres distribution is composed on a central core component (Postgres) and some other components that fulfill requirements in each different area required in the Postgres production distribution.
The main container used for a Postgres cluster node uses an UBI 8 minimal image as its base to which is added a vanilla PostgreSQL v11, v12. It uses a persistent storage configured via StorageClass. Is always deployed with a sidecar util container to allow access for a system/database admisitrator.
Run PostgreSQL using default configuration is a really bad idea in a production environment. PostgreSQL uses very conservative defaults so it must be tuned in order to achieve good performance of the database. Exists some places where you can find information about Postgres configuration parameters and best practices:
StackGres is tuned by default to achieve better performance than using the default configuration. User can still be configured by user in order to give the flexibility that some users needs.
Connecting directly to PostgreSQL does not scale very well.
Connection pooling is required in order to not saturate PostgreSQL processes by creating queue of sessions, transactions or statements (depending on the application requirements).
Exists 3 alternatives solutions:
Which one to chose?
For StackGres PgBouncer was the chosen solution. It is enough simple and stable to be used for connection pooling. It has a cons that is the lack of multithreading that can lead to a saturation of CPU when connections increase over a vertain limit that depends on the perfromnace of a sigle core of the CPU where it is running. Odyssey will be a good candidate to replace PgBouncer when it will become more mature.
If a Postgres instance goes down or is not working properly we want our cluster to recover by chosing a working instance to convert to the new master and configure all the other instances and the application to point to this new master. We want all this to happen without manual intervention.
A high availability solution allow to achieve this feature. Exists many solutions to this problem and is really hard to chose one among them:
Patroni is the HA solution chosen for StackGres. It is a well proved solution that relies on distributed consensus algorithms in order to provide a consistent mechanism for master election. In particular it is able to use the same distributed consensus algorithm used by Kubernetes so that it does not requires installation of other services.
Backup tools solutions are also a very higly populated ecosystem:
Also, where do we store our backups?
And finally, will our backup work when needed or will it fail?
Wal-g, the successor of Wal-e, is the most complete and lightweight solution to provide both incremental (trought archive
command) and full backup support. Also, it provides out of the box features that allow store backup in a persistent volume
(using a storage class that supports
ReadWriteMany access mode) or a cloud storage between AWS S3, Google Cloud Storage
or Azure Blob Storage. It also allow configure aspects like bandwidth or disk usage rate.
We want to sotre our logs distributed across all our containers in a central location and be able to analyze them when needed. It does not exists a good solution for that so you have to build one. Exists fluentd and Loki, this last does not work very well with Postgres. An alternative is to store all the logs in Postgres using Timescale.
How do I locate the master, if it might be changing? How do I obtain traffic metrics? It is possible to manage traffic: duplicate, A/B to test cluster or event inspect it?
Envoy is an open source edge and service proxy, designed for cloud-native applications. It is extensible in order to provide advanced funcionality based on the actual traffic (for example the Postgres could be parsed in order to offer stats) or on connection characteristic (like the TLS certificate in order to chose to which node the connection have to be dispatched.
Which monitoring solution can we use to monitor a Postgres cluster?
StackGres approach here is to enable as much monitoring solution as possible. Currently only Prometheus can connect to StackGres stats using the PostgreSQL Server Exporter.
Exists some user interface to interact with Postgres like pgadmin or dbviewer that allow to look at the database content and configuration. We need a user interface that is capable of manage an entire cluster. How do I list the clusters? How many nodes have a cluster? What is the status of replication? How many resources are used by a node? How to get monitoring info of a particular node?
StackGres provide a Web and CLI user interface able to monitor and interact with the created StackGres clusters. It allow to do basic and advanced tasks like list/get/create/update/delete a cluster or execute a switchover or a backup recovery.
The Web interface is also able to integrate with Grafana in order to allow see stats of each node of the cluster.