Deploy StackGres

Use the stackgres-operator and stackgres-cluster packages to run PostgreSQL under the StackGres operator.

This guide creates one PostgreSQL 17 instance with a 5 GiB volume. The target cluster must provide a default StorageClass, or the database instance must name one explicitly.

Add the StackGres operator

Install one operator instance in its own namespace:

how-to/package-stacks/stackgres-cluster.nix

      instances.stackgres-system.stackgres-operator = {
        package = packages.stackgres-operator;
      };

View source on GitHub ↗

The operator installs and reconciles the StackGres custom resource types and its admission webhooks when it starts. One operator manages database clusters in every namespace, and a cluster runs only one. The package does not run the StackGres web console, REST API, or OpenTelemetry collector.

Add the PostgreSQL cluster

Add the database instance in the namespace where it should run:

how-to/package-stacks/stackgres-cluster.nix

      instances.database.postgres = {
        package = packages.stackgres-cluster;
        config = {
          instances = 1;
          postgres.version = "17";
          persistentVolume.size = "5Gi";
          instanceProfile = {
            cpu = "500m";
            memory = "1Gi";
          };
        };
      };

View source on GitHub ↗

The stackgres-cluster package resolves the operator through its stackgresOperator dependency. Kix also connects it to the StorageClass provided by the cluster flavor.

postgres.version takes a major, such as "17", or a minor, such as "17.11". Kix checks it against the versions the operator’s StackGres release ships, and refuses "latest", because a new operator release would then move the database to a new major.

For a production cluster, increase persistentVolume.size and size instanceProfile for the workload. StackGres takes CPU as whole cores or millicores ("2", "1500m") and sizes in Mi, Gi, or (for the volume) Ti, and Kix refuses other forms. instances defaults from the cluster’s availability level, and above one instance the pods spread across nodes and zones. On a multi-node cluster StackGres also places every database pod of a namespace on its own node, so the clusters in one namespace need as many nodes as they have instances together. Set persistentVolume.storageClass when the flavor’s default is not suitable. PostgreSQL parameters go in postgresqlConf, and any other SGCluster field in extraSpec.

Apps should not read the superuser Secret. Give each app a stackgres-database instance, which creates a role and a database on the cluster and puts the credentials in the app’s namespace. Its out follows the Postgres database contract, kix.contracts.postgresDatabase, which the packages that take a database dependency check at evaluation.

Back up to object storage

Backups go to an SGObjectStorage in the cluster’s namespace. Reference the Secret that holds the storage credentials, add a stackgres-object-storage instance, and turn on backups:

instances.database = {
  backup-credentials = {
    package = packages.secret-ref;
    aliases = [ "storageCredentials" ];
    config.keys = [ "accessKeyId" "secretAccessKey" ];
  };

  backups = {
    package = packages.stackgres-object-storage;
    config = {
      type = "s3Compatible";
      bucket = "pg-backups";
      extraSpec.s3Compatible = {
        endpoint = "https://s3.example.com";
        region = "example-1";
        enablePathStyleAddressing = true;
      };
    };
  };

  postgres.config.backups = [
    {
      cronSchedule = "0 1 * * *";
      retention = 7;
    }
  ];
};

Kix refuses backups when no stackgres-object-storage instance is in the same namespace, and refuses a credentials Secret that does not declare the keys the storage type reads. credentialKeys maps those keys to other names. With backups on, the package sets archive_timeout to 60 seconds, so a quiet database still archives its write-ahead log every minute.

The network policy treats an endpoint written in extraSpec as an address outside the cluster. For an S3 store that runs in the cluster, wire the instance whose root is its S3 Service instead, and leave endpoint unset:

backups.deps.endpointService = ref.minio.minio;

The package then sets the endpoint to that Service’s URL and turns on path-style addressing, and the PostgreSQL pods get network policy to the store.

Monitor PostgreSQL

When the cluster has a Prometheus provider, such as prometheus-operator, the package adds a PodMonitor for the exporter sidecar StackGres runs beside PostgreSQL. Set metrics.interval to change the scrape interval, or metrics.enabled = false to leave the PodMonitor out.

Check the generated resources

Evaluate the cluster before deploying it:

kix-examples/

❱ kix check how-to-package-stackgres
 TOOL       RESULT  DETAILS                      
 eval       pass    41 manifests evaluated       
 scorecard  pass    0 errors, 1 warnings, 0 info

Inspect the custom resources that configure PostgreSQL:

kix-examples/ output excerpt

❱ kix build how-to-package-stackgres --output json
[
  {
    "apiVersion": "stackgres.io/v1",
    "kind": "SGCluster",
    "metadata": {
      "name": "postgres",
      "namespace": "database"
    },
    "spec": {
      "instances": 1,
      "postgres": {
        "version": "17"
      },
      "sgInstanceProfile": "postgres-profile",
      "configurations": {
        "sgPostgresConfig": "postgres-pg17-config"
      },
      "persistentVolume": {
        "size": "5Gi",
        "storageClass": "standard"
      }
    }
  },
  {
    "apiVersion": "stackgres.io/v1",
    "kind": "SGInstanceProfile",
    "metadata": {
      "name": "postgres-profile",
      "namespace": "database"
    },
    "spec": {
      "cpu": "500m",
      "memory": "1Gi"
    }
  }
]

The SGCluster refers to the generated instance profile and PostgreSQL configuration by name. StackGres uses those resources to create and operate the StatefulSet, Services, and persistent volume claims.

Deploy and connect

Deploy the operator and database, then check their status:

kix-examples/

❱ kix deploy how-to-package-stackgres

❱ kix status how-to-package-stackgres

❱ kubectl get sgcluster -n database postgres

The StackGres operator creates a Service and a Secret named postgres in the database namespace. Forward the primary Service port:

❱ kubectl port-forward -n database service/postgres 5432:5432

In another terminal, read the generated superuser password and connect:

❱ export PGPASSWORD=$(kubectl get secret -n database postgres -o jsonpath='{.data.superuser-password}' | base64 --decode)

❱ psql --host 127.0.0.1 --username postgres --dbname postgres

Stop the port-forward with Ctrl-C. Clear the shell variable when you finish:

❱ unset PGPASSWORD

If the SGCluster does not become ready, inspect it with kubectl describe, then check the StackGres operator logs and PVC state in the database namespace.