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 to use for the new cluster. It can be either of:

  • The string ‘latest’, which will automatically select the latest major.minor Postgres version.
  • A major version, like ‘12’ or ‘11’, which will select that major version and the latest minor version.
  • A specific major.minor version, like ‘12.2’.
instances integer
Number of StackGres instances to be created. Each instance will contain one Postgres server. Out of all of the Postgres servers, one will be elected as the master, the rest will remain as read-only replicas.
sgInstanceProfile string will be generated
Name of the SGInstanceProfile to use. A SGInstanceProfile defines CPU and memory limits. Must exist before creating the cluster. If you don’t select a profile, a default (currently: 1 core, 2 GiB RAM) one will be used.
pods object
Cluster pod’s configuration
configurations object
Custom configuration to be applied to the cluster.
prometheusAutobind boolean false
If enabled, a ServiceMonitor will be 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. You may also specify scripts to run on the database after cluster creation.
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/v1beta1
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
Set to false to avoid creating a connection pooling (using PgBouncer) sidecar.
disableMetricsExporter boolean false
Set to false to avoid creating the Prometheus exporter sidecar. Recommended if you don’t intent to use Prometheus for monitoring.
disablePostgresUtil boolean false
Set to false to avoid 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/v1beta1
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 to be created for each instance of the cluster. Size must be 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 K8s cluster, that will be used to create the PersistentVolumes for the instances of this cluster.
apiVersion: stackgres.io/v1beta1
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 to have
apiVersion: stackgres.io/v1beta1
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 to use for this cluster. It must exist. If not set, a default Postgres config, for the major version selected, will be used.
sgPoolingConfig string will be generated

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

If not selected, a default configuration will be used. Note that you may disable connection pooling altogether by setting to false the disableConnectionPooling property of the pods object.

sgBackupConfig string
Name of the SGBackupConfig to use for this cluster. This will define the backups policy, storage and retention, among others, to apply to this cluster. If not set, a default backup config will be used.

Example:


apiVersion: stackgres.io/v1beta1
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 that will be 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
Initialize the cluster data by restoring an existing backup. If not used, cluster will be initialized empty. Set to the UID of an existing SGBackup from which to restore the data. The backup needs to be in the same namespace.
downloadDiskConcurrency integer 1
The backup fetch process may fetch several streams in parallel. Specify a value larger than one for parallel fetching.

Example:

apiVersion: stackgres.io/v1beta1
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
The name of the script. Must be unique across this SGCluster.
database string postgres
The database where the script will be executed. Defaults to the postgres database, if not specified.
script string
The 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/v1beta1
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/v1beta1
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 K8s node. Set this property to true to allow more than one StackGres pod per node.