Using a9s PostgreSQL

This chapter describes how to interact with a9s PostgreSQL using the Kubernetes API.

Pre-requisite

• kubectl

• Data Service Custom Resources

  Before using the a9s PostgreSQL service, ensure the necessary custom resources are present in your Kubernetes cluster. Use the following commands to verify their existence:

  kubectl get postgresqlinstances.anynines.com
  
  kubectl get servicebindings.anynines.com
  
  kubectl get backups.anynines.com
  
  kubectl get restores.anynines.com

  If you encounter the following error:

  error: the server doesn't have a resource type "<resource-name>"

  This indicates that the corresponding resource is missing. To resolve this, you can follow the instructions here or reach out to a platform operator for assistance.

Create a PostgreSQL Service Instance

To provision a PostgreSQL Service Instance, simply create a PostgreSQL Kubernetes Object by applying a Service Instance Claim.

Replace the placeholder values denoted by < > in the provided yaml file. Kindly select one of the supported values for spec.service and spec.plan. The supported values for service and plan can be found here.

Optional: To set up a clustered PostgreSQL with small specifications, apply the yaml manifest provided in the Example tab below. Feel free to modify the fields in order to create a different type of PostgreSQL database tailored to your requirements.

  apiVersion: anynines.com/v1
  kind: PostgresqlInstance
  metadata:
    name: <name>
    namespace: <namespace>
  spec:
    service: <service-name>
    plan: <plan-name>
    compositionRef:
      name: a9s-postgresql

Template

  apiVersion: anynines.com/v1
  kind: PostgresqlInstance
  metadata:
    name: example-a9s-postgresql
    namespace: default
  spec:
    service: a9s-postgresql15
    plan: postgresql-replica-small
    compositionRef:
      name: a9s-postgresql

Download the yaml manifest example

Applying this Kubernetes yaml manifest is as straightforward as working with any default Kubernetes resource.

kubectl apply -f <filename.yaml>

After applying the manifest, please allow some time for the remote resources to be deployed. If you've deployed the provided example, you should observe an output similar to the following:

kubectl get postgresqlinstances.anynines.com

NAME                          SYNCED   READY   CONNECTION-SECRET   AGE
example-a9s-postgresql        True     True                        9m

You can also obtain additional information about the status of PostgreSQL Objects using the following command:

kubectl describe postgresqlinstances.anynines.com

Name:         example-a9s-postgresql
Namespace:    default
...
Status:
  Conditions:
    Last Transition Time:  2023-08-07T09:57:49Z
    Reason:                ReconcileSuccess
    Status:                True
    Type:                  Synced
    Last Transition Time:  2023-08-07T10:04:11Z
    Reason:                Available
    Status:                True
    Type:                  Ready
  Managed:
    Conditions:
      Last Transition Time:  2023-08-07T09:57:50Z
      Reason:                ReconcileSuccess
      Status:                True
      Type:                  Synced
      Last Transition Time:  2023-08-07T10:04:00Z
      Reason:                Available
      Status:                True
      Type:                  Ready
    Created At:              2023-08-07T09:57:50.556Z
    Provisioned At:          2023-08-07T10:03:40.445Z
    State:                   provisioned
    Updated At:              2023-08-07T10:03:40.500Z

info

No postgreSQL pod is running locally in the App Cluster.

Behind the scenes the Custom Resources synced up to the a9s Data Service platform which ensures the database is provisioned and healthy. The status information reflecting the database status synced back to the App Cluster to signify service readiness.

Configuring Extensions in a PostgreSQL Database (Optional)

You can configure a PostgreSQL database with extensions as needed. See the full list of supported PostgreSQL extensions here.

The following example demonstrates how to enable them:

  apiVersion: anynines.com/v1
  kind: PostgresqlInstance
  metadata:
    name: <name>
    namespace: <namespace>
  spec:
    service: <service-name>
    plan: <plan-name>
    parameters:
      <extension-key1>: <value>
      <extension-key2>: <value>
      <extension-key3>: <value>
    compositionRef:
      name: a9s-postgresql

Template

  apiVersion: anynines.com/v1
  kind: PostgresqlInstance
  metadata:
    name: example-postgresql-instance
    namespace: default
  spec:
    service: a9s-postgresql15
    plan: postgresql-replica-small
    parameters:
      maxConnections: 200
      tempFileLimit: 4096
      trackIoTiming: "on"
      archiveTimeout: 10
      statementTimeout: 3000
      walWriterDelay: 400
      logErrorVerbosity: VERBOSE
    compositionRef:
      name: a9s-postgresql

Download the yaml manifest example

Then, apply this yaml file using the same method described here.

Bind an Application to a PostgreSQL Database

The ServiceBinding Custom Resource is all that you need to quickly start using the database. To target a specific PostgreSQL instance, set the spec.instanceRef field.

Optional: If you've followed the example from the previous step, where we create a PostgreSQL Kubernetes Object, you can now proceed to apply the yaml manifest provided in the Example tab below. This will bind the previously created PostgreSQL instance.

  apiVersion: anynines.com/v1
  kind: ServiceBinding
  metadata:
    name: <name>
    namespace: <namespace>
  spec:
    instanceRef: <postgresql-instance-name>
    serviceInstanceType: postgresql
    compositionRef:
      name: a9s-servicebinding

Template

  apiVersion: anynines.com/v1
  kind: ServiceBinding
  metadata:
    name: example-a9s-postgresql
    namespace: default
  spec:
    instanceRef: example-a9s-postgresql
    serviceInstanceType: postgresql
    compositionRef:
      name: a9s-servicebinding

Download the yaml manifest example

After a few seconds, the ServiceBinding will be ready. You can then access the credentials and network details required for database connectivity by describing the Secret that was automatically created. The Data Service automation generates a Secret, named {service-binding-name}-creds, in the same namespace as the ServiceBinding.

For instance, if you have already applied the provided examples, upon the successful deployment of the ServiceBinding, you can access credentials and network details by describing the corresponding Kubernetes Secret using the following command:

kubectl get secret example-a9s-postgresql-creds -o yaml

apiVersion: v1
data:
  host: <base64 encoded value>
  hosts: <base64 encoded value>
  name: <base64 encoded value>
  password: <base64 encoded value>
  port: <base64 encoded value>
  uri: <base64 encoded value>
  username: <base64 encoded value>
...

Backup a PostgreSQL Database

The a9s platform provides an easy way to create backups and restore if needed. You can use the Kubernetes Custom Resource Backup from the API group anynines.com to create backups of Data Service Instances. To do this, simply target the specific Data Service Instance you want to back up, as shown in the following yaml manifest:

  apiVersion: anynines.com/v1
  kind: Backup
  metadata:
    name: <name>
    namespace: <namespace>
  spec:
    instanceRef: <postgresql-instance-name>
    serviceInstanceType: postgresql
    compositionRef:
      name: a9s-backup

Template

  apiVersion: anynines.com/v1
  kind: Backup
  metadata:
    name: example-a9s-postgresql
    namespace: default
  spec:
    instanceRef: example-a9s-postgresql
    serviceInstanceType: postgresql
    compositionRef:
      name: a9s-backup

Download the yaml manifest example

And then apply the yaml manifest:

kubectl apply -f <filename.yaml>

You can inspect the status of the backup to determine when it is complete, similar to how we did it for the PostgreSQL instance, using the following command:

kubectl get backup.anynines.com -o yaml

You can observe the status transition from queued to done as the backup process completes.

apiVersion: anynines.com/v1
kind: Backup
...
status:
    ...
  managed:
    ...
    status: queued

apiVersion: anynines.com/v1
kind: Backup
...
status:
    ...
  managed:
    ...
    size: 235184
    status: done

Restore a PostgreSQL Database Backup

To restore a backup, you must apply a Restore from the API group anynines.com, targeting an existing PostgreSQL Backup. Below is the yaml file you can utilize for this purpose:

  apiVersion: anynines.com/v1
  kind: Restore
  metadata:
    name: <name>
  spec:
      backupRef: <backup-name>
      serviceInstanceType: postgresql
      compositionRef:
        name: a9s-restore

Template

  apiVersion: anynines.com/v1
  kind: Restore
  metadata:
    name: example-a9s-postgresql
  spec:
      backupRef: example-a9s-postgresql
      serviceInstanceType: postgresql
      compositionRef:
        name: a9s-restore

Download the yaml manifest example

And then apply the yaml manifest:

kubectl apply -f <filename.yaml>

You can observe the state transition from queued to running and done as the restore completes.

kubectl get restore.anynines.com -o yaml

apiVersion: v1
items:
- apiVersion: anynines.com/v1
  kind: Restore
  ...
  status:
      ...
    managed:
      ...
      state: queued

apiVersion: v1
items:
- apiVersion: anynines.com/v1
  kind: Restore
  ...
  status:
      ...
    managed:
      ...
      state: done

Delete a9s Kubernetes Custom Resources

Delete the Custom Resources using the kubectl delete command. Replace <resource-type> with the actual resource type and <resource-name> with the name of the Custom Resource you want to delete.

kubectl delete <resource-type> <resource-name>

Alternatively, you can use the following command to delete Custom Resources by specifying the filename of the yaml manifest:

kubectl delete -f <filename.yaml>

Coming soon

a9s PostgreSQL offers a range of features that will soon be supported for a9s PostgreSQL through Kubernetes integration. See the following table for a summary of these upcoming features.

Feature	Description
Metrics	Monitor, collect, and manage metrics for comprehensive insights into the service.
Logging	Stream log data to a third-party service for centralized log management and analysis.
Disk Usage Alerts	Create disk usage alerts to be notified when ephemeral or persistent disk usage exceeds a certain threshold.
Migration and Backup	Migrate data to another PostgreSQL instance using forks, dumps, or continuous archiving and point-in-time recovery.