SGCluster

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
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 primary, the rest remain as read-only replicas.
postgres ✓ object
This section allow to configure Postgres features
replication ✓ object

This section allow to configure Postgres replication mode and HA roles groups.

The main replication group is implicit and contains the total number of instances less the sum of all instances in other replication groups.

The total number of instances is always specified by .spec.instances.
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.
metadata ✓ object
Metadata information from cluster created resources.
postgresServices ✓ object
Kubernetes services created or managed by StackGres.
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.
nonProductionOptions ✓ array

Example:

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

Postgres

Property Required Updatable Type Default Description
version ✓ ✓ 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’.
flavor string

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

If not specified then the vanilla Postgres will be used for the cluster.
extensions ✓ array

StackGres support deploy of extensions at runtime by simply adding an entry to this array. A deployed extension still requires the creation in a database using the CREATE EXTENSION statement. After an extension is deployed correctly it will be present until removed and the cluster restarted.

A cluster restart is required for:

  • Extensions that requires to add an entry to shared_preload_libraries configuration parameter.
  • Upgrading extensions that overwrite any file that is not the extension'’s control file or extension'’s script file.
  • Removing extensions. Until the cluster is not restarted a removed extension will still be available.
  • Install of extensions that require extra mount. After installed the cluster will require to be restarted.
ssl ✓ object
This section allow to use SSL when connecting to Postgres

Postgres extensions

Extensions to be installed in the cluster.

Property Required Updatable Type Default Description
name ✓ ✓ string
The name of the extension to deploy.
version ✓ string stable
The version of the extension to deploy. If not specified version of stable channel will be used by default.
publisher ✓ string com.ongres
The id of the publisher of the extension to deploy. If not specified com.ongres will be used by default.
repository ✓ string
The repository base URL from where to obtain the extension to deploy. If not specified https://stackgres.io/downloads/postgres/extensions will be used by default (or the value specified during operator deployment).

Example:


apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  postgres:
    extensions:
      - {name: 'timescaledb', version: '2.3.1'}

Postgres SSL

By default, support for SSL connections to Postgres is disabled, to enable it configure this section. SSL connections will be handled by Envoy using Postgres filter’s SSL termination.

Property Required Updatable Type Default Description
enabled string false

Allow to enable SSL for connections to Postgres. By default is false.

If true fields certificateSecretKeySelector and privateKeySecretKeySelector will be required.
certificateSecretKeySelector object
Secret key selector for the certificate or certificate chain used for SSL connections.
privateKeySecretKeySelector object
Secret key selector for the private key used for SSL connections.

Example:

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  postgres:
    ssl:
      enabled: true
      certificateSecretKeySelector:
        name: stackgres-secrets
        key: cert
      secretKeyRef:
        name: stackgres-secrets
        key: key

SSL Certificate Secret

Property Required Updatable Type Default Description
name ✓ ✓ string
The name of Secret that contains the certificate or certificate chain for SSL connections
key ✓ ✓ string
The key of Secret that contains the certificate or certificate chain for SSL connections

SSL Private Key Secret

Property Required Updatable Type Default Description
name ✓ ✓ string
The name of Secret that contains the private key for SSL connections
key ✓ ✓ string
The key of Secret that contains the private key for SSL connections

Replication

Property Required Updatable Type Default Description
mode ✓ string

The replication mode applied to the whole cluster. Possible values are:

  • async (default)
  • sync
  • strict-sync

async Mode

When in asynchronous mode the cluster is allowed to lose some committed transactions. When the primary server fails or becomes unavailable for any other reason a sufficiently healthy standby will automatically be promoted to primary. Any transactions that have not been replicated to that standby remain in a “forked timeline” on the primary, and are effectively unrecoverable (the data is still there, but recovering it requires a manual recovery effort by data recovery specialists).

sync Mode

When in synchronous mode a standby will not be promoted unless it is certain that the standby contains all transactions that may have returned a successful commit status to client (clients can change the behavior per transaction using PostgreSQL’s synchronous_commit setting. Transactions with synchronous_commit values of off and local may be lost on fail over, but will not be blocked by replication delays). This means that the system may be unavailable for writes even though some servers are available. System administrators can still use manual failover commands to promote a standby even if it results in transaction loss.

Synchronous mode does not guarantee multi node durability of commits under all circumstances. When no suitable standby is available, primary server will still accept writes, but does not guarantee their replication. When the primary fails in this mode no standby will be promoted. When the host that used to be the primary comes back it will get promoted automatically, unless system administrator performed a manual failover. This behavior makes synchronous mode usable with 2 node clusters.

When synchronous mode is used and a standby crashes, commits will block until the primary is switched to standalone mode. Manually shutting down or restarting a standby will not cause a commit service interruption. Standby will signal the primary to release itself from synchronous standby duties before PostgreSQL shutdown is initiated.

strict-sync Mode

When it is absolutely necessary to guarantee that each write is stored durably on at least two nodes, use the strict synchronous mode. This mode prevents synchronous replication to be switched off on the primary when no synchronous standby candidates are available. As a downside, the primary will not be available for writes (unless the Postgres transaction explicitly turns off synchronous_mode parameter), blocking all client write requests until at least one synchronous replica comes up.

Note: Because of the way synchronous replication is implemented in PostgreSQL it is still possible to lose transactions even when using strict synchronous mode. If the PostgreSQL backend is cancelled while waiting to acknowledge replication (as a result of packet cancellation due to client timeout or backend failure) transaction changes become visible for other backends. Such changes are not yet replicated and may be lost in case of standby promotion.
role ✓ string

This role is applied to the instances of the implicit replication group that is composed by .spec.instances number of instances. Possible values are:

  • ha-read (default)
  • ha The primary instance will be elected among all the replication groups that are either ha or ha-read. Only if the role is set to ha-read instances of main replication group will be exposed via the replicas service.
syncInstances ✓ integer
Number of synchronous standby instances. Must be less than the total number of instances. It is set to 1 by default. Only setteable if mode is sync or strict-sync.
groups ✓ array
StackGres support replication groups where a replication group of a specified number of instances could have different replication role. The main replication group is implicit and contains the total number of instances less the sum of all instances in other replication groups.

Replication group

Property Required Updatable Type Default Description
name ✓ string
The name of the replication group. If not set will default to the group-<index>.
role ✓ string

This role is applied to the instances of this replication group. Possible values are:

  • ha-read
  • ha
  • readonly
  • none The primary instance will be elected among all the replication groups that are either ha or ha-read. Only if the role is set to readonly or ha-read instances of such replication group will be exposed via the replicas service.
instances ✓ integer

Number of StackGres instances for this replication group.

The total number of instance of a cluster is always .spec.instances. The sum of the instances in the replication group must be less than the total number of instances.

Metadata

Holds custom metadata information for StackGres generated resources to have.

Property Required Updatable Type Default Description
annotations ✓ object
Custom Kubernetes annotations to be passed to resources created and managed by StackGres.
labels ✓ object
Custom Kubernetes labels to be passed to resources created and managed by StackGres.

Annotations

Holds custom annotations for StackGres generated resources to have.

Property Required Updatable Type Default Description
allResources ✓ object
Annotations to attach to any resource created or managed by StackGres.
clusterPods ✓ object
Annotations to attach to pods created or managed by StackGres.
services ✓ object
Annotations to attach to all services created or managed by StackGres.
primaryService ✓ object
Custom Kubernetes annotations passed to the -primary service.
replicasService ✓ object
Custom Kubernetes annotations passed to the -replicas service.
 
apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  metadata:
    annotations:
      clusterPods:
        customAnnotations: customAnnotationValue
      primaryService:
        customAnnotations: customAnnotationValue
      replicasService:
        customAnnotations: customAnnotationValue

Labels

Holds custom labels for StackGres generated resources to have.

Property Required Updatable Type Default Description
clusterPods ✓ object
Labels to attach to pods created or managed by StackGres.
 
apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  metadata:
    labels:
      clusterPods:
        customLabel: customLabelValue

Postgres Services

Specifies the service configuration for the cluster:

Property Required Updatable Type Default Description
Primary ✓ object primary
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 replicas
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.

Primary service type

Property Required Updatable Type Default Description
enabled ✓ boolean true
Specify if the -primary service should be created or not.
type ✓ string ClusterIP
Specifies the type of Kubernetes service.
externalIPs ✓ array
Specify custom external IPs for Postgres primary service

Replicas service type

Property Required Updatable Type Default Description
enabled ✓ boolean true
Specify if the -replicas service should be created or not.
type ✓ string ClusterIP
Specifies the type of Kubernetes service.
externalIPs ✓ array
Specify the custom external IPs for Postgres replicas service

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.
scheduling ✓ object
Pod custom scheduling configuration.

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

Scheduling

Holds scheduling configuration for StackGres pods to have.

Property Required Updatable Type Default Description
nodeSelector ✓ object
Pod custom node selector.
nodeAffinity ✓ object
Node affinity is a group of node affinity scheduling rules.
tolerations ✓ array
Pod custom node tolerations

Node Affinity

Sets the pod’s affinity to restrict it to run only on a certain set of node(s)

Property Required Updatable Type Default Description
requiredDuringSchedulingIgnoredDuringExecution ✓ object
If the affinity requirements specified by this field are not met at scheduling time, the pod will not be scheduled onto the node. This type of affinity can restrict to only run the pod on specifically nodes like “Intel CPUs”.
preferredDuringSchedulingIgnoredDuringExecution ✓ array
The scheduler will prefer to schedule pods to nodes that satisfy the affinity expressions specified by this field, but it may choose a node that violates one or more of the expressions. The node that is most preferred is the one with the greatest sum of weights, i.e. for each node that meets all of the scheduling requirements (resource request, requiredDuringScheduling affinity expressions, etc.), compute a sum by iterating through the elements of this field and adding “weight” to the sum if the node matches the corresponding matchExpressions; the node(s) with the highest sum are the most preferred.

See Kubernetes pod node affinity definition for more details.

Tolerations

Holds scheduling configuration for StackGres pods to have.

Property Required Updatable Type Default Description
key ✓ string
Key is the taint key that the toleration applies to. Empty means match all taint keys. If the key is empty, operator must be Exists; this combination means to match all values and all keys.
operator ✓ string Equal
Operator represents a key’s relationship to the value. Valid operators are Exists and Equal. Defaults to Equal. Exists is equivalent to wildcard for value, so that a pod can tolerate all taints of a particular category.
value ✓ string
Value is the taint value the toleration matches to. If the operator is Exists, the value should be empty, otherwise just a regular string.
effect ✓ string match all taint effects
Effect indicates the taint effect to match. Empty means match all taint effects. When specified, allowed values are NoSchedule, PreferNoSchedule and NoExecute.
tolerationSeconds ✓ string 0
TolerationSeconds represents the period of time the toleration (which must be of effect NoExecute, otherwise this field is ignored) tolerates the taint. By default, it is not set, which means tolerate the taint forever (do not evict). Zero and negative values will be treated as 0 (evict immediately) by the system.

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, backup configuration will not be 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 ✓ object
From which backup to restore and how the process is configured
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.

If not specified it will be interpreted as latest.

From backup configuration

Property Required Updatable Type Default Description
name ✓ string
When set to the name 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.
pointInTimeRecovery object

It is possible to restore the database to its state at any time since your backup was taken using Point-in-Time Recovery (PITR) as long as another backup newer than the PITR requested restoration date does not exists.

Point In Time Recovery (PITR). PITR allow to restore the database state to an arbitrary point of time in the past, as long as you specify a backup older than the PITR requested restoration date and does not exists a backup newer than the same restoration date.

See also: https://www.postgresql.org/docs/current/continuous-archiving.html

PITR configuration

Property Required Updatable Type Default Description
restoreToTimestamp string
An ISO 8601 date, that holds UTC date indicating at which point-in-time the database have to be restored.

Example:

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: stackgres
spec:
  initialData:
    restore:
      fromBackup:
        uid: 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

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.
enabledFeatureGates ✓ boolean false

A list of StackGres feature gates to enable (not suitable for a production environment).

Available feature gates are:

  • babelfish-flavor: Allow to use babelfish flavor.