Creating a PostgreSQL Cluster with Kubernetes CRDs

The PostgreSQL Operator provides users with a few different methods to perform PostgreSQL cluster operations, via:

• a REST API
• pgo, PostgreSQL Operator Command Line Interface (CLI)
• Directly interfacing with Kubernetes, including various APIs and custom resource definitions (CRDs).

While the REST API and pgo provide many conveniences, there are use cases that require the third method, i.e. interacting directly with Kubernetes to create the desired PostgreSQL cluster setup.

To demonstrate this, let's look at how we can manipulate the CRD responsible for managing PostgreSQL clusters to create a new cluster managed by the PostgreSQL Operator.

Background

The PostgreSQL Operator defines the following series of Kubernetes Custom Resource Definitions (CRDs). Each of these CRDs perform specific PostgreSQL management operations that are common for managing the full lifecycle of a database cluster, such as provisioning, scaling, taking backups, etc.

The CRDs contain metadata about each PostgreSQL cluster that is managed by the Operator and act as the "source of truth" for Kubernetes in terms of the overall state of the components that make up the clusters.

The PostgreSQL Operator defines the following CRDs:

• Cluster (pgclusters) defines the key attributes need to run PostgreSQL cluster, such as CPU/RAM/disk size, and lets Kubernetes know which clusters should exist.
• Tasks (pgtask) tracks administrative tasks and allows a user of the Operator to track the state of certain operations.
• Replica (pgreplica) stores the definition for replicas of a particular PostgreSQL cluster.
• Policies (pgpolicies) is used for custom policy files (e.g. for RLS, or any type of custom SQL file).
• Backup (pgbackups) tracks the status of a backup as well as its progress.

Creating a Cluster Directly with a CRD

To create a cluster directly with the pgcluster CRD, you first need to install the PostgreSQL Operator in your Kubernetes environment. There are several ways to install the PostgreSQL Operator, including by following our HOWTO on installing the PostgreSQL Operator in GKE. This article is using version 4.0.1.

The PostgreSQL Operator repository provides an example for how to create a cluster by directly interfacing with a CRD. We will be mostly following this example, but detail what each step entails.

In order to successfully create a PostgreSQL cluster using the Kubernetes CRD, we will need to do the following:

1. Define several Kubernetes secrets that will store passwords needed for PostgreSQL
2. Generate SSH keys that will be used by the backup management tool pgBackRest, which is used by the cluster
3. Edit the configuration file for the new PostgreSQL cluster that will be applied to the pgcluster CRD
4. Add all this information to Kubernetes!

Without further ado, let's begin!

Step 0: The Operator Environment

There are a few assumptions made about the PostgreSQL Operator operating environment (no pun intended) when following this example:

• We are using version 4.0.1 of the PostgreSQL Operator
• We are using a single namespace called pgo; this is great for a development environment as you only need a single namespace for everything
• We will call our cluster crdcluster

Step 1: Create PostgreSQL Passwords

PostgreSQL passwords are not stored in the pgcluster CRD, but rather in Kubernetes secrets and are subsequently loaded into the deployed pods. Each secret is base64 encoded. For example, if you want to base64 encode the string password, you can do the following from the command line:

echo -n "password" | base64

which will output the value cGFzc3dvcmQ=.

The PostgreSQL Operator requires three user account & password combinations to be made for a cluster:

• postgres - this is the PostgreSQL superuser for the cluster.
• primaryuser - this is meant to be for the primary user for the cluster
• user - a third user account that can connect to the cluster. In this example, we will call this user myuser.

On the machine you are running kubectl from, create three files called postgres-secret.yml, primaryuser-secret.yml, and myuser-secret.yml. Set each user to have the password password (in a production environment, you would set up different passwords for each).

For this example, the postgres-secret.yml file should look like this:

apiVersion: v1
data:
  password: cGFzc3dvcmQ=
  username: cG9zdGdyZXM=
kind: Secret
metadata:
  labels:
    pg-cluster: crdcluster
  name: crdcluster-postgres-secret
  namespace: pgo
type: Opaque

The primaryuser-secret.yml file should look like this:

apiVersion: v1
data:
  password: cGFzc3dvcmQ=
  username: cHJpbWFyeXVzZXI=
kind: Secret
metadata:
  labels:
    pg-cluster: crdcluster
  name: crdcluster-primaryuser-secret
  namespace: pgo
type: Opaque

and for myuser-secret.yml create a file that looks like this:

apiVersion: v1
data:
  password: cGFzc3dvcmQ=
  username: bXl1c2Vy
kind: Secret
metadata:
  labels:
    pg-cluster: crdcluster
  name: crdcluster-myuser-secret
  namespace: pgo
type: Opaque

Step 2: Generate SSH Keys for pgBackRest

The integration with pgBackRest relies upon having the appropriate sshd configuration as well as a public/private SSH keypair stored in a Kubernetes secret. We can use the default configuration files that come with the PostgreSQL Operator, but we will need to generate a unique SSH keypair and encode it in base64.

Generate a SSH keypair with the following command:

ssh-keygen -f crdcluster-key - N ''

Get the base64 encoded values for each key, which you can do with the following commands:

base64 -i crdcluster-key
base64 -i crdcluster-key.pub

Create a secret file called backrest-repo-config.yml using the example below. Copy and paste the base64 encoded values in crdcluster-key and crdcluster-key.pub in their respective places (the crdcluster-key base64 value goes in the id_rsa and ssh_host_rsa_key attributes, and the crdcluster-key.pub value goes in the authorized_keys attribute).

apiVersion: v1
data:
  authorized_keys: <crdcluster-key.pub>
  id_rsa: <crdcluster-key>
  ssh_host_rsa_key: <crdcluster-key>
  config: SG9zdCAqCglTdHJpY3RIb3N0S2V5Q2hlY2tpbmcgbm8KCUlkZW50aXR5RmlsZSAvdG1wL2lkX3JzYQoJUG9ydCAyMDIyCglVc2VyIHBnYmFja3Jlc3QK
  sshd_config: IwkkT3BlbkJTRDogc3NoZF9jb25maWcsdiAxLjEwMCAyMDE2LzA4LzE1IDEyOjMyOjA0IG5hZGR5IEV4cCAkCgojIFRoaXMgaXMgdGhlIHNzaGQgc2VydmVyIHN5c3RlbS13aWRlIGNvbmZpZ3VyYXRpb24gZmlsZS4gIFNlZQojIHNzaGRfY29uZmlnKDUpIGZvciBtb3JlIGluZm9ybWF0aW9uLgoKIyBUaGlzIHNzaGQgd2FzIGNvbXBpbGVkIHdpdGggUEFUSD0vdXNyL2xvY2FsL2JpbjovdXNyL2JpbgoKIyBUaGUgc3RyYXRlZ3kgdXNlZCBmb3Igb3B0aW9ucyBpbiB0aGUgZGVmYXVsdCBzc2hkX2NvbmZpZyBzaGlwcGVkIHdpdGgKIyBPcGVuU1NIIGlzIHRvIHNwZWNpZnkgb3B0aW9ucyB3aXRoIHRoZWlyIGRlZmF1bHQgdmFsdWUgd2hlcmUKIyBwb3NzaWJsZSwgYnV0IGxlYXZlIHRoZW0gY29tbWVudGVkLiAgVW5jb21tZW50ZWQgb3B0aW9ucyBvdmVycmlkZSB0aGUKIyBkZWZhdWx0IHZhbHVlLgoKIyBJZiB5b3Ugd2FudCB0byBjaGFuZ2UgdGhlIHBvcnQgb24gYSBTRUxpbnV4IHN5c3RlbSwgeW91IGhhdmUgdG8gdGVsbAojIFNFTGludXggYWJvdXQgdGhpcyBjaGFuZ2UuCiMgc2VtYW5hZ2UgcG9ydCAtYSAtdCBzc2hfcG9ydF90IC1wIHRjcCAjUE9SVE5VTUJFUgojClBvcnQgMjAyMgojQWRkcmVzc0ZhbWlseSBhbnkKI0xpc3RlbkFkZHJlc3MgMC4wLjAuMAojTGlzdGVuQWRkcmVzcyA6OgoKSG9zdEtleSAvc3NoZC9zc2hfaG9zdF9yc2Ffa2V5CiNIb3N0S2V5IC9zc2hkL3NzaF9ob3N0X2VjZHNhX2tleQojSG9zdEtleSAvc3NoZC9zc2hfaG9zdF9lZDI1NTE5X2tleQoKIyBDaXBoZXJzIGFuZCBrZXlpbmcKI1Jla2V5TGltaXQgZGVmYXVsdCBub25lCgojIExvZ2dpbmcKI1N5c2xvZ0ZhY2lsaXR5IEFVVEgKU3lzbG9nRmFjaWxpdHkgQVVUSFBSSVYKI0xvZ0xldmVsIElORk8KCiMgQXV0aGVudGljYXRpb246CgojTG9naW5HcmFjZVRpbWUgMm0KUGVybWl0Um9vdExvZ2luIG5vClN0cmljdE1vZGVzIG5vCiNNYXhBdXRoVHJpZXMgNgojTWF4U2Vzc2lvbnMgMTAKClB1YmtleUF1dGhlbnRpY2F0aW9uIHllcwoKIyBUaGUgZGVmYXVsdCBpcyB0byBjaGVjayBib3RoIC5zc2gvYXV0aG9yaXplZF9rZXlzIGFuZCAuc3NoL2F1dGhvcml6ZWRfa2V5czIKIyBidXQgdGhpcyBpcyBvdmVycmlkZGVuIHNvIGluc3RhbGxhdGlvbnMgd2lsbCBvbmx5IGNoZWNrIC5zc2gvYXV0aG9yaXplZF9rZXlzCiNBdXRob3JpemVkS2V5c0ZpbGUJL3BnY29uZi9hdXRob3JpemVkX2tleXMKQXV0aG9yaXplZEtleXNGaWxlCS9zc2hkL2F1dGhvcml6ZWRfa2V5cwoKI0F1dGhvcml6ZWRQcmluY2lwYWxzRmlsZSBub25lCgojQXV0aG9yaXplZEtleXNDb21tYW5kIG5vbmUKI0F1dGhvcml6ZWRLZXlzQ29tbWFuZFVzZXIgbm9ib2R5CgojIEZvciB0aGlzIHRvIHdvcmsgeW91IHdpbGwgYWxzbyBuZWVkIGhvc3Qga2V5cyBpbiAvZXRjL3NzaC9zc2hfa25vd25faG9zdHMKI0hvc3RiYXNlZEF1dGhlbnRpY2F0aW9uIG5vCiMgQ2hhbmdlIHRvIHllcyBpZiB5b3UgZG9uJ3QgdHJ1c3Qgfi8uc3NoL2tub3duX2hvc3RzIGZvcgojIEhvc3RiYXNlZEF1dGhlbnRpY2F0aW9uCiNJZ25vcmVVc2VyS25vd25Ib3N0cyBubwojIERvbid0IHJlYWQgdGhlIHVzZXIncyB+Ly5yaG9zdHMgYW5kIH4vLnNob3N0cyBmaWxlcwojSWdub3JlUmhvc3RzIHllcwoKIyBUbyBkaXNhYmxlIHR1bm5lbGVkIGNsZWFyIHRleHQgcGFzc3dvcmRzLCBjaGFuZ2UgdG8gbm8gaGVyZSEKI1Bhc3N3b3JkQXV0aGVudGljYXRpb24geWVzCiNQZXJtaXRFbXB0eVBhc3N3b3JkcyBubwpQYXNzd29yZEF1dGhlbnRpY2F0aW9uIG5vCgojIENoYW5nZSB0byBubyB0byBkaXNhYmxlIHMva2V5IHBhc3N3b3JkcwpDaGFsbGVuZ2VSZXNwb25zZUF1dGhlbnRpY2F0aW9uIHllcwojQ2hhbGxlbmdlUmVzcG9uc2VBdXRoZW50aWNhdGlvbiBubwoKIyBLZXJiZXJvcyBvcHRpb25zCiNLZXJiZXJvc0F1dGhlbnRpY2F0aW9uIG5vCiNLZXJiZXJvc09yTG9jYWxQYXNzd2QgeWVzCiNLZXJiZXJvc1RpY2tldENsZWFudXAgeWVzCiNLZXJiZXJvc0dldEFGU1Rva2VuIG5vCiNLZXJiZXJvc1VzZUt1c2Vyb2sgeWVzCgojIEdTU0FQSSBvcHRpb25zCiNHU1NBUElBdXRoZW50aWNhdGlvbiB5ZXMKI0dTU0FQSUNsZWFudXBDcmVkZW50aWFscyBubwojR1NTQVBJU3RyaWN0QWNjZXB0b3JDaGVjayB5ZXMKI0dTU0FQSUtleUV4Y2hhbmdlIG5vCiNHU1NBUElFbmFibGVrNXVzZXJzIG5vCgojIFNldCB0aGlzIHRvICd5ZXMnIHRvIGVuYWJsZSBQQU0gYXV0aGVudGljYXRpb24sIGFjY291bnQgcHJvY2Vzc2luZywKIyBhbmQgc2Vzc2lvbiBwcm9jZXNzaW5nLiBJZiB0aGlzIGlzIGVuYWJsZWQsIFBBTSBhdXRoZW50aWNhdGlvbiB3aWxsCiMgYmUgYWxsb3dlZCB0aHJvdWdoIHRoZSBDaGFsbGVuZ2VSZXNwb25zZUF1dGhlbnRpY2F0aW9uIGFuZAojIFBhc3N3b3JkQXV0aGVudGljYXRpb24uICBEZXBlbmRpbmcgb24geW91ciBQQU0gY29uZmlndXJhdGlvbiwKIyBQQU0gYXV0aGVudGljYXRpb24gdmlhIENoYWxsZW5nZVJlc3BvbnNlQXV0aGVudGljYXRpb24gbWF5IGJ5cGFzcwojIHRoZSBzZXR0aW5nIG9mICJQZXJtaXRSb290TG9naW4gd2l0aG91dC1wYXNzd29yZCIuCiMgSWYgeW91IGp1c3Qgd2FudCB0aGUgUEFNIGFjY291bnQgYW5kIHNlc3Npb24gY2hlY2tzIHRvIHJ1biB3aXRob3V0CiMgUEFNIGF1dGhlbnRpY2F0aW9uLCB0aGVuIGVuYWJsZSB0aGlzIGJ1dCBzZXQgUGFzc3dvcmRBdXRoZW50aWNhdGlvbgojIGFuZCBDaGFsbGVuZ2VSZXNwb25zZUF1dGhlbnRpY2F0aW9uIHRvICdubycuCiMgV0FSTklORzogJ1VzZVBBTSBubycgaXMgbm90IHN1cHBvcnRlZCBpbiBSZWQgSGF0IEVudGVycHJpc2UgTGludXggYW5kIG1heSBjYXVzZSBzZXZlcmFsCiMgcHJvYmxlbXMuClVzZVBBTSBubyAKCiNBbGxvd0FnZW50Rm9yd2FyZGluZyB5ZXMKI0FsbG93VGNwRm9yd2FyZGluZyB5ZXMKI0dhdGV3YXlQb3J0cyBubwpYMTFGb3J3YXJkaW5nIHllcwojWDExRGlzcGxheU9mZnNldCAxMAojWDExVXNlTG9jYWxob3N0IHllcwojUGVybWl0VFRZIHllcwojUHJpbnRNb3RkIHllcwojUHJpbnRMYXN0TG9nIHllcwojVENQS2VlcEFsaXZlIHllcwojVXNlTG9naW4gbm8KVXNlUHJpdmlsZWdlU2VwYXJhdGlvbiBubwojUGVybWl0VXNlckVudmlyb25tZW50IG5vCiNDb21wcmVzc2lvbiBkZWxheWVkCiNDbGllbnRBbGl2ZUludGVydmFsIDAKI0NsaWVudEFsaXZlQ291bnRNYXggMwojU2hvd1BhdGNoTGV2ZWwgbm8KI1VzZUROUyB5ZXMKI1BpZEZpbGUgL3Zhci9ydW4vc3NoZC5waWQKI01heFN0YXJ0dXBzIDEwOjMwOjEwMAojUGVybWl0VHVubmVsIG5vCiNDaHJvb3REaXJlY3Rvcnkgbm9uZQojVmVyc2lvbkFkZGVuZHVtIG5vbmUKCiMgbm8gZGVmYXVsdCBiYW5uZXIgcGF0aAojQmFubmVyIG5vbmUKCiMgQWNjZXB0IGxvY2FsZS1yZWxhdGVkIGVudmlyb25tZW50IHZhcmlhYmxlcwpBY2NlcHRFbnYgTEFORyBMQ19DVFlQRSBMQ19OVU1FUklDIExDX1RJTUUgTENfQ09MTEFURSBMQ19NT05FVEFSWSBMQ19NRVNTQUdFUwpBY2NlcHRFbnYgTENfUEFQRVIgTENfTkFNRSBMQ19BRERSRVNTIExDX1RFTEVQSE9ORSBMQ19NRUFTVVJFTUVOVApBY2NlcHRFbnYgTENfSURFTlRJRklDQVRJT04gTENfQUxMIExBTkdVQUdFCkFjY2VwdEVudiBYTU9ESUZJRVJTCgojIG92ZXJyaWRlIGRlZmF1bHQgb2Ygbm8gc3Vic3lzdGVtcwpTdWJzeXN0ZW0Jc2Z0cAkvdXNyL2xpYmV4ZWMvb3BlbnNzaC9zZnRwLXNlcnZlcgoKIyBFeGFtcGxlIG9mIG92ZXJyaWRpbmcgc2V0dGluZ3Mgb24gYSBwZXItdXNlciBiYXNpcwojTWF0Y2ggVXNlciBhbm9uY3ZzCiMJWDExRm9yd2FyZGluZyBubwojCUFsbG93VGNwRm9yd2FyZGluZyBubwojCVBlcm1pdFRUWSBubwojCUZvcmNlQ29tbWFuZCBjdnMgc2VydmVyCg==
kind: Secret
metadata:
  labels:
    pg-cluster: crdcluster
    pgo-backrest-repo: 'true'
  name: crdcluster-backrest-repo-config
  namespace: pgo
type: Opaque

Step 3: Create the CRD File

As part of the Operator pattern, the PostgreSQL Operator looks for an entry to be added to the pgcluster CRD. In order to do this, we first need to create a CRD file.

Below is the CRD file, called crdcluster.json, we will use to create the crdcluster. It includes references to the various secrets we created in the previous steps:

{
	"apiVersion": "crunchydata.com/v1",
	"kind": "Pgcluster",
	"metadata": {
		"labels": {
			"archive": "false",
			"archive-timeout": "60",
			"crunchy-pgbadger": "false",
			"crunchy_collect": "false",
			"current-primary": "crdcluster",
			"deployment-name": "crdcluster",
			"name": "crdcluster",
			"pg-cluster": "crdcluster",
			"pgo-backrest": "true",
			"pgo-version": "4.0.1",
			"primary": "true"
		},
		"name": "crdcluster",
		"namespace": "pgo"
	},
	"spec": {
		"ArchiveStorage": {
			"accessmode": "",
			"fsgroup": "26",
			"matchLabels": "",
			"name": "",
			"size": "",
			"storageclass": "standard",
			"storagetype": "dynamic",
			"supplementalgroups": ""
		},
		"BackrestStorage": {
			"accessmode": "ReadWriteOnce",
			"fsgroup": "26",
			"matchLabels": "",
			"name": "",
			"size": "1G",
			"storageclass": "standard",
			"storagetype": "dynamic",
			"supplementalgroups": ""
		},
		"ContainerResources": {
			"limitscpu": "",
			"limitsmemory": "",
			"requestscpu": "",
			"requestsmemory": ""
		},
		"PrimaryStorage": {
			"accessmode": "ReadWriteOnce",
			"fsgroup": "26",
			"matchLabels": "",
			"name": "crdcluster",
			"size": "1G",
			"storageclass": "standard",
			"storagetype": "dynamic",
			"supplementalgroups": ""
		},
		"ReplicaStorage": {
			"accessmode": "ReadWriteOnce",
			"fsgroup": "26",
			"matchLabels": "",
			"name": "",
			"size": "1G",
			"storageclass": "standard",
			"storagetype": "dynamic",
			"supplementalgroups": ""
		},
		"backuppath": "",
		"backuppvcname": "",
		"ccpimage": "crunchy-postgres",
		"ccpimagetag": "centos7-11.4-2.4.1",
		"clustername": "crdcluster",
		"customconfig": "",
		"database": "mydb",
		"name": "crdcluster",
		"namespace": "pgo",
		"nodename": "",
		"policies": "",
		"port": "5432",
		"primaryhost": "crdcluster",
		"primarysecretname": "crdcluster-primaryuser-secret",
		"replicas": "0",
		"rootsecretname": "crdcluster-postgres-secret",
		"secretfrom": "",
		"status": "",
		"strategy": "1",
		"user": "myuser",
		"userlabels": {
			"archive": "false",
			"archive-timeout": "60",
			"crunchy-pgbadger": "false",
			"crunchy_collect": "false",
			"pgo-backrest": "true",
			"pgo-version": "4.0.1"
		},
		"usersecretname": "crdcluster-myuser-secret"
	}
}

Step 4: Create the PostgreSQL Cluster

Now we can create the PostgreSQL cluster! Simply run the following commands:

# create the required postgres credentials for the crdcluster cluster
kubectl -n pgo create -f postgres-secret.yml
kubectl -n pgo create -f primaryuser-secret.yml
kubectl -n pgo create -f myuser-secret.yml
kubectl -n pgo create -f backrest-repo-config.yml
# create the pgcluster CRD for the crdcluster cluster
kubectl -n pgo create -f crdcluster.json

Success, we've created a PostgreSQL cluster in Kubernetes! But how do we know that it was successfully created, and is being managed by the PostgreSQL Operator?

Step 5: Validating the Creation of the PostgreSQL Cluster

There are a few PostgreSQL Operator commands we can use to validate the creation of the cluster. First, to see if the PostgreSQL Operator is aware of crdcluster, you can use pgo show cluster like so:

pgo show cluster -n pgo crdcluster

This command should successfully return and provide information about the current state of the cluster.

To see if the crdcluster is up, running, and available, you can use the pgo test command:

pgo test -n pgo crdcluster

If the cluster is up and running, you should see output similar to the following:

cluster : crdcluster
	psql -p 5432 -h 10.0.4.193 -U myuser
 postgres is Working
	psql -p 5432 -h 10.0.4.193 -U myuser
 mydb is Working
	psql -p 5432 -h 10.0.4.193 -U postgres
 postgres is Working
	psql -p 5432 -h 10.0.4.193 -U postgres
 mydb is Working
	psql -p 5432 -h 10.0.4.193 -U primaryuser
 postgres is Working
	psql -p 5432 -h 10.0.4.193 -U primaryuser
 mydb is Working

If you want to inspect the user credentials that we defined in Step 1 were created and available to the cluster, you can do so with the pgo show user command:

pgo show user -n pgo crdcluster

You should see output similar to:

cluster : crdcluster

secret : crdcluster-myuser-secret
	username: myuser
	password: password

secret : crdcluster-postgres-secret
	username: postgres
	password: password

secret : crdcluster-primaryuser-secret
	username: primaryuser
	password: password

Cleaning Up: How to Delete the Cluster

You can delete the cluster in a few ways, with two methods listed below:

Method 1: Use pgo

The simplest way to delete the cluster is to use the pgo command. The following will delete the cluster and any backups and persistent-volume claims (PVCs) associated with it:

pgo delete cluster -n pgo -b -d crdcluster

Method 2: Deleting Directly from the CRD

The following commands allow you to remove the cluster directly from the CRD. They delete the various secrets, PVCs, and the CRD of the crdcluster itself:

kubectl delete secret -n pgo \
    crdcluster-backrest-repo-config \
    crdcluster-postgres-secret \
    crdcluster-primaryuser-secret \
    crdcluster-myuser-secret
kubectl delete pgcluster -n pgo crdcluster
kubectl delete pvc -n pgo crdcluster crdcluster-pgbr-repo

Conclusion

The PostgreSQL Operator provides a lot of out-of-the-box functionality for managing PostgreSQL clusters on Kubernetes. It is also designed to be flexible and recognizes that certain users require the ability to interact directly with Kubernetes CRDs directly to create the desired PostgreSQL cluster to spec. This is another example of how you can use the PostgreSQL Operator to build out database clusters the way that you want to use them for your team.