Postgres clusters

StackGres PostgreSQL cluster can be created using a cluster Custom Resource (CR) in Kubernetes.


Kind: SGCluster

listKind: SGClusterList

plural: sgclusters

singular: sgcluster


Spec

Property Required Updatable Type Default Description
postgresVersion string

Postgres version used on the cluster. It is either of:

  • The string ‘latest’, which automatically sets the latest major.minor Postgres version.
  • A major version, like ‘12’ or ‘11’, which sets that major version and the latest minor version.
  • A specific major.minor version, like ‘12.2’.
instances integer
Number of StackGres instances for the cluster. Each instance contains one Postgres server. Out of all of the Postgres servers, one is elected as the master, the rest remain as read-only replicas.
sgInstanceProfile string will be generated
Name of the SGInstanceProfile. A SGInstanceProfile defines CPU and memory limits. Must exist before creating a cluster. When no profile is set, a default (currently: 1 core, 2 GiB RAM) one is used.
pods object
Cluster pod’s configuration.
configurations object
Cluster custom configurations.
prometheusAutobind boolean false
If enabled, a ServiceMonitor is created for each Prometheus instance found in order to collect metrics.
initialData object
Cluster initialization data options. Cluster may be initialized empty, or from a backup restoration. Specifying scripts to run on the database after cluster creation is also possible.
distributedLogs object
StackGres features a functionality for all pods to send Postgres, Patroni and PgBouncer logs to a central (distributed) location, which is in turn another Postgres database. Logs can then be accessed via SQL interface or from the web UI. This section controls whether to enable this feature or not. If not enabled, logs are send to the pod’s standard output.
postgresServices object
Kubernetes services created or managed by StackGres.
nonProductionOptions array

Example:

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  instances: 1
  postgresVersion: 'latest'
  pods:
    persistentVolume:
      size: '5Gi'
  sgInstanceProfile: 'size-xs'

Pods

Cluster’s pod configuration

Property Required Updatable Type Default Description
persistentVolume object
Pod’s persistent volume configuration.
disableConnectionPooling boolean false
If set to true, avoids creating a connection pooling (using PgBouncer) sidecar.
disableMetricsExporter boolean false
If set to true, avoids creating the Prometheus exporter sidecar. Recommended when there’s no intention to use Prometheus for monitoring.
disablePostgresUtil boolean false
If set to true, avoids creating the postgres-util sidecar. This sidecar contains usual Postgres administration utilities that are not present in the main (patroni) container, like psql. Only disable if you know what you are doing.
metadata object
Pod custom metadata information.

Sidecar containers

A sidecar container is a container that adds functionality to PostgreSQL or to the cluster infrastructure. Currently StackGres implement following sidecar containers:

  • envoy: this container is always present, and is not possible to disable it. It serve as a edge proxy from client to PostgreSQL instances or between PostgreSQL instances. It enables network metrics collection to provide connection statistics.
  • pgbouncer: a container with pgbouncer as the connection pooling for the PostgreSQL instances.
  • prometheus-postgres-exporter: a container with postgres exporter that exports metrics for the PostgreSQL instances.
  • fluent-bit: a container with fluent-bit that send logs to a distributed logs cluster.
  • postgres-util: a container with psql and all PostgreSQL common tools in order to connect to the database directly as root to perform any administration tasks.

The following example, disable all optional sidecars:

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  pods:
    disableConnectionPooling: false
    disableMetricsExporter: false
    disablePostgresUtil: false

Persistent Volume

Holds the configurations of the persistent volume that the cluster pods are going to use.

Property Required Updatable Type Default Description
size string
Size of the PersistentVolume set for each instance of the cluster. This size is specified either in Mebibytes, Gibibytes or Tebibytes (multiples of 2^20, 2^30 or 2^40, respectively).
storageClass string default storage class
Name of an existing StorageClass in the Kubernetes cluster, used to create the PersistentVolumes for the instances of the cluster.
apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  pods:
    persistentVolume:
      size: '5Gi'
      storageClass: default

Metadata

Holds custom metadata information for StackGres pods to have.

Property Required Updatable Type Default Description
labels string default storage class
Additional labels for StackGres Pods.
apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  pods:
    metadata:
      annotations:
        customAnnotations: customAnnotationValue
      labels:
        customLabel: customLabelValue

Configurations

Custom configurations to be applied to the cluster.

Property Required Updatable Type Default Description
sgPostgresConfig string will be generated
Name of the SGPostgresConfig used for the cluster. It must exist. When not set, a default Postgres config, for the major version selected, is used.
sgPoolingConfig string will be generated

Name of the SGPoolingConfig used for this cluster. Each pod contains a sidecar with a connection pooler (currently: PgBouncer). The connection pooler is implemented as a sidecar.

If not set, a default configuration will be used. Disabling connection pooling altogether is possible if the disableConnectionPooling property of the pods object is set to true.

sgBackupConfig string
Name of the SGBackupConfig to use for the cluster. It defines the backups policy, storage and retention, among others, applied to the cluster. When not set, a default backup config is used.

Example:


apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  configurations:
    sgPostgresConfig: 'postgresconf'
    sgPoolingConfig: 'pgbouncerconf'
    sgBackupConfig: 'backupconf'

Initial Data Configuration

Specifies the cluster initialization data configurations

Property Required Updatable Type Default Description
restore object
scripts object
A list of SQL scripts executed in sequence, exactly once, when the database is bootstrap and/or after restore is completed.

Restore configuration

By default, stackgres it’s creates as an empty database. To create a cluster with data from an existent backup, we have the restore options. It works, by simply indicating the backup CR UUI that we want to restore.

Property Required Updatable Type Default Description
fromBackup string
When set to the UID of an existing SGBackup, the cluster is initialized by restoring the backup data to it. If not set, the cluster is initialized empty. The selected backup must be in the same namespace.
downloadDiskConcurrency integer 1
The backup fetch process may fetch several streams in parallel. Parallel fetching is enabled when set to a value larger than one.

Example:

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  initialData: 
    restore:
      fromBackup: d7e660a9-377c-11ea-b04b-0242ac110004
      downloadDiskConcurrency: 1

Scripts configuration

By default, stackgres creates as an empty database. To execute some scripts, we have the scripts options where you can specify a script or reference a key in a ConfigMap or a Secret that contains the script to execute.

Property Required Updatable Type Default Description
name string
Name of the script. Must be unique across this SGCluster.
database string postgres
Database where the script is executed. Defaults to the postgres database, if not specified.
script string
Raw SQL script to execute. This field is mutually exclusive with scriptFrom field.
scriptFrom object

Reference to either a Kubernetes Secret or a ConfigMap that contains the SQL script to execute. This field is mutually exclusive with script field.

Fields secretKeyRef and configMapKeyRef are mutually exclusive, and one of them is required.

Example:

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  initialData: 
    scripts:
    - name: create-stackgres-user
      scriptFrom:
        secretKeyRef: # read the user from a Secret to maintain credentials in a safe place
          name: stackgres-secret-sqls-scripts
          key: create-stackgres-user.sql
    - name: create-stackgres-database
      script: |
        CREATE DATABASE stackgres WITH OWNER stackgres;
    - name: create-stackgres-schema
      database: stackgres
      scriptFrom:
        configMapKeyRef: # read long script from a ConfigMap to avoid have to much data in the helm releasea and the sgcluster CR
          name: stackgres-sqls-scripts
          key: create-stackgres-schema.sql

Script from

Property Required Updatable Type Default Description
configMapKeyRef object
A ConfigMap reference that contains the SQL script to execute. This field is mutually exclusive with secretKeyRef field.
secretKeyRef object
A Kubernetes SecretKeySelector that contains the SQL script to execute. This field is mutually exclusive with configMapKeyRef field.

Script from ConfigMap

Property Required Updatable Type Default Description
name string
The name of the ConfigMap that contains the SQL script to execute.
key string
The key name within the ConfigMap that contains the SQL script to execute.

Script from Secret

Property Required Updatable Type Default Description
name string
Name of the referent. More information.
key string
The key of the secret to select from. Must be a valid secret key.

Distributed logs

Specifies the distributed logs cluster to send logs to:

Property Required Updatable Type Default Description
sgDistributedLogs string
Name of the SGDistributedLogs to use for this cluster. It must exist.

Example:

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  distributedLogs: 
    sgDistributedLogs: distributedlogs

Postgres Services

Specifies the service configuration for the cluster:

Property Required Updatable Type Default Description
Primary object
Configuration for the -primary service. It provides a stable connection (regardless of primary failures or switchovers) to the read-write Postgres server of the cluster.
Replicas object
Configuration for the -replicas service. It provides a stable connection (regardless of replica node failures) to any read-only Postgres server of the cluster. Read-only servers are load-balanced via this service.

service type

Property Required Updatable Type Default Description
Type string ClusterIP
Specifies the type of Kubernetes service.

Postgres Services

Specifies the service configuration for the cluster:

Property Required Updatable Type Default Description
primary object
Configuration for the -primary service. It provides a stable connection (regardless of primary failures or switchovers) to the read-write Postgres server of the cluster.
replicas object
Configuration for the -replicas service. It provides a stable connection (regardless of replica node failures) to any read-only Postgres server of the cluster. Read-only servers are load-balanced via this service.

service type

Property Required Updatable Type Default Description
type string ClusterIP
Specifies the type of Kubernetes service.

Postgres Services

Specifies the service configuration for the cluster:

Property Required Updatable Type Default Description
Primary object
Configuration for the -primary service. It provides a stable connection (regardless of primary failures or switchovers) to the read-write Postgres server of the cluster.
Replicas object
Configuration for the -replicas service. It provides a stable connection (regardless of replica node failures) to any read-only Postgres server of the cluster. Read-only servers are load-balanced via this service.

service type

Property Required Updatable Type Default Description
Type string ClusterIP
Specifies the type of Kubernetes service.

Non Production options

The following options should NOT be enabled in a production environment.

Property Required Updatable Type Default Description
disableClusterPodAntiAffinity boolean false

It is a best practice, on non-containerized environments, when running production workloads, to run each database server on a different server (virtual or physical), i.e., not to co-locate more than one database server per host.

The same best practice applies to databases on containers. By default, StackGres will not allow to run more than one StackGres pod on a given Kubernetes node. Set this property to true to allow more than one StackGres pod per node.