Creating a PostgreSQL cluster

If you have a project, you can create a PostgreSQL cluster in it.

Using the CLIUsing the CLI

1. If the project does not exist yet, create it: kubectl create namespace <project name>.

2. If the database needs a backup, get a storage ready depending on the S3 type:

   External S3

   Stackland Storage (internal Stackland object storage)

   Create a secret with credentials for accessing an S3-compatible storage:

   1. Create a file, e.g., touch s3-credentials.yaml.

   2. Paste a configuration and substitute your accessKey and secretKey:

      apiVersion: v1
      kind: Secret
      metadata:
        name: access-key-credentials
      type: Opaque
      stringData:
        accessKey: "<access_key_id>"
        secretKey: "<secret_access_key>"
      
      

   3. Apply the manifest: kubectl apply -f s3-credentials.yaml -n <project_name>.

   In the cluster manifest, specify spec.backup.storage.type: s3 and a link to this secret in spec.backup.storage.s3.credentialsSecretRef.name, e.g., access-key-credentials.

   You need not create anything. In the cluster manifest, specify spec.backup.storage.type: stackland-storage; the operator will create a bucket and access key automatically. If required, you can refer to the existing resources via spec.backup.storage.stacklandStorage.bucketRef and accessKeyRef.

3. Create the PostgresqlCluster resource file, e.g., using the touch postgresqlcluster.yaml command.

4. Open the file and paste the configuration below into it:

   Minimum configuration

   Maximum configuration (backups to an external S3)

   Maximum configuration (backups to Stackland Storage)

   apiVersion: postgresql.stackland.yandex.cloud/v1alpha1
   kind: PostgresqlCluster
   metadata:
     name: cluster
     annotations:
       pgcl.io/description: "Example of a minimum PostgreSQL cluster"
   spec:
     instances: 1
     deletionProtection: false # `true` prohibits deleting the cluster until the protection is explicitly deactivated.
     storage:
       size: 2Gi
       readOnlyTriggerPercent: 90 # disk usage percentage to switch to read-only mode (the default value is 90)
     version: "17"
     enableSuperuserAccess: true # this field value must be "true" when creating a cluster; you can change it after creating a cluster
     resources:
       requests: # resource requests
         cpu: "500m"
         memory: "1Gi"
       limits: # resource limits
         cpu: "1"
         memory: "2Gi"
     postgresConfiguration: # postgres parameters
       logLevel: info
       parameters:
         max_connections: "100"
         shared_buffers: "128MB"
         work_mem: "16MB"
     poolers: # read and write operations poolers
       resources:
         requests:
           cpu: "0.1"
           memory: "64Mi"
         limits:
           cpu: "0.2"
           memory: "128Mi"
       rw: # write pooler
         port: 6432 # write port
         instances: 1 # number of instances
         type: ClusterIP # cluster type
         odyssey:
           poolMode: session # pool operation mode (session or transaction)
       ro:
         port: 6433
         instances: 1
         type: ClusterIP
         odyssey:
           poolMode: session
       r:
         port: 6434
         instances: 1
         type: ClusterIP
         odyssey:
           poolMode: session
     backup:
       storage:
         type: stackland-storage
   

   User data will be stored in a secret named <cluster name>-superuser.

   Use it if you had created a secret with external S3 credentials at the previous step.

   Note

   To create a custom password for the superuser, create a secret with this password and specify the username as postgres.

   apiVersion: v1
   kind: Secret
   metadata:
     name: secret
   type: kubernetes.io/basic-auth
   stringData:
     username: postgres
     password: $2b$12$4T***** # database access password
   ---
   apiVersion: postgresql.stackland.yandex.cloud/v1alpha1
   kind: PostgresqlCluster
   metadata:
     name: cluster
     annotations:
       pgcl.io/description: "Full example of a PostgreSQL cluster"
   spec:
     instances: 1
     deletionProtection: false # `true` enables cluster protection against accidental deletion
     storage:
       size: 2Gi
   #    storageClass: "your-storage-class"
       autoScaling:
         enabled: false # enabling autoscaling
         maxSize: 300Gi # maximum storage size
         standardIncreasePercent: 20 # storage expansion percentage
         resizeTriggerPercent: 80 # usage percentage threshold that triggers storage expansion
       readOnlyTriggerPercent: 90 # disk usage percentage to switch to read-only mode (the default value is 90)
     version: "17"
     enableSuperuserAccess: true # this field value must be "true" when creating a cluster; you can change it after creating a cluster
     superuserSecretRef:
       name: secret
     resources:
       requests: # resource requests
         cpu: "500m"
         memory: "1Gi"
       limits: # resource limits
         cpu: "1"
         memory: "2Gi"
     postgresConfiguration: # postgres parameters
       logLevel: info
       parameters:
         max_connections: "100"
         shared_buffers: "128MB"
         work_mem: "16MB"
     poolers: # read and write operations poolers
       resources:
         requests:
           cpu: "0.1"
           memory: "64Mi"
         limits:
           cpu: "0.2"
           memory: "128Mi"
       rw: # write pooler
         port: 6432 # write port
         instances: 1 # number of instances
         type: ClusterIP # cluster type
         odyssey:
           poolMode: session # pool operation mode (session or transaction)
       ro:
         port: 6433
         instances: 1
         type: ClusterIP
         odyssey:
           poolMode: session
       r:
         port: 6434
         instances: 1
         type: ClusterIP
         odyssey:
           poolMode: session
     backup:
       storage:
         type: s3
         s3:
           prefix: s3://bucket # bucket for backups
           region: ru-central1
           endpointUrl: https://storage.yandexcloud.net # endpoint for bucket access
           forcePathStyle: false
           storageClass: STANDARD
           credentialsSecretRef:
             name: access-key-credentials
             accessKeyIdPath: accessKey
             secretAccessKeyPath: secretKey
       schedule: "0 0 2 * * *" # running a scheduled backup (https://pkg.go.dev/github.com/robfig/cron#hdr-CRON_Expression_Format)
   

   Substitute your backup schedule into spec.backup.schedule. Format: CRON Expression Format. In spec.backup.storage.s3, specify endpointUrl, prefix, region, and the name of the secret in credentialsSecretRef.name. Optionally, you can remove schedule to skip creating a schedule.

   This option is for backups to an internal object storage in Stackland Storage. Specify spec.backup.storage.type: stackland-storage in the manifest; the operator will create a bucket and access key automatically.

   Note

   To create a custom password for the superuser, create a secret with this password and specify the username as postgres.

   apiVersion: v1
   kind: Secret
   metadata:
     name: secret
   type: kubernetes.io/basic-auth
   stringData:
     username: postgres
     password: $2b$12$4T***** # database access password
   ---
   apiVersion: postgresql.stackland.yandex.cloud/v1alpha1
   kind: PostgresqlCluster
   metadata:
     name: cluster
     annotations:
       pgcl.io/description: "Full example of a PostgreSQL cluster"
   spec:
     instances: 1
     deletionProtection: false # `true` enables cluster protection against accidental deletion
     storage:
       size: 2Gi
   #    storageClass: "your-storage-class"
       autoScaling:
         enabled: false # enabling autoscaling
         maxSize: 300Gi # maximum storage size
         standardIncreasePercent: 20 # storage expansion percentage
         resizeTriggerPercent: 80 # usage percentage threshold that triggers storage expansion
       readOnlyTriggerPercent: 90 # disk usage percentage to switch to read-only mode (the default value is 90)
     version: "17"
     enableSuperuserAccess: true # this field value must be "true" when creating a cluster; you can change it after creating a cluster
     superuserSecretRef:
       name: secret
     resources:
       requests: # resource requests
         cpu: "500m"
         memory: "1Gi"
       limits: # resource limits
         cpu: "1"
         memory: "2Gi"
     postgresConfiguration: # postgres parameters
       logLevel: info
       parameters:
         max_connections: "100"
         shared_buffers: "128MB"
         work_mem: "16MB"
     poolers: # read and write operations poolers
       resources:
         requests:
           cpu: "0.1"
           memory: "64Mi"
         limits:
           cpu: "0.2"
           memory: "128Mi"
       rw: # write pooler
         port: 6432 # write port
         instances: 1 # number of instances
         type: ClusterIP # cluster type
         odyssey:
           poolMode: session # pool operation mode (session or transaction)
       ro:
         port: 6433
         instances: 1
         type: ClusterIP
         odyssey:
           poolMode: session
       r:
         port: 6434
         instances: 1
         type: ClusterIP
         odyssey:
           poolMode: session
     backup:
       storage:
         type: stackland-storage
       schedule: "0 0 2 * * *" # running a scheduled backup (https://pkg.go.dev/github.com/robfig/cron#hdr-CRON_Expression_Format)
   

   Substitute your backup schedule into spec.backup.schedule. Format: CRON Expression Format.

5. Apply the manifest: kubectl apply -f postgresqlcluster.yaml -n <project name>. Optionally, you can specify the project name in the metadata.namespace resource property and skip it in the command.

Deletion protectionDeletion protection

To prohibit accidental cluster deletion, enable deletion protection by setting spec.deletionProtection: true in the manifest. As long as the protection is on, the deletion of the PostgresqlCluster resource via kubectl delete or the management console will be rejected. To delete the cluster, first deactivate the protection by setting spec.deletionProtection: false and applying the manifest. Then you can delete the cluster.

In the management console, the Deletion protection toggle is available when you create or edit a cluster.

Using the management consoleUsing the management console

1. If you have not opened a project yet, select one.

2. In the left menu, select PostgreSQL Clusters.

3. Click Create.

4. Fill out the fields as follows:

   • Cluster name: Only use lowercase letters, numbers, and hyphens.
   • Number of instances: Number of cluster replicas.
   • PostgreSQL version: Select from the list of available versions.
   • Computing resources, where Limits is the upper limit and Requests is the lower limit.
   • Storage, where Disk type is the storage class (stackland-nvme, stackland-ssd, stackland-hdd, stackland-other). Learn more about storage classes in Disk subsystem.
   • Database: Section containing authentication credentials.
   • Connection pooler: Handles writes and reads; used for all data-modifying operations and critical transactions.
   • Backups: Settings for creating the database backup in an S3 bucket.
   • Deletion protection: Toggle. You cannot delete the cluster via the API or console until the protection is disabled.

5. Click Create.

This is it: the cluster has appeared in the PostgreSQL Clusters list. To copy the connection link:

1. Select the cluster from the list.

2. Click Connect and copy the link.

   Note

   The link is generated in following format: jdbc:postgresql://<cluster name>.<project name>.svc.<cluster domain>:6432/<datatbase name>?user=postgres&password=<password>&ssl=true&sslmode=require. Use the database password for connecting.

   For your first connection, you can use postgres both as the database name and superuser name.