Percona Distribution for PostgreSQL Operator 1.1.0 – Notable Features

Percona in 2021 is heavily invested in making the PostgreSQL ecosystem better and contributing to it from different angles:

• We have created pg_stat_monitor – query performance monitoring tool for PostgreSQL
• Percona Distribution for PostgreSQL Operator was released in October
• Greatly improved PostgreSQL monitoring with Percona Monitoring and Management
• At PGConf NYC 2021 we were a Platinum sponsor and had 5 awesome speakers sharing their wisdom with the community

With this in mind let me introduce to you Percona Distribution for PostgreSQL Operator version 1.1.0 and its notable features:

• Smart Update – forget about manual and error-prone database upgrades
• System Users management – add and modify system users with ease with a single Kubernetes Secret resource
• PostgreSQL 14 support – leverage the latest and greatest by running Percona Distribution for PostgreSQL on Kubernetes

Full release notes can be found here.

Smart Update Feature

Updating databases and their components is always a challenge. In our Operators for MySQL and MongoDB we have simplified and automated upgrade procedures, and now it’s time for PostgreSQL. In the 1.1.0 version, we ship this feature as Technical Preview with a plan to promote it to GA in the next release.

This feature consists of two parts:

• Version Service – get the latest or recommended version of the database or other component (PMM for example)
• Smart Update – apply new version without downtime

Version Service

This feature answers the question: which PostgreSQL/pgBackRest/pgBouncer version should I be running with this Operator? It is important to note, that Version Service and Smart Update can only perform minor version upgrades (ex. from 13.1 to 13.4). Major Version upgrades are manual for now and will be automated in the Operator soon.

The way it works is well depicted on the following diagram: 

Version Service is an open source tool, see the source code on Github. Percona hosts check.percona.com and Operators use it by default, but users can run their own self-hosted Version Service.

Users who worked with our Operators for MySQL and MongoDB will find the configuration of Version Service and Smart Update quite familiar:

• Define Version Service endpoint
• Define PostgreSQL version – Operator will automatically figure out components versions
• Schedule defines the time when the rollout of newer versions is going to take place. Good practice to set this time outside of peak hours.

Smart Update

Okay, now Operator knows the versions that should be used. It is time to apply them and do it with minimal downtime. Here is where the Smart Update feature kicks in. 

The heart of Smart Update is smartUpdateCluster function. The goal here is to switch container images versions for database components in a specific order and minimize downtime. Once the image is changed, Kubernetes does the magic. For Deployment resources, which we use in our Operator, Kubernetes first spins up the Pod with a new image and then terminates the old one. This provides minimal downtime. The update itself looks like this:

1. Upgrade pgBackRest image in Deployment object in Kubernetes
2. Start upgrading PostgreSQL itself

   1. Percona Monitoring and Management which runs as a sidecar gets the new version here as well
   2. Same for pgBadger
   3. We must upgrade replica nodes first here. If we upgrade the primary node first, the cluster will not recover. The tricky part here, is that in an event of failover Primary node can be somewhere in the pgReplicas Deployment. So we need to verify where the primary is first and only after that change the image. See the Smart Update sequence diagram for more details. 

3. Last, but not least – change the image for pgBouncer. To minimize the downtime here, we recommend running at least two pgBouncer nodes. By default pgBouncer.size is set to 3.

As a result, the user gets the latest, most secure, and performant PostgreSQL and its components automatically with minimal downtime.

System Users Management

Our Operator has multiple system users to manage the cluster and ensure its health. Our users raised two main concerns:

• it is not possible to change system user password with the Operator after cluster deployment
• it is confusing that there is a Secret object per user

In this release, we are moving all system users to a single Secret. The change in the Secret resource is going to trigger the update of the passwords in PostgreSQL automatically.

If the cluster is created from scratch the Secret with system users is going to be created automatically and passwords would be randomly generated. By default the Secret name is <clusterName>-users, it can be changed under spec.secretUsers variable in the Custom Resource.

When upgrading from 1.0.0 to 1.1.0, if you want to keep old passwords, please create the Secret resource manually. Otherwise, the passwords for system users are going to be generated randomly and updated by the Operator.

PostgreSQL 14 Support

PostgreSQL 14 provides an extensive set of new features and enhancements to security, performance, usability for client applications, and more.

Most notable of them include the following:

• Expired B-tree index entries can now be detected and removed between vacuum runs. This results in a lesser number of page splits and reduces the index bloat.
• The vacuum process now deletes dead tuples in a single cycle, as opposed to the previous 2-step approach of first marking tuples as deleted and then actually freeing up space in the next run. This speeds up free space cleanup.
• Support for subscripts in JSON is added to simplify data retrieval using a commonly recognized syntax.
• Stored procedures can accept OUT parameters.
• The libpq library now supports the pipeline mode. Previously, the client applications waited for a transaction to be completed before sending the next one. The pipeline mode allows the applications to send multiple transactions at the same time thus boosting performance.
• Large transactions are now streamed to subscribers in-progress, thus increasing the performance. This improvement applies to logical replication.
• LZ4 compression is added for TOAST operations. This speeds up large data processing and also improves the compression ratio.
• SCRAM is made the default authentication mechanism. This mechanism improves security and simplifies regulatory compliance for data security.

In the 1.1.0 version of PostgreSQL Distribution for PostgreSQL Operator, we enable our users to run the latest and greatest PostgreSQL 14. PostgreSQL 14 is the default version since this release, but you still can use versions 12 and 13.

Conclusion

Kubernetes Operators are mainly seen as the tool to automate deployment and management of the applications. With this Percona Distribution for PostgreSQL Operator release, we simplify PostgreSQL management even more and enable users to leverage the latest version 14. 

We encourage you to try out our operator. See our github repository and check out the documentation.

Found a bug or have a feature idea? Feel free to submit it in JIRA.

For general questions please raise the topic in the community forum. 

You are a developer and looking to contribute? Please read our CONTRIBUTING.md and send the Pull Request.