Deploy IvorySQL with IvorySQL Operator

Creating an Ivory cluster is pretty simple. Using the example in the examples/kustomize/ivory directory, all we have to do is run:

and IVYO will create a simple Ivory cluster named hippo in the ivory-operator namespace. You can track the status of your Ivory cluster using kubectl describe on the ivoryclusters.ivory-operator.ivorysql.org custom resource:

and you can track the state of the Ivory Pod using the following command:

We’re up and running — now let’s connect to our Ivory cluster!

IVYO creates a series of Kubernetes Services to provide stable endpoints for connecting to your Ivory databases. These endpoints make it easy to provide a consistent way for your application to maintain connectivity to your data. To inspect what services are available, you can run the following command:

will yield something similar to:

You do not need to worry about most of these Services, as they are used to help manage the overall health of your Ivory cluster. For the purposes of connecting to your database, the Service of interest is called hippo-primary. Thanks to IVYO, you do not need to even worry about that, as that information is captured within a Secret!

When your Ivory cluster is initialized, IVYO will bootstrap a database and Ivory user that your application can access. This information is stored in a Secret named with the pattern <clusterName>-pguser-<userName>. For our hippo cluster, this Secret is called hippo-pguser-hippo. This Secret contains the information you need to connect your application to your Ivory database:

All connections are over TLS. IVYO provides its own certificate authority (CA) to allow you to securely connect your applications to your Ivory clusters. This allows you to use the verify-full "SSL mode" of Ivory, which provides eavesdropping protection and prevents MITM attacks. You can also choose to bring your own CA, which is described later in this tutorial in the Customize Cluster section.

For this tutorial, we are going to connect Keycloak, an open source identity management application. Keycloak can be deployed on Kubernetes and is backed by an Ivory database. We provide an example of deploying Keycloak andan ivorycluster, the manifest below deploys it using our hippo cluster that is already running:

Notice this part of the manifest:

The above manifest shows how all of these values are derived from the hippo-pguser-hippo Secret. This means that we do not need to know any of the connection credentials or have to insecurely pass them around — they are made directly available to the application!

Using this method, you can tie application directly into your GitOps pipeline that connect to Ivory without any prior knowledge of how IVYO will deploy Ivory: all of the information your application needs is propagated into the Secret!

Now that we have seen how to connect an application to a cluster, let’s learn how to create a high availability Ivory cluster!

IVYO provides several ways to add replicas to make a HA cluster:

For the purposes of this tutorial, we will go with the first method and set spec.instances.replicas to 2. Your manifest should look similar to:

Apply these updates to your Ivory cluster with the following command:

Within moment, you should see a new Ivory instance initializing! You can see all of your Ivory Pods for the hippo cluster by running the following command:

Let’s test our high availability set up.

An important part of building a resilient Ivory environment is testing its resiliency, so let’s run a few tests to see how IVYO performs under pressure!

IvorySQL supports synchronous replication, which is a replication mode designed to limit the risk of transaction loss. Synchronous replication waits for a transaction to be written to at least one additional server before it considers the transaction to be committed. For more information on synchronous replication, please read about IVYO’s high availability architecture

To add synchronous replication to your Ivory cluster, you can add the following to your spec:

While PostgreSQL defaults synchronous_commit to on, you may also want to explicitly set it, in which case the above block becomes:

Note that Patroni, which manages many aspects of the cluster’s availability, will favor availability over synchronicity. This means that if a synchronous replica goes down, Patroni will allow for asynchronous replication to continue as well as writes to the primary. However, if you want to disable all writing if there are no synchronous replicas available, you would have to enable synchronous_mode_strict, i.e.:

Kubernetes affinity rules, which include Pod anti-affinity and Node affinity, can help you to define where you want your workloads to reside. Pod anti-affinity is important for high availability: when used correctly, it ensures that your Ivory instances are distributed amongst different Nodes. Node affinity can be used to assign instances to specific Nodes, e.g. to utilize hardware that’s optimized for databases.

In addition to affinity and anti-affinity settings, Kubernetes Pod Topology Spread Constraints can also help you to define where you want your workloads to reside. However, while PodAffinity allows any number of Pods to be added to a qualifying topology domain, and PodAntiAffinity allows only one Pod to be scheduled into a single topology domain, topology spread constraints allow you to distribute Pods across different topology domains with a finer level of control.

We’ve now seen how IVYO helps your application stay "always on" with your Ivory database. Now let’s explore how IVYO can minimize or eliminate downtime for operations that would normally cause that, such as resizing your Ivory cluster.

Memory and CPU resources are an important component for vertically scaling your Ivory cluster. Coupled with tweaks to your Ivory configuration file, allocating more memory and CPU to your cluster can help it to perform better under load.

It’s important for instances in the same high availability set to have the same resources. IVYO lets you adjust CPU and memory within the resources sections of the ivoryclusters.ivory-operator.ivorysql.org custom resource. These include:

The layout of these resources sections should be familiar: they follow the same pattern as the standard Kubernetes structure for setting container resources. Note that these settings also allow for the configuration of QoS classes.

For example, using the spec.instances.resources section, let’s say we want to update our hippo Ivory cluster so that each instance has a limit of 2.0 CPUs and 4Gi of memory. We can make the following changes to the manifest:

In particular, we added the following to spec.instances:

Apply these updates to your Ivory cluster with the following command:

Now, let’s watch how the rollout happens:

Observe how each Pod is terminated one-at-a-time. This is part of a "rolling update". Because updating the resources of a Pod is a destructive action, IVYO first applies the CPU and memory changes to the replicas. IVYO ensures that the changes are successfully applied to a replica instance before moving on to the next replica.

Once all of the changes are applied, IVYO will perform a "controlled switchover": it will promote a replica to become a primary, and apply the changes to the final Ivory instance.

By rolling out the changes in this way, IVYO ensures there is minimal to zero disruption to your application: you are able to successfully roll out updates and your users may not even notice!

Your application is a success! Your data continues to grow, and it’s becoming apparently that you need more disk. That’s great: you can resize your PVC directly on your ivoryclusters.ivory-operator.ivorysql.org custom resource with minimal to zero downtime.

PVC resizing, also known as volume expansion, is a function of your storage class: it must support volume resizing. Additionally, PVCs can only be sized up: you cannot shrink the size of a PVC.

You can adjust PVC sizes on all of the managed storage instances in a Ivory instance that are using Kubernetes storage. These include:

The above should be familiar: it follows the same pattern as the standard Kubernetes PVC structure.

For example, let’s say we want to update our hippo Ivory cluster so that each instance now uses a 10Gi PVC and our backup repository uses a 20Gi PVC. We can do so with the following markup:

In particular, we added the following to spec.instances:

and added the following to spec.backups.pgbackrest.repos.volume:

Apply these updates to your Ivory cluster with the following command:

You’ve now resized your Ivory cluster, but how can you configure Ivory to take advantage of the new resources? Let’s look at how we can customize the Ivory cluster configuration.

All connections in IVYO use TLS to encrypt communication between components. IVYO sets up a PKI and certificate authority (CA) that allow you create verifiable endpoints. However, you may want to bring a different TLS infrastructure based upon your organizational requirements. The good news: IVYO lets you do this!

There are several ways to add your own custom Kubernetes Labels to your Ivory cluster.

There are several ways to add your own custom Kubernetes Annotations to your Ivory cluster.

IVYO allows you to use pod priority classes to indicate the relative importance of a pod by setting a priorityClassName field on your Ivory cluster. This can be done as follows:

IvorySQL commits transactions by storing changes in its Write-Ahead Log (WAL). Because the way WAL files are accessed and utilized often differs from that of data files, and in high-performance situations, it can desirable to put WAL files on separate storage volume. With IVYO, this can be done by adding the walVolumeClaimSpec block to your desired instance in your ivorycluster spec, either when your cluster is created or anytime thereafter:

This volume can be removed later by removing the walVolumeClaimSpec section from the instance. Note that when changing the WAL directory, care is taken so as not to lose any WAL files. IVYO only deletes the PVC once there are no longer any WAL files on the previously configured volume.

IVYO can run SQL for you as part of the cluster creation and initialization process. IVYO runs the SQL using the psql client so you can use meta-commands to connect to different databases, change error handling, or set and use variables. Its capabilities are described in the psql documentation.

You’ve now seen how you can further customize your Ivory cluster, but what about managing users and databases? That’s a great question that is answered in the next section.

You can create a new user with the following snippet in the ivorycluster custom resource. Let’s add this to our hippo database:

You can now apply the changes and see that the new user is created. Note the following:

Let’s create a new database named zoo that we will let the rhino user access:

Inspect the hippo-pguser-rhino Secret. You should now see that the dbname and uri fields are now populated!

We can set role privileges by using the standard role attributes that Ivory provides and adding them to the spec.users.options. Let’s say we want the rhino to become a superuser (be careful about doling out Ivory superuser privileges!). You can add the following to the spec:

There you have it: we have created a Ivory user named rhino with superuser privileges that has access to the rhino database (though a superuser has access to all databases!).

Let’s say you want to revoke the superuser privilege from rhino. You can do so with the following:

If you want to add multiple privileges, you can add each privilege with a space between them in options, e.g.:

By default, IVYO does not give you access to the ivory user. However, you can get access to this account by doing the following:

This will create a Secret of the pattern <clusterName>-pguser-ivory that contains the credentials of the ivory account. For our hippo cluster, this would be hippo-pguser-ivory.

IVYO does not delete users automatically: after you remove the user from the spec, it will still exist in your cluster. To remove a user and all of its objects, as a superuser you will need to run DROP OWNED in each database the user has objects in, and DROP ROLE in your Ivory cluster.

For example, with the above rhino user, you would run the following:

Note that you may need to run DROP OWNED BY rhino CASCADE; based upon your object ownership structure — be very careful with this command!

IVYO does not delete databases automatically: after you remove all instances of the database from the spec, it will still exist in your cluster. To completely remove the database, you must run the DROP DATABASE command as a Ivory superuser.

For example, to remove the zoo database, you would execute the following:

Let’s look at how IVYO handles disaster recovery!

There are several attributes on the custom resource that are important to understand as part of the restore process. All of these attributes are grouped together in the spec.dataSource.ivorycluster section of the custom resource.

Please review the table below to understand how each of these attributes work in the context of setting up a restore operation.

Let’s walk through some examples for how we can clone and restore our databases.

Let’s create a clone of our hippo cluster that we created previously. We know that our cluster is named hippo (based on its metadata.name) and that we only have a single backup repository called repo1.

Let’s call our new cluster elephant. We can create a clone of the hippo cluster using a manifest like this:

Note this section of the spec:

This is the part that tells IVYO to create the elephant cluster as an independent copy of the hippo cluster.

The above is all you need to do to clone a Ivory cluster! IVYO will work on creating a copy of your data on a new persistent volume claim (PVC) and work on initializing your cluster to spec. Easy!

Did someone drop the user table? You may want to perform a point-in-time-recovery (PITR) to revert your database back to a state before a change occurred. Fortunately, IVYO can help you do that.

You can set up a PITR using the restore command of pgBackRest, the backup management tool that powers the disaster recovery capabilities of IVYO. You will need to set a few options on spec.dataSource.ivorycluster.options to perform a PITR. These options include:

A few quick notes before we begin:

With that in mind, let’s use the elephant example above. Let’s say we want to perform a point-in-time-recovery (PITR) to 2021-06-09 14:15:11-04, we can use the following manifest:

The section to pay attention to is this:

Notice how we put in the options to specify where to make the PITR.

Using the above manifest, IVYO will go ahead and create a new Ivory cluster that recovers its data up until 2021-06-09 14:15:11-04. At that point, the cluster is promoted and you can start accessing your database from that specific point in time!

Similar to the PITR restore described above, you may want to perform a similar reversion back to a state before a change occurred, but without creating another IvorySQL cluster. Fortunately, IVYO can help you do this as well.

You can set up a PITR using the restore command of pgBackRest, the backup management tool that powers the disaster recovery capabilities of IVYO. You will need to set a few options on spec.backups.pgbackrest.restore.options to perform a PITR. These options include:

A few quick notes before we begin:

To perform an in-place restore, users will first fill out the restore section of the spec as follows:

And to trigger the restore, you will then annotate the ivorycluster as follows:

And once the restore is complete, in-place restores can be disabled:

Notice how we put in the options to specify where to make the PITR.

Using the above manifest, IVYO will go ahead and re-create your Ivory cluster to recover its data up until 2021-06-09 14:15:11-04. At that point, the cluster is promoted and you can start accessing your database from that specific point in time!

You might need to restore specific databases from a cluster backup, for performance reasons or to move selected databases to a machine that does not have enough space to restore the entire cluster backup.

You can restore individual databases from a backup using a spec similar to the following:

where --db-include=hippo would restore only the contents of the hippo database.

Advanced high-availability and disaster recovery strategies involve spreading your database clusters across data centers to help maximize uptime. IVYO provides ways to deploy ivoryclusters that can span multiple Kubernetes clusters using an external storage system or IvorySQL streaming replication. The disaster recovery architecture documentation provides a high-level overview of standby clusters with IVYO can be found in the disaster recovery architecture documentation.

At some point, you will want to promote the standby to start accepting both reads and writes. This has the net effect of pushing WAL (transaction archives) to the pgBackRest repository, so we need to ensure we don’t accidentally create a split-brain scenario. Split-brain can happen if two primary instances attempt to write to the same repository. If the primary cluster is still active, make sure you shutdown the primary before trying to promote the standby cluster.

Once the primary is inactive, we can promote the standby cluster by removing or disabling its spec.standby section:

This change triggers the promotion of the standby leader to a primary IvorySQL instance and the cluster begins accepting writes.

You can clone a Ivory cluster from backups that are stored in AWS S3 (or a storage system that uses the S3 protocol), GCS, or Azure Blob Storage without needing an active Ivory cluster! The method to do so is similar to how you clone from an existing ivorycluster. This is useful if you want to have a data set for people to use but keep it compressed on cheaper storage.

For the purposes of this example, let’s say that you created a Ivory cluster named hippo that has its backups stored in S3 that looks similar to this:

Ensure that the credentials in ivyo-s3-creds match your S3 credentials. For more details on deploying a Ivory cluster using S3 for backups, please see the Backups section of the tutorial.

For optimal performance when creating a new cluster from an active cluster, ensure that you take a recent full backup of the previous cluster. The above manifest is set up to take a full backup. Assuming hippo is created in the ivory-operator namespace, you can trigger a full backup with the following command:

Wait for the backup to complete. Once this is done, you can delete the Ivory cluster.

Now, let’s clone the data from the hippo backup into a new cluster called elephant. You can use a manifest similar to this:

There are a few things to note in this manifest. First, note that the spec.dataSource.pgbackrest object in our new ivorycluster is very similar but slightly different from the old ivorycluster’s spec.backups.pgbackrest object. The key differences are:

Note also the similarities:

This is because the new restore pod for the elephant ivorycluster will need to reuse the configuration and credentials that were originally used in setting up the hippo ivorycluster.

In this example, we are creating a new cluster which is also backing up to the same S3 bucket; only the spec.backups.pgbackrest.global field has changed to point to a different path. This will ensure that the new elephant cluster will be pre-populated with the data from `hippo’s backups, but will backup to its own folders, ensuring that the original backup repository is appropriately preserved.

Deploy this manifest to create the elephant Ivory cluster. Observe that it comes up and running:

When it is ready, you will see that the number of expected instances matches the number of ready instances, e.g.:

The previous example shows how to use an existing S3 repository to pre-populate a ivorycluster while using a new S3 repository for backing up. But ivoryclusters that use cloud-based data sources can also use local repositories.

For example, assuming a ivorycluster called rhino that was meant to pre-populate from the original hippo ivorycluster, the manifest would look like this:

Now we’ve seen how to clone a cluster and perform a point-in-time-recovery, let’s see how we can monitor our Ivory cluster to detect and prevent issues from occurring.

Let’s look at how we can add the IvorySQL Exporter sidecar to your cluster using the kustomize/ivory example in the Postgres Operator examples repository.

Monitoring tools are added using the spec.monitoring section of the custom resource. Currently, the only monitoring tool supported is the IvorySQL Exporter configured with pgMonitor.

In the kustomize/ivory/ivory.yaml file, add the following YAML to the spec:

Save your changes and run:

IVYO will detect the change and add the Exporter sidecar to all Ivory Pods that exist in your cluster. IVYO will also do the work to allow the Exporter to connect to the database and gather metrics that can be accessed using the IVYO Monitoring stack.

Once the IvorySQL Exporter has been enabled in your cluster, follow the steps outlined in IVYO Monitoring to install the monitoring stack. This will allow you to deploy a pgMonitor configuration of Prometheus, Grafana, and Alertmanager monitoring tools in Kubernetes. These tools will be set up by default to connect to the Exporter containers on your Ivory Pods.

While the default Kustomize install should work in most Kubernetes environments, it may be necessary to further customize the project according to your specific needs.

For instance, by default fsGroup is set to 26 for the securityContext defined for the various Deployments comprising the IVYO Monitoring stack:

In most Kubernetes environments this setting is needed to ensure processes within the container have the permissions needed to write to any volumes mounted to each of the Pods comprising the IVYO Monitoring stack. However, when installing in an OpenShift environment (and more specifically when using the restricted Security Context Constraint), the fsGroup setting should be removed since OpenShift will automatically handle setting the proper fsGroup within the Pod’s securityContext.

Additionally, within this same section it may also be necessary to modify the supplmentalGroups setting according to your specific storage configuration:

Therefore, the following files (located under kustomize/monitoring) should be modified and/or patched (e.g. using additional overlays) as needed to ensure the securityContext is properly defined for your Kubernetes environment:

And to modify the configuration for the various storage resources (i.e. PersistentVolumeClaims) created by the IVYO Monitoring installer, the kustomize/monitoring/pvcs.yaml file can also be modified.

Additionally, it is also possible to further customize the configuration for the various components comprising the IVYO Monitoring stack (Grafana, Prometheus and/or AlertManager) by modifying the following configuration resources:

Finally, please note that the default username and password for Grafana can be updated by modifying the Grafana Secret in file kustomize/monitoring/grafana-secret.yaml.

Once the Kustomize project has been modified according to your specific needs, IVYO Monitoring can then be installed using kubectl and Kustomize:

And similarly, once IVYO Monitoring has been installed, it can uninstalled using kubectl and Kustomize:

Now that we can monitor our cluster, let’s explore how connection pooling can be enabled using IVYO and how it is helpful.

Let’s look at how we can add a connection pooler using the kustomize/keycloak example in the Ivory Operator repository examples folder.

Connection poolers are added using the spec.proxy section of the custom resource. Currently, the only connection pooler supported is PgBouncer.

The only required attribute for adding a PgBouncer connection pooler is to set the spec.proxy.pgBouncer.image attribute. In the kustomize/keycloak/ivory.yaml file, add the following YAML to the spec:

(You can also find an example of this in the kustomize/examples/high-availability example).

Save your changes and run:

IVYO will detect the change and create a new PgBouncer Deployment!

That was fairly easy to set up, so now let’s look at how we can connect our application to the connection pooler.

When a connection pooler is deployed to the cluster, IVYO adds additional information to the user Secrets to allow for applications to connect directly to the connection pooler. Recall that in this example, our user Secret is called keycloakdb-pguser-keycloakdb. Describe the user Secret:

You should see that there are several new attributes included in this Secret that allow for you to connect to your Ivory instance via the connection pooler:

Open up the file in kustomize/keycloak/keycloak.yaml. Update the DB_ADDR and DB_PORT values to be the following:

This changes Keycloak’s configuration so that it will now connect through the connection pooler.

Apply the changes:

Kubernetes will detect the changes and begin to deploy a new Keycloak Pod. When it is completed, Keycloak will now be connected to Ivory via the PgBouncer connection pooler!

IVYO deploys every cluster and component over TLS. This includes the PgBouncer connection pooler. If you are using your own custom TLS setup, you will need to provide a Secret reference for a TLS key / certificate pair for PgBouncer in spec.proxy.pgBouncer.customTLSSecret.

Your TLS certificate for PgBouncer should have a Common Name (CN) setting that matches the PgBouncer Service name. This is the name of the cluster suffixed with -pgbouncer. For example, for our hippo cluster this would be hippo-pgbouncer. For the keycloakdb example, it would be keycloakdb-pgbouncer.

To customize the TLS for PgBouncer, you will need to create a Secret in the Namespace of your Ivory cluster that contains the TLS key (tls.key), TLS certificate (tls.crt) and the CA certificate (ca.crt) to use. The Secret should contain the following values:

For example, if you have files named ca.crt, keycloakdb-pgbouncer.key, and keycloakdb-pgbouncer.crt stored on your local machine, you could run the following command:

You can specify the custom TLS Secret in the spec.proxy.pgBouncer.customTLSSecret.name field in your ivorycluster.ivory-operator.ivorysql.org custom resource, e.g.:

The PgBouncer connection pooler is highly customizable, both from a configuration and Kubernetes deployment standpoint. Let’s explore some of the customizations that you can do!

Now that we can enable connection pooling in a cluster, Let’s explore some administrative tasks such as manually restarting IvorySQL using IVYO. How do we do that?

There are times when you might need to manually restart IvorySQL. This can be done by adding or updating a custom annotation to the cluster’s spec.metadata.annotations section. IVYO will notice the change and perform a rolling restart.

For example, if you have a cluster named hippo in the namespace ivory-operator, all you need to do is patch the hippo ivorycluster with the following:

Watch your hippo cluster: you will see the rolling update has been triggered and the restart has begun.

You can shut down an Ivory cluster by setting the spec.shutdown attribute to true. You can do this by editing the manifest, or, in the case of the hippo cluster, executing a command like the below:

The effect of this is that all the Kubernetes workloads for this cluster are scaled to 0. You can verify this with the following command:

To turn an Ivory cluster that is shut down back on, you can set spec.shutdown to false.

You can pause the Ivory cluster reconciliation process by setting the spec.paused attribute to true. You can do this by editing the manifest, or, in the case of the hippo cluster, executing a command like the below:

Pausing a cluster will suspend any changes to the cluster’s current state until reconciliation is resumed. This allows you to fully control when changes to the ivorycluster spec are rolled out to the Ivory cluster. While paused, no statuses are updated other than the "Progressing" condition.

To resume reconciliation of an Ivory cluster, you can either set spec.paused to false or remove the setting from your manifest.

Credentials should be invalidated and replaced (rotated) as often as possible to minimize the risk of their misuse. Unlike passwords, every TLS certificate has an expiration, so replacing them is inevitable.

In fact, IVYO automatically rotates the client certificates that it manages before the expiration date on the certificate. A new client certificate will be generated after 2/3rds of its working duration; so, for instance, a IVYO-created certificate with an expiration date 12 months in the future will be replaced by IVYO around the eight month mark. This is done so that you do not have to worry about running into problems or interruptions of service with an expired certificate.

There may be times when you want to change the primary in your HA cluster. This can be done using the patroni.switchover section of the ivorycluster spec. It allows you to enable switchovers in your ivoryclusters, target a specific instance as the new primary, and run a failover if your ivorycluster has entered a bad state.

Let’s go through the process of performing a switchover!

First you need to update your spec to prepare your cluster to change the primary. Edit your spec to have the following fields:

After you apply this change, IVYO will be looking for the trigger to perform a switchover in your cluster. You will trigger the switchover by adding the ivory-operator.ivorysql.org/trigger-switchover annotation to your custom resource. The best way to set this annotation is with a timestamp, so you know when you initiated the change.

For example, for our hippo cluster, we can run the following command to trigger the switchover:

IVYO will detect this annotation and use the Patroni API to request a change to the current primary!

The roles on your database instance Pods will start changing as Patroni works. The new primary will have the master role label, and the old primary will be updated to replica.

The status of the switch will be tracked using the status.patroni.switchover field. This will be set to the value defined in your trigger annotation. If you use a timestamp as the annotation this is another way to determine when the switchover was requested.

After the instance Pod labels have been updated and status.patroni.switchover has been set, the primary has been changed on your cluster!

We’ve covered a lot in terms of building, maintaining, scaling, customizing, restarting, and expanding our Ivory cluster. However, there may come a time where we need to delete our Ivory cluster. How do we do that?