SQL Database - PostgreSQL

Notes
Provisioning of PostgreSQL Instance will have a waiting time of 20 minutes.
Once PostgreSQL Instance is provisioned, you are not supposed to rename the instance: rds-postgresql/database-name: <your_database_instance_name>
Use SSL/TLS option in your application to connect to PostgreSQL Instance.

Background

Tenant with a requirement to provision Relational Database can consider Managed Database offerings from AWS RDS (Relational Database Service).
Supported PostgreSQL engine: spec.parameters.engineVersion and instance type: spec.parameters.dbInstanceClass

Requirements

Give a unique name to your PostgreSQL database instance to avoid failure in provisioning. Use annotation or refer to section Example section: rds-postgresql/database-name: <your_database_instance_name>
Create a DB password in the Sealed Secret format.
- The DB password can include any printable ASCII character except /, ", or @
Input only spec.parameters fields as shown in the Example section.
The result will be stored in status fields.
Plan the storage to be allocated accordingly for the database spec.parameters.allocatedStorage, as any modification may impact some downtime.
Give at least 20 GB for spec.parameters.allocatedStorage.
The spec.parameters.masterUsername is a mandatory field. It should consist of 1 to 16 characters, which can be letters, numbers, or underscores. The first character must be a letter.
By default, the DB instance will be an empty machine. Hence, use spec.parameters.dbName to seed the default database during the initial creation step. It should contain 1 to 63 characters, which can be letters, numbers, or underscores. The first character must be a letter.
By default, the database that is provisioned in the PRD environment will have multiAZ turned on to make sure the resources are highly available.
Available spec.parameters.engineVersion are “14”, “15”.
Refer to the Service Endpoint in the Validation section on your DB server URL.
For spec.parameters.deletionPolicy refer to the Deletion Policy.

Warnings
To comply with Cloud Security Policy, do not use default pgsql user ‘postgres’ for spec.parameters.masterUsername.
The spec.parameters.dbName can be initialised only when you create the database instance for the first time. UPDATE operation is not supported.
When updating DB parameters, the changes take effect immediately. To prevent service disruption, tenants should schedule the modifications within the maintenance window by setting the maintenanceWindow parameter. Refer to Version or Upgrade.
If you are performing a major version upgrade, set spec.parameters.allowMajorVersionUpgrade: true.
If you are updating RDS Secret Object to change password. Upon update, you have to delete the Service endpoint. The naming convention can be found in the Validation section. Once deleted, the endpoint will be recreated and the new password will be reflected in the database. Service endpoint convention and DBPasswordUpdated status can be found here.
The default maintenance window for the version upgrade is Sunday 00:00-01:00 SGT.

Example

apiVersion: crd.aws.container.stack.gov.sg/v1alpha1
kind: CSPostgreSQLInstance
metadata:
  name: cstack-demo-postgresql
  namespace: <your_namespace>
  annotations:
    rds-postgresql/database-name: cstack-demo-postgresql
spec:
  parameters: #refer to Requirements section to know more about the field constraints
    masterUsername: changeme #do not use 'postgres'
    dbInstanceClass: db.t3.micro
    dbName: changeme #to seed database (optional)
    engine: postgres
    engineVersion: "14" #refer above for available versions
    secretName: cstack-demo-secret
    allocatedStorage: 20
    autoMinorVersionUpgrade: true #refer to above for Default maintenance Window
    deletionPolicy: Orphan # Recommended to set 'Orphan' in Stg/Prod for deletion protection
---
apiVersion: bitnami.com/v1alpha1
kind: SealedSecret
metadata:
  name: cstack-demo-secret
  namespace: <your_namespace>
spec:
  encryptedData:
    password: <your_encrypted_rds_password> #refer to above for field constraints
  template:
    metadata:
      name: cstack-demo-secret
      namespace: <your_namespace>
    type: Opaque

Version or Upgrade

This section highlights the different manifests used for deploying specific database version or doing upgrading.

For best practices on upgrading postgresql, refer to the steps in AWS documentation.
Always perform the upgrade first in non-prod environment to understand the behavior of each upgrade process. RDS upgrade or error logs will be available. Refer to the Validation section.
For backup and restore procedures, check the Administration section.
When upscaling the DB instance type and storage, remember to set the maintenanceWindow parameter to prevent any service disruptions.

# Upgrade to next Major version
# Read this process: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process
apiVersion: crd.aws.container.stack.gov.sg/v1alpha1
kind: CSPostgreSQLInstance
metadata:
  name: cstack-demo-postgresql-major
  namespace: <your_namespace>
  annotations:
    rds-postgresql/database-name: cstack-demo-postgresql-major
spec:
  parameters: #refer to above for field constraints
    masterUsername: changeme #do not use 'postgres'
    dbInstanceClass: db.t3.micro
    dbName: changeme #to seed database (optional)
    engine: postgres
    engineVersion: "15" #14 -> 15
    maintenanceWindow: sun:18:00-sun:19:00 # mon:02:00-mon:03:00 SGT
    secretName: cstack-demo-secret
    allocatedStorage: 20
    allowMajorVersionUpgrade: true #refer to above for Default maintenance Window
    deletionPolicy: Delete # Recommended to set 'Orphan' in Prod

Validation

Check the CSPostgreSQLInstance manifest status. When all the status types are True, the Database resource is ready to be consumed through Kubernetes Service endpoint.
Service endpoint: {cspostgresqlinstance-metadata-name}-service.{namespace}.svc.cluster.local:5432
Check the Service manifest using command kubectl get services -n <your_namespace> <resource_name>-service -o yaml
Check the Status using command kubectl get cspostgresqlinstances -n <your_namespace> <resource_name> -o yaml
Visualizing metrics and logs can be done by setting them up in Grafana dashboard. Refer to this reference guide.

# Sample of successful status
status:
  awsConditions:
  - lastTransitionTime: "2021-10-07T06:13:45Z"
    reason: Available
    status: "True"
    type: Ready
  - lastTransitionTime: "2021-10-07T06:31:40Z"
    reason: ReconcileSuccess
    status: "True"
    type: Synced
  conditions:
  - lastTransitionTime: "2021-10-07T06:28:50Z"
    reason: Available
    status: "True"
    type: Ready
  - lastTransitionTime: "2021-10-07T06:31:17Z"
    reason: Successfully create DB Service endpoint
    status: "True"
    type: SvcEndpointCreated
  - lastTransitionTime: "2021-10-07T06:31:18Z"
    reason: Successfully updated Database Password
    status: "True"
    type: DBPasswordUpdated
  parameters:
    dbInstanceStatus: available

# Sample of status with error (downgrading version is not allowed)
status:
  awsConditions:
  - lastTransitionTime: "2021-09-09T16:23:44Z"
    reason: Available
    status: "True"
    type: Ready
  - lastTransitionTime: "2021-10-10T02:14:52Z"
    message: 'update failed: cannot modify RDS instance: InvalidParameterCombination:
      Cannot upgrade postgres from 15.3 to 14.8'
    reason: ReconcileError
    status: "False"
    type: Synced

Administration

Refer to CStack’s reference repository on how to provision tools that help on Database Administration tasks such as Initialization and Debugging.
A backup job will be enabled on a daily basis using AWS Backup Managed Service. To access backup and restore functions, you can setup this application in your project namespace from the following repository: backup-restore.

Additional Resources

AWS Relational Database Service User Guide

Deletion Policy

This policy applies to all Custom Resource manifests.

By default, spec.parameters.deletionPolicy is set to Orphan during creation of Cloud Resources. Orphan implies the actual Cloud Resources will be left running even you have removed CSBucket from cstack-manifest repository
Before deleting the Cloud Resources, ensure that spec.parameters.deletionPolicy is set to Delete. This setting implies that the actual Cloud Resources will be removed when deleting the CSBucket manifest. It’s important to verify that this parameter has been updated and that CSBucket shows a status type of True. Once confirmed, it is safe to remove the CR manifest from the cstack-manifest repository after the designated grace period.
It is recommended to always set spec.parameters.deletionPolicy to Orphan for Production workload.

Service Account

Block storage