Crunchy Data announces the release of the PGO, the PostgreSQL Operator, 4.7.0 on May 17, 2021.

The PostgreSQL Operator is released in conjunction with the Crunchy Container Suite.

PGO 4.7.0 introduces the following software components:

• pg_partman 4.5.0, a PostgreSQL extension used for partition management.
• pg_cron 1.3.0, a scheduling extension for PostgreSQL.
• TimescaleDB 2.2.0, an open-source database designed to make SQL scalable for time-series data. Timescale, Inc. the company behind TimescaleDB, provides an Apache licensed "community edition" of TimescaleDB that is packaged as a Postgres extension that provides automated partitioning across time and space (partitioning key).

PGO 4.7.0 release includes the following software versions upgrades:

• PostgreSQL is now at 13.3, 12.7, 11.12, 10.17, and 9.6.22.
• PostGIS 3.1 is now available.
• pgBackRest is now at version 2.33.
• pgMonitor is now at 4.5.0. Grafana 7.4 is now required for visualizing the exported metrics.
• Patroni is now at version 2.0.2.
• pgBouncer for CentOS 8 / UBI 8 is rebuilt to use the evdns for its async DNS backend.

Additionally, the UBI 8 builds for PGO and its components now use the ubi8-minimal container as a base.

Major Features

Resize Persistent Volume Claims (Resize PVCs)

There are many things that can cause someone to need more storage in their database system, such as an increase in a backup retention policy or organically through data growth. In Kubernetes, this requires having to increase the size of a PVC.

This release of PGO introduces the ability to resize, i.e. increase the size of, the managed PVCs of the Postgres Operator. These PVCs included:

• The Postgres data PVC, i.e. the PVC that holds your database
  • The PVC of an individual instance in the PostgreSQL cluster
• The pgBackRest repository PVC
• The optional WAL directory PVC
• The pgAdmin 4 PVC

All of these attributes can be edited directly on the pgclusters.crunchydata.com or pgreplicas.crunchydata.com custom resources by editing the size attribute in the correct storage configuration. The new PVC sizes must be larger than the previous PVC sizes. Please see the Custom Resources section of the documentation for more information.

Modifying the size of the Postgres cluster or WAL PVC will cause the Postgres Operator to use a rolling update action to minimize (or eliminate) any downtime that the cluster may have with resizing. This method not only reduces or eliminates downtime, it ensures that the PVC resize functionality is backwards compatible down to Kubernetes 1.11 / OpenShift 3.11.

It is also possible to resize the Postgres cluster, pgBackRest repo, and WAL PVC sizes using the pgo update cluster command with the --pvc-size, --pgbackrest-pvc-size, and --wal-pvc-size flags respectively.

Store Backups in Google Cloud Storage (GCS)

Google Cloud Storage (GCS) is a blob storage service available in the Google Cloud Platform. pgBackRest 2.33 added native support for backup management using GCS. And now, PGO, the Postgres Operator, now natively supports storing backups in GCS!

This release brings equivalent functionality between PGO's support for S3 and GCS. In particular, Postgres Operator 4.7 adds the following attributes to the pgclusters.crunchydata.com custom resource for configuring backup storage with GCS:

• BackrestGCSBucket (required)
• BackrestGCSEndpoint
• BackrestGCSKeyType

The pgBackRest repository Secret now supports a key called gcs-key, which references the GCS credential. For more information on setting up a Postgres cluster with backups stored in GCS using custom resources, please see the custom resources section of the documentation.

Similarly, additional flags are now available in the pgo create cluster command to enable GCS support, including:

• --pgbackrest-gcs-bucket
• --pgbackrest-gcs-endpoint
• --pgbackrest-gcs-key
• --pgbackrest-gcs-key-type

Note that --pgbackrest-gcs-key references a file path in your local environment. The GCS credential is a JSON file; for convenience, the PGO client will accept the file and handle the upload.

The two parameters that are required are the GCS bucket name and the GCS key; pgBackRest can figure out the rest.

Note that in a "hybrid" setup, you can only use "posix,gcs"; "s3,gcs" is not supported at this time. In other words, the following storage types are supported:

• posix
• s3
• gcs
• posix,s3
• posix,gcs

For more information, please refer to the documentation

Restore / Clone a Cluster Across Namespaces

The Postgres Operator now allows you to create a Postgres cluster in a different namespace from the source cluster. This is useful, for example, when trying to copy data from a production cluster that is in a production namespace into a development cluster in a development namespace.

For example, to create a cluster called hippo from data in a cluster called elephant in namespace production, you can execute the following command:

pgo create cluster hippo --restore-from=elephant --restore-from-namespace=production

and the Postgres Operator will create hippo from elephant. This also works by setting the namespace attribute in the restoreFrom block in the pgclusters.crunchydata.com custom resource.

Note that the Postgres Operator needs to have sufficient privileges in both namespaces to use this feature. For more information, please read about Namespace Management in the documentation.

Monitoring Improvements

This release brings several enhancements to monitoring Postgres clusters managed by PGO, including support for Grafana 7.4. These include:

• New query performance insights, including the standard "top N" views (longest running queries, average runtime, total queries run) to help with performance troubleshooting.
• Enhancements to information available about the state of backups, including the backup recovery window, the total number of missing WAL archives, the total size of backups, and more.
• A new "alerting dashboard" that provides summary information about system health and alert history.

There are also general improvements to the overall user experience of the monitoring visualizations.

Enable / Disable TLS in Active PostgreSQL Clusters

It's common to begin designing a Postgres cluster in development and then, upon bringing it to production, add TLS support to it.

This release of PGO, the Postgres Operator from Crunchy Data, allows you to enable or disable TLS in an active Postgres cluster. This has the added benefit of being able to point a Postgres cluster at different Secrets, e.g. for rotation.

The pgclusters.crunchydata.com custom resource now allows for the following attributes in its specification to be edited:

• tls.caSecret
• tls.replicationTLSSecret
• tls.tlsSecret
• tlsOnly

Additionally, the following flags are now available on the pgo update cluster command:

• --disable-server-tls: removes TLS from a cluster
• --disable-tls-only: removes the TLS-only requirement from a cluster
• --enable-tls-only: adds the TLS-only requirement to a cluster
• --server-ca-secret: combined with --server-tls-secret, enables TLS in a cluster
• --server-tls-secret: combined with --server-ca-secret, enables TLS in a cluster
• --replication-tls-secret: enables certificate-based auth between Postgres instances.

To enable TLS in an active Postgres cluster, you need to ensure that both the server CA Secret and the server TLS secret are set.

Note that PGO will rewrite some of your HBA rules when performing any TLS enable/disable updates. While it will do its best to preserve custom rules, this is not a guarantee, and if you have customized your HBA rules, you should inspect your config after.

Breaking Changes

• The --compress / --no-compress flag for the pgBackRest backup options (--backup-opts) is removed. Please use the --compress-type flag instead.
• The Namespace attribute is removed from the Spec of pgclusters, pgreplicas, and pgtasks as it was superfluous.

Features

• Custom labels can now be added and removed through the userlabels attribute on the pgclusters.crunchydata.com custom resource, in addition to extending this functionality of the pgo label and pgo delete label commands.
• Custom labels are now applied to all of the managed Kubenretes objects associated with a Postgres cluster. These include: Deployments, Jobs, Pods, PVCs, ConfigMaps, and Secrets.
• The default password hashing mechanism (scram-sha-256, md5) for PostgreSQL users can now be selected using the --password-type flag on pgo create cluster. The passwordType attribute on the pgclusters.crunchydata.com custom resource can also be used for this purpose.
• The size of the pgAdmin4 PVC can be set with the --pvc-size flag on the pgo create pgadmin command.
• The storage configuration for pgAdmin 4 is now configurable. This can be configured either in the pgo.yaml ConfigMap in the PGAdminStorage section, or via the --storage-config flag on the pgo create pgadmin command. If nothing is set, it will default to the configuration based on PrimaryStorage.
• The s3bucketname attribute on the pgclusters.crunchydata.com can now be edited in an existing cluster.

Changes

• readOnlyRootFileSystem is now enabled by default on containers. This also coincides with a change in how some of the entrypoints are set in order to guarantee compatibility with OpenShift 3.11.
• The --compress-type flag is now supported for the backup options (--backup-opts) for pgBackRest backups with pgo backup. none, gz, bz2, and lz4 are all supported. Presently zst is not supported.
• The Postgres Operator and associated containers now contain defaults to use more locked down Pod and Container security context settings. These include setting readOnlyRootFileSystem to true, allowPrivilegeEscalation to false, and explicitly stating that the container should not run as root. Many of these were already honored, if not defaulted, within the Postgres Operator ecosystem, but these changes make the settings explicit. This is all configuration: there are no breaking changes, and these configurations can be supported down to at least the 4.2 series.
• Ensure that at least one replica is created when sync replication is chosen during cluster creation.
• Allow for the PGOADMIN_USERNAME, PGOADMIN_PASSWORD, PGOADMIN_ROLENAME credential bootstrap variables to be overridden as part of the OLM and development install process. Contributed by Mathieu Parent (@sathieu).
• Revert setting "UsePAM" to "yes" by default as the bug fix in Docker that required that change was applied roughly one year ago.
• PGO Apiserver now requires a minimum of TLS 1.2 to connect. Additionally, the ciphersuites that can be used are further restricted to be a more secure set while still maintaining FIPS compatibility. Contributed by Steve Kerrison (@stevekerrison).
• The post-cluster creation pgBackRest tasks, i.e. creating a stanza and creating an initial backup, are now deleted by the Operator should they complete successfully. Besides good housekeeping, this provides a workaround for an OpenShift 3.11 bug that was causing the Operator to continuously issue pgBackRest backups during an OpenShift refresh cycle. Reported by Paul Heinen (@v3nturetheworld).
• The container ports are now named, which may make it easier to integrate PGO with external systems. Suggested by Aleksander Roszig (@AleksanderRoszig).
• On Operator boot, Automatically detect when deployed in an OpenShift environment and set DisableFSGroup to true. This makes it easier to get started with the Postgres Operator in an OpenShift environment with the default security settings (i.e. restricted). If you use the anyuid Security Context Constraint, you will need to explicitly set DisableFSGroup to false.
• Update Helm installer to follow appropriate conventions. Contributed by Jakub Ráček (@kubaracek)

Fixes

• Ensure archive_mode is forced to on when performing using the "restore in place" method. This ensures that the timeline is correctly incremented post-restore, which could manifest itself with various types of WAL archive failures.
• Only attempts to start scheduled backups in running pgBackRest repository Pods. Reported by Satria Sahputra (@satriashp).
• Fix error when attempting to perform restores when using node affinity. Reported by (@gilfrade) and Cristian Chiru (@cristichiru).
• Fix crash due to superfluous trailing whitespace when parsing --backup-opts in pgo backup. Reported by Samir Faci (@safaci2000).
• Fix issue with newer versions of PostgreSQL where a replica would automatically restart after a configuration change. Now the replica will only show that it is pending a restart; a user will have to run pgo restart.
• Fix issue where metrics about pgBackRest backups could not be scraped if the backups were stored in a S3-like environment that requires the use of disabling TLS verification. Reported by (@lphan-clv) and (@dakine1111).
• Fix issue where certain pgAdmin 4 functions did not work (e.g. taking a backup) due to python references in EL8 containers. Reported by (@douggutaby).
• Fix how the pgAdmin 4 Service is identified in pgo test. Prior to this, it was identified as a "primary"; now it is "pgadmin".
• Ensure a Postgres cluster shutdown can execute even if the status subresource of a pgclusters.crunchydata.com custom resource is missing.
• Support the substitution for the limit on the number of queries to include the the pg_stat_statements support of pgMonitor. Defaults to 20, which is the pgMonitor upstream value. Contributed by Steven Siahetiong (@ssiahetiong).
• Ensure sshd_config is correctly set on an upgrade. This could have manifested with some pgBackRest functionality not working. This can be manually fixed by setting UsePAM no in the sshd_config file in a cluster. Reported by (@douggutaby)
• Ensure major upgrades via crunchy-upgrade support PostgreSQL 12 and PostgreSQL 13. Reported by (@lbartnicki92).
• The pgo-deployer and Ansible installer will no longer create an initial TLS secret for the PGO apiserver. PGO apiserver has been able to self-create this for a long time, and PGO defers to that. This fixes an issue that occurred on newer builds where certificates generated by OpenSSL contained incomplete usage blocks, which could cause for these certificates to be properly outright rejected.
• Fix installed RBAC permissions via OLM. Reported by Tim Bo (@timbrd), with additional analysis from Aleksander Roszig (@AleksanderRoszig) and Eric Ace (@aceeric).
• Fix nonbreaking error message that occurs when pgo-scheduler container shuts down in the UBI 8 base container.