Concepts

PostgreSQL Overview​

PostgreSQL is a popular, open-source relational database management system. Deploying PostgreSQL on a Kubernetes (k8s) cluster is a popular approach for creating scalable, resilient, and dynamic database environments. The integration of PostgreSQL with Kubernetes provides significant advantages such as enhanced scalability, resilience, and efficient resource utilization. With PDS, users can leverage core capabilities of Kubernetes to deploy and manage their PostgreSQL deployments using a cloud-native, container-based model.

PDS follows the release life cycle of the upstream PostgreSQL project, meaning new releases are available on PDS shortly after they are generally available. Likewise, older versions of PostgreSQL are removed once they have reached end-of-life. The list of currently supported PostgreSQL versions can be found here.

Similar to all data services in PDS, PostgreSQL deployments run within Kubernetes. PDS includes a component called the PDS Deployments Operator which manages the deployment of all PDS data services, including PostgreSQL. The operator extends the functionality of Kubernetes by implementing a custom resource called postgresql. This resource type represents a PostgreSQL instance allowing standard Kubernetes tools to be used to manage PostgreSQL deployments, including scaling, monitoring, and upgrading.

You can learn more about the PDS architecture here.

Clustering​

Within PDS, PostgreSQL is deployed using the Patroni clustering, which ensures high availability, automated failover, and efficient replication, greatly enhancing the reliability and resilience of their database environments.

Key Features of Patroni

1. Automated Failover and Recovery: Patroni provides automated failover and recovery mechanisms, allowing the system to promptly respond to node failures by promoting standby replicas to primary roles without human intervention. This ensures minimal downtime and continuous availability.

2. Replication Management: It supports synchronous and asynchronous replication setups, ensuring that data is consistently replicated across various nodes. Patroni can work with PostgreSQL’s built-in replication mechanisms or integrate with more advanced replication solutions.

3. Easy Integration with Existing Systems: Patroni is designed to work seamlessly with existing PostgreSQL setups and various cloud environments, including Kubernetes. It can be integrated with other cluster management tools and orchestration platforms.

Although PDS allows PostgreSQL to be deployed with any number of nodes, high availability can only be achieved when running with three or more nodes. Smaller clusters should only be considered in development environments. When using multi-node PostgreSQL deployments, one node is elected as the primary and can service write requests; all other nodes will run as secondaries and can service read requests. In the event that the primary node fails, an automatic election process by Patroni promotes an eligible secondary to serve as the primary node. PDS provides endpoints like readonly (ro)/readwrite (rw)/round-robin (rr) that ensure client requests are automatically routed to the correct database node(s).

When deployed as a multi-node cluster, individual nodes, deployed as pods within a statefulSet, automatically discover each other to form a cluster. Node discovery within a PostgreSQL cluster is also automatic when pods are deleted and recreated or when additional nodes are added to the cluster via horizontal scaling.

PDS leverages the fault tolerance of PostgreSQL native clustering capabilities by spreading PostgreSQL servers across Kubernetes worker nodes when possible. PDS utilizes the Stork, in combination with Kubernetes storageClasses, to intelligently schedule pods. By provisioning storage from different worker nodes, and then scheduling pods to be hyper-converged with the volumes, PDS deploys PostgreSQL clusters in a way that maximizes fault tolerance, even if entire worker nodes or availability zones are impacted.

Refer to the PDS architecture to learn more about Stork and scheduling.

Replication​

Application Replication​

When deployed as a multi-node cluster, one node is elected as the cluster’s primary node; every other node in the deployment will serve as a read replica and will, thus, maintain a copy of the data.

Application replication via PostgreSQL Patroni Cluster is used to achieve high availability and can be leveraged to distribute read workloads. With data replicated to multiple nodes, more nodes are able to respond to read requests for data. Likewise, in the event of primary node failure, an eligible secondary node can be promoted to become the cluster’s new primary node. Note that additional nodes will not enhance write performance since only one node, the elected primary, can service write requests; consider vertical scaling when additional write performance is required.

Storage Replication​

PDS takes data replication further. Storage in PDS is provided by Portworx Enterprise, which itself allows data to be replicated at the storage level.

Each PostgreSQL server in PDS is configured to store data to a persistentVolume which is provisioned by Portworx Enterprise. These Portworx volumes can, in turn, be configured to replicate data to multiple volume replicas. It is recommended to use two volume replicas in PDS in combination with application replication in PostgreSQL.

While the additional level of replication will result in write amplification, the storage-level replication solves a different problem than what is accomplished by application-level replication. Specifically, storage-level replication reduces the amount of downtime in failure scenarios. That is, it reduces RTO.

Portworx volume replicas are able to ensure that data is replicated to different Kubernetes worker nodes or even different availability zones. This maximizes the ability of the Kubernetes API scheduler to schedule PostgreSQL pods.

For example, in cases where Kubernetes worker nodes are unschedulable, pods can be scheduled on other worker nodes where data already exists. Moreover, pods can start instantly and service traffic immediately without waiting for PostgreSQL to replicate data to the pod.

Configuration​

PostgreSQL server configurations can be tuned by specifying setting-specific environment variables within the deployment’s application configuration template.

You can learn more about application configuration templates here. The list of all PostgreSQL server configurations that may be overridden is itemized in the PostgreSQL service’s reference documentation here.

Scaling

​ Because of the ease with which databases can be deployed and managed in PDS, it is common for customers to deploy many of them. Likewise, because it is easy to scale databases in PDS, it is common for customers to start with smaller clusters and then add resources when needed. PDS supports both vertical scaling (i.e., CPU/memory), as well as horizontal scaling (i.e., nodes) of PostgreSQL clusters.

Vertical Scaling​

Vertical scaling refers to the process of adding hardware resources to (scaling up) or removing hardware resources from (scaling down) database nodes. In the context of Kubernetes and PDS, these hardware resources are virtualized CPU and memory.

PDS allows PostgreSQL servers to be dynamically reprovisioned with additional or reduced CPU and/or memory. These changes are applied in a rolling fashion across each of the pods in the PostgreSQL cluster.

Horizontal Scaling​

Horizontal scaling refers to the process of adding database nodes to (scaling out) or removing database nodes from (scaling in) a cluster. Currently, PDS supports only scaling out of clusters. This is accomplished by adding additional pods to the existing PostgreSQL cluster by updating the replica count of the statefulSet.

For PostgreSQL, adding nodes equates to adding additional standby nodes, which can serve as read replicas. Thus, horizontal scaling can be used as a means to scale a deployment's read capacity by distributing read workloads over a large number of nodes.

Connectivity​

PDS manages pod-specific services whose type is determined based on user input (currently, only LoadBalancer and ClusterIP types are supported). This ensures a stable IP address beyond the life cycle of the pod. For each of these services, an accompanying DNS record will be managed automatically by PDS. Connection information provided through the PDS UI will reflect this by providing users with an array of PostgreSQL server endpoints. Specifying some or all individual server endpoints in client configurations will distribute client requests and mitigate connectivity issues in the event of any individual server being unavailable.

PDS will create a default administrator user called pds which can be used to connect to the database initially. This user can be used to create additional users and can be dropped if needed. Instance-specific authentication information, including the pds user’s password, can be retrieved from the PDS UI within the instance’s connection details page.

Service Endpoint Details

Service Name	Details
pg-<name>-<namespace>-<pod-id>-vip	Endpoint for each pod
pg-<name>-<namespace>-rw	Endpoint to master node (read/write)
pg-<name>-<namespace>-ro	Endpoint to all replica nodes (read only)

Backup and Restore​

Backup and restore functionality for PostgreSQL in PDS is enabled by Pgbackrest, an open-source backup utility for PostgreSQL. Backups can be taken ad hoc or can be performed on a schedule.

To learn more about ad hoc and scheduled backups in PDS, refer to the PDS backups docs.

Backups​

Pgbackrest is configured to take a backup of all databases within the PostgreSQL deployment and to store the data to a dedicated Portworx volume.

The backup volume is shared across all nodes in the PostgreSQL deployment. By using a dedicated volume, the backup process does not interfere with the performance of the database.

Type of backups:

1. Full backup: A full database physical backup will be taken. The incremental backup feature will be supported in the future.

2. WAL archive (WAL backup): By default, the database will be archiving WAL files to the backup location whenever new WAL files are created. These WAL archives will be removed when a full backup happens. It is recommended to have scheduled full backups to avoid filling the backup filesystem.

Once Pgbackrest has completed the backup, the PDS Backup Operator makes a copy of the volume to a remote object store. This feature, which is provided by Portworx Enterprise’s cloud snapshot functionality, allows PDS to fulfill the 3-2-1 backup strategy – three copies of data on two types of storage media with one copy offsite.

Restore​

Restoring PostgreSQL in PDS is done out-of-place. That is to say, PDS will deploy a new PostgreSQL cluster and restore the data to the new cluster. This prevents users from accidentally overwriting data and allows users to stand up multiple copies of their databases for debugging or forensic purposes.

note

PDS does not currently support backup/restoration of specific databases or tables (logical objects). As with restoring any database, loss of data is likely to occur. Because user passwords are stored within the database, restoring from backup will revert any password changes. Therefore, in order to ensure access to the database, users must manage their own credentials.

Monitoring​

With each PostgreSQL server, PDS bundles a Prometheus exporter for monitoring purposes. This exporter is provided by the Prometheus community and makes PostgreSQL metrics available in the Prometheus text-based format. For a full list of metrics, see the reference documentation for PostgreSQL.

The metrics captured by PDS are available for export from the PDS Control Plane. A Prometheus endpoint is available for use with standard tools such as Grafana. For more information about monitoring and data visualization of metrics, see the PDS Platform documentation.