Getting Started with PostgreSQL Operator 4.3 in OpenShift
The first step of working with any software is getting it installed. Based on your environment, this could be as easy as a one-click installer, or require a manual that's hundreds of pages long with thousands of config bits to tune. Deploying complex applications on Kubernetes and OpenShift can often fall somewhere in the middle. For example, deploying an Operator on OpenShift can be viewed as an intimidating thing, but it doesn't have to be. Here we're going to walk through the quickest and easiest possible way to get started!
Below I’ll guide you through the steps for installing and using the PostgreSQL Operator using the pgo-deployer. The pgo-deployer is included in the PostgreSQL Operator, and is presented in a container. In this guide, I’ll be using OpenShift 4.4.3 but any version on 3.11 or greater will work.
Confirming your environment
Before you get started, you will need to confirm your environment is set up to deploy the PostgreSQL Operator.
You will need an OpenShift environment where:
- You are running OpenShift v 3.11+
- You have enough privileges to install an application, i.e. you can add a ClusterRole. If you’re a Cluster Admin, you’re all set
- There are PersistentVolumes available
- You can access your OpenShift cluster using oc
Install the PostgreSQL Operator
To get started we’ll first need to download the PostgreSQL Operator Installer manifest to our OpenShift environment. In this example we’ll do so with the following command:
curl https://github.com/CrunchyData/postgres-operator/blob/REL_4_3/installers/kubectl/postgres-operator.yml
Note the "REL_4_3" - this is pulling the installer from the PostgreSQL Operator 4.3.x branch. Also note that if you are installing on OpenShift 3.11, you will have to download the installer manifest from as such:
curl https://github.com/CrunchyData/postgres-operator/blob/REL_4_3/installers/kubectl/postgres-operator-ocp311.yml > postgres-operator.yml
It’s important to review the configuration parameters in detail. In this example, we’ll be using many of the default settings, but there are a number of variables that should be changed. A good example of this would be PGO_ADMIN_PASSWORD. This password is used with the pgo client to manage your PostgreSQL clusters. Changing this from the default of “password” is generally recommended. Additionally, you’ll want to specify your particular storage class. We’ll be using GCE for our instance.
- name: BACKREST_STORAGE
value: “gce"
- name: BACKUP_STORAGE
value: 'gce'
- name: PRIMARY_STORAGE
value: 'gce'
- name: REPLICA_STORAGE
value: 'gce'
All of these changes (password, storage class, etc) should be applied by editing the postgres-operator.yml file that we downloaded in the above steps.
Now that we’ve edited our manifest file we can begin installing the PostgreSQL Operator. First, we’ll need to create the pgo namespace. We can do this with the following command:
oc create namespace pgo
To launch the pgo-deployer container we’ll need to apply the postgres-operator.yml manifest. We can do this with the following command:
oc apply -f postgres-operator.yml
Next we’ll download the pgo client setup script to help set up the OpenShift environment for using the PostgreSQL Operator.
curl https://github.com/CrunchyData/postgres-operator/blob/REL_4_3/installers/olm/install.sh
chmod +x install.sh
Once the PostgreSQL Operator has installed successfully, you can then run the install.sh script. This will do a number of different things for you, like creating a subscription to install the operator, and creating a client pod where commands can be executed. This can take a few minutes to complete depending on your OpenShift cluster.
While the PostgreSQL Operator is being installed, for ease of using the pgo command line interface, you will need to set up some environmental variables.
You can do so with the following command within your RHEL or CentOS system.
export PATH="${HOME?}/.pgo/pgo/pgouser"
export PGOUSER="${HOME?}/.pgo/pgo/pgouser"
export PGO_CA_CERT="${HOME?}/.pgo/pgo/client.crt"
export PGO_CLIENT_CERT="${HOME?}/.pgo/pgo/client.crt"
export PGO_CLIENT_KEY="${HOME?}/.pgo/pgo/client.key"
If you wish to permanently add these variables to your environment, you can run the following:
export PATH="${HOME?}/.pgo/pgo/pgouser"
export PGOUSER="${HOME?}/.pgo/pgo/pgouser"
export PGO_CA_CERT="${HOME?}/.pgo/pgo/client.crt"
export PGO_CLIENT_CERT="${HOME?}/.pgo/pgo/client.crt"
export PGO_CLIENT_KEY="${HOME?}/.pgo/pgo/client.key"
source ~/.bashrc
NOTE: For macOS users, you must use ~/.bash_profile instead of ~/.bashrc
Verify the Installation
Below are a few steps to check if the PostgreSQL Operator is up and running.
By default, the PostgreSQL Operator installs into a namespace called pgo. First, see that the the OpenShift Deployment of the Operator exists and is healthy:
oc -n pgo get deployments
If successful, you should see output similar to this:
NAME READY UP-TO-DATE AVAILABLE AGE
postgres-operator 1/1 1 1 16h
Next, see if the Pods that run the PostgreSQL Operator are up and running:
oc -n pgo get pods
If successful, you should see output similar to this:
NAME READY STATUS RESTARTS AGE
postgres-operator-56d6ccb97-tmz7m 4/4 Running 0 2m
Connect to the PostgreSQL Operator
Finally, let’s see if we can connect to the PostgreSQL Operator from the pgo command-line client. In order to communicate with the PostgreSQL Operator API server, you will first need to set up a port forwarding to your local environment.
In a new console window, run the following command to set up a port forward:
oc -n pgo port-forward svc/postgres-operator 8443:8443
Back to your original console window, you can verify that you can connect to the PostgreSQL Operator using the following command:
pgo version
If successful, you should see output similar to this:
pgo client version 4.3.2
pgo-apiserver version 4.3.2
Have Some Fun - Create a Postgres Cluster
The quickstart installation method creates two namespaces that you can deploy your PostgreSQL clusters into called pgouser1 and pgouser2. Let’s create a new Postgres cluster in pgouser1:
pgo create cluster -n pgouser1 hippo
If successful, you should see output similar to this:
created Pgcluster hippo
workflow id 1cd0d225-7cd4-4044-b269-aa7bedae219b
This will create a Postgres cluster named hippo. It may take a few moments for the cluster to be provisioned. You can see the status of this cluster using the pgo test command:
pgo test -n pgouser1 hippo
When everything is up and running, you should set output similar to this:
cluster : hippo
Services
primary (10.97.140.113:5432): UP
Instances
primary (hippo-594f794476-glt9n): UP
Once everything is up and running, we can check to make sure our pgBackRest stanza and backup completed successfully. If successful, we should see “Completed” under the “Status” column.
NAME READY STATUS RESTARTS AGE
backrest-backup-hippo-pwpwq 0/1 Completed 0 11m
hippo-594f794476-glt9n 1/1 Running 0 12m
hippo-backrest-shared-repo-b5d7f8f68-6rl8k 1/1 Running 0 13m
hippo-stanza-create-5v7j4 0/1 Completed 0 12m
Awesome! We now have a Postgres primary as well as a successful backup. We can log into the pgBackRest repo and check to make sure by issuing the following command:
oc exec -it hippo-backrest-shared-repo-b5d7f8f68-6rl8k -n pgouser1 bash
From here, you’ll be able to run your pgbackrest info command to check on the
status of the backup.
bash-4.2$ pgbackrest info
stanza: db
status: ok
cipher: none
db (current)
wal archive min/max (12-1): 000000010000000000000001/000000010000000000000004
full backup: 20200511-171705F
timestamp start/stop: 2020-05-11 17:17:05 / 2020-05-11 17:17:16
wal start/stop: 000000010000000000000002 / 000000010000000000000002
database size: 31.0MB, backup size: 31.0MB
repository size: 3.7MB, repository backup size: 3.7MB
The pgo test command provides the basic information you need to connect to your PostgreSQL cluster from within your OpenShift environment. For more detailed information, you can use the following command:
pgo show cluster -n pgouser1 hippo
cluster : hippo (crunchy-postgres-ha:centos7-12.2-4.3.0)
pod : hippo-594f794476-glt9n (Running) on opensh-c49lw-w-b-k4fb9.c.container-suite.internal (1/1) (primary)
pvc : hippo
resources : Memory: 128Mi
storage : Primary=300M Replica=300M
deployment : hippo
deployment : hippo-backrest-shared-repo
service : hippo - ClusterIP (172.30.62.61)
labels : crunchy-pgha-scope=hippo crunchy_collect=false deployment-name=hippo pgo-backrest=true pgo-version=4.3.0 pgouser=admin workflowid=e3793de6-3d6c-4278-b37b-8b49f79b076a autofail=true crunchy-pgbadger=false name=hippo pg-cluster=hippo pg-pod-anti-affinity=
PGO CLI Commands
scale (up)
We’ve now verified that we have a single Postgres cluster with 1 primary! You’ve likely read through some of the documentation at this point and noticed the Crunchy PostgreSQL Operator also offers High Availability. Let’s scale up our existing cluster and make it so.
To create 2 replicas in addition to our existing primary, let’s run the following command on our specify our “hippo” cluster, shall we?
pgo scale hippo --replica-count=2 -n pgouser1
Here, you’ll be asked if you’re sure you want to proceed. Type “yes” and you’ll get output showing that two Pgreplica’s have been created:
created Pgreplica hippo-rkol
created Pgreplica hippo-egzo
Very good! At this point we can re-run our pgo show cluster command. We should see additional pods and pvcs for our newly created replicas.
cluster : hippo (crunchy-postgres-ha:centos7-12.2-4.3.0)
pod : hippo-594f794476-glt9n (Running) on opensh-c49lw-w-b-k4fb9.c.container-suite.internal (1/1) (primary)
pvc : hippo
pod : hippo-egzo-7d567dc84d-sfqfr (Running) on opensh-c49lw-w-b-k4fb9.c.container-suite.internal (1/1) (replica)
pvc : hippo-egzo
pod : hippo-rkol-b69947d99-mr58r (Running) on opensh-c49lw-w-c-hws4x.c.container-suite.internal (1/1) (replica)
pvc : hippo-rkol
The pgo test command now shows the additional PostgreSQL clusters as well.
cluster : hippo
Services
primary (172.30.62.61:5432): UP
replica (172.30.213.187:5432): UP
Instances
primary (hippo-594f794476-glt9n): UP
replica (hippo-egzo-7d567dc84d-sfqfr): UP
replica (hippo-rkol-b69947d99-mr58r): UP
You now have a High Availability cluster with 2 replicas! With automated failover now available, and Pod Affinity to help configure how aggressive automatic self healing of failed primaries should be.
scaledown
With the pgo command you can add and remove replicas as needed. Need to remove a replica because it’s just not needed? No problem! With the Scaledown command we can specify a particular replica to remove. Let’s do it, but first we need to choose a replica. We can do so by querying available replicas with the following command:
pgo scaledown hippo -n pgouser1 --query
Cluster: hippo
REPLICA STATUS NODE REPLICATION LAG
hippo-egzo running opensh-c49lw-w-b-k4fb9.c.container-suite.internal 0 MB
hippo-rkol running opensh-c49lw-w-c-hws4x.c.container-suite.internal 0 MB
For this example we’ll use hippo-egzo as our replica to remove.
pgo scaledown hippo -n pgouser1 --target=hippo-egzo
For this example we’ll use hippo-egzo as our replica to remove.
pgo scaledown hippo -n pgouser1 --target=hippo-egzo
Again, you’ll be prompted to confirm your decision. We’re ready to go here, so we’ll type “yes” and proceed.
WARNING: Are you sure? (yes/no): yes
deleted replica hippo-egzo
If we run our pgo test command again, we’ll now get output without the replica we chose to delete.
cluster : hippo
Services
primary (172.30.62.61:5432): UP
replica (172.30.213.187:5432): UP
Instances
primary (hippo-594f794476-glt9n): UP
replica (hippo-rkol-b69947d99-mr58r): UP
Failover (manual)
Thanks to distributed consensus, the PostgreSQL Operator is designed to tolerate automated failover with ease. This is great, but there may be a need to test failovers manually from time to time. With the pgo client, this can be done easily. Let’s prove that here. Just like with the replica scaledown command, let’s query available failover targets and choose one. We’ll take a note of the provided replica here since it will soon switch places with the existing primary.
pgo failover -n pgouser1 hippo --query
Cluster: hippo
REPLICA STATUS NODE REPLICATION LAG
hippo-rkol running opensh-c49lw-w-c-hws4x.c.container-suite.internal
0 MB
Since we recently scaled down the number of replicas we have, we only see one remaining replica. We’ll choose this replica as our failover target.
pgo failover hippo -n pgouser1 --target=hippo-rkol
We’ll be asked if we’re sure we want to do this. Again, we’ll type “yes” and a failover Pgtask will be created for us.
Running the pgo test command we can now see our old replica, hippo-rkol, is now a primary. Easy!
cluster : hippo
Services
primary (172.30.62.61:5432): UP
replica (172.30.213.187:5432): UP
Instances
replica (hippo-594f794476-glt9n): UP
primary (hippo-rkol-b69947d99-mr58r): UP
Labels
Do you have a need to label your PostgreSQL clusters? The Crunchy PostgreSQL Operator allows you to do so easily in the event you need to differentiate between production, UAT, SIT, or DEV environments for example. Let’s create a label for a DEV environment. This will be applied to our “hippo” cluster.
pgo label hippo --label=env=development
Now if we run our pgo show cluster command, we’ll notice that in the labels section there is an “env=development”. This can be quite useful if you need to group PostgreSQL clusters together based on what they’re used for.
labels : workflowid=e3793de6-3d6c-4278-b37b-8b49f79b076a autofail=true crunchy-pgbadger=false crunchy-pgha-scope=hippo name=hippo pg-cluster=hippo pg-pod-anti-affinity= pgo-backrest=true crunchy_collect=false deployment-name=hippo-rkol env=development pgo-version=4.3.0 pgouser=admin
Creating Backups
When we first deployed the Operator, we took a look at how a stanza is created, and a backup is taken. What if we need to create a backup, manually, after deployment? No problem! The pgo command makes this easy by utilizing pgBackRest. Let’s run a backup.
pgo backup -n pgouser1 hippo
This will create a Pgtask for pgBackRest. Last time, we connected to the pgBackRest manually and ran the pgbackrest info command to look at our list of available backups. This time, let’s use the pgo command since it’s a quick and easy way to get the same list. In this output, we’ll see the full backup that was taken a day before, when the cluster was deployed, as well as the manually run Incremental.
cluster: hippo
storage type: local
stanza: db
status: ok
cipher: none
db (current)
wal archive min/max (12-1)
full backup: 20200511-171705F
timestamp start/stop: 2020-05-11 17:17:05 +0000 UTC / 2020-05-11 17:17:16 +0000 UTC
wal start/stop: 000000010000000000000002 / 000000010000000000000002
database size: 31.0MiB, backup size: 31.0MiB
repository size: 3.7MiB, repository backup size: 3.7MiB
backup reference list:
incr backup: 20200511-171705F_20200512-132803I
timestamp start/stop: 2020-05-12 13:28:03 +0000 UTC / 2020-05-12 13:28:06 +0000 UTC
wal start/stop: 000000020000000000000009 / 000000020000000000000009
database size: 31.0MiB, backup size: 229.8KiB
repository size: 3.7MiB, repository backup size: 27.1KiB
backup reference list: 20200511-171705F
Backup types
Taking a full backup instead of an incremental is easy. All we have to do here is pass the “--backup-opts” flag and specify “--type=full”. For example:
pgo backup -n pgouser1 hippo --backup-opts=”--type=full”
Backup schedule creation
You can just as easily schedule certain types of backups to run on a cron schedule with the create schedule command listed below.
pgo create schedule -n pgouser1 hippo --schedule="*/1 * * * *" --schedule-type=pgbackrest --pgbackrest-backup-type=diff
Backup schedule deletion
This creates a schedule to run a differential backup every minute. That’s a bit aggressive for some environments and datasets. Let’s delete this schedule to ensure we don’t fill up our pgBackRest repo, shall we?
pgo delete schedule -n pgouser1 --schedule -name=hippo-pgbackrest-diff
Restore from backup
Having backups is great, and definitely necessary, however, backups without regular restore testing is like not having a backup at all! It’s extremely important to test your backups by restoring them in regular testing. Let’s restore a backup to make sure it restores appropriately.
pgo restore -n pgouser1 hippo
It’s important to note that the primary will be stopped and recreated. The following prompt ensures you’re aware of this by requesting a “yes” or “no” input.
Warning: If currently running, the primary database in this cluster will be stopped and recreated as part of this workflow!
WARNING: Are you sure? (yes/no): yes
After selecting “yes” we can then check the status of the job by running the following command:
oc get job -n pgouser1
NAME COMPLETIONS DURATION AGE
backrest-backup-hippo 1/1 18s 62s
hippo-stanza-create 1/1 12s 133m
restore-hippo-mxrm 1/1 35s 118s
Our restore was successful! There are a number of different flags that can be passed in the restore command in order to get your desired result for the restore. More information can be found in the PGO Restore section of our documentation.
Delete
So we’ve deployed and tested numerous pgo command line examples. The Crunchy PostgreSQL Operator is extremely robust, and has loads of features to experiment with! At this point we’ve walked through the beginning steps, and have a good idea of how to get started. Let’s delete the cluster. In the event you’d like to keep the data, you can always pass the “--keep-data” flag so that the PVC is kept. In this example, we’ll simply delete the data.
pgo delete cluster hippo -n pgouser1
As usual, we’ll be prompted to confirm our decision.
WARNING - This will delete ALL OF YOUR DATA, including backups. Proceed? (yes/no): yes
deleted pgcluster hippo
Voila! The cluster is now in the process of being deleted. It may take a few minutes, but you’ll be able to run the following oc commands to ensure the cluster has been deleted successfully.
oc get pod -n pgouser1
oc get pvc -n pgouser1
If you run the commands before the cluster is actually deleted, you’ll see “terminating” in the status field instead of the following:
No resources found in pgouser1 namespace.
There is much, much more to do within the Crunchy PostgreSQL Operator. This fundamental guide is only the beginning. If the above functionality was as interesting to you as we hope it was, please visit the official documentation to see what else the Operator has to offer!
