Run Metis on top of StackGres
In this how-to guide, we’ll walk you through the whole process of setting up Metis on a Kubernetes cluster using StackGres. This guide is also available as a video
This post has been published with explicit permission from Metis. It’s a copy of an article originally published on Metis Data’s website on February 12, 2024.
Setting Up Metis On Top Of StackGres
We’ll need to install the Kubernetes cluster, then install StackGres, then configure the StackGres cluster, and then configure Metis. Instructions below are for Amazon Linux 2 x86_64 but will work the same way on other operating systems like Mac OS and Windows (with slight syntax changes for PowerShell or other shells), and on ARM64 architecture.
Installing Kubernetes With Minikube
Before installing StackGres, you will need a running Kubernetes cluster and the usual command line tools kubectl and Helm. Please refer to the respective installation pages if you don’t have these tools. As for Kubernetes, if you don’t have one you can try easily with minikube. It can be installed like this:
curl -LO https://storage.googleapis.com/minikube/releases/latest/minikube-linux-amd64
sudo install minikube-linux-amd64 /usr/local/bin/minikube
This should give you a running single-node cluster in seconds (depending on your Internet connection speed). You can install it in other operating systems the way it’s described in the docs.
Keep in mind that minikube uses Docker behind the scenes. It works natively in Linux, and with a virtual machine in MacOS and Windows. Refer to installation instructions for Docker Desktop to learn how to install it on your machine.
You can now create the minikube cluster with:
This should give you the output similar to this:
* minikube v1.32.0 on Amazon 2
* Automatically selected the docker driver. Other choices: none, ssh
* Using Docker driver with root privileges
* Starting control plane node minikube in cluster minikube
* Pulling base image ...
* Creating docker container (CPUs=2, Memory=3900MB) ...
* Preparing Kubernetes v1.28.3 on Docker 24.0.7 ...
- Generating certificates and keys ...
- Booting up control plane ...
- Configuring RBAC rules ...
* Configuring bridge CNI (Container Networking Interface) ...
* Verifying Kubernetes components...
- Using image gcr.io/k8s-minikube/storage-provisioner:v5
* Enabled addons: storage-provisioner, default-storageclass
* Done! kubectl is now configured to use "minikube" cluster and "default" namespace by default
When you’re done with the examples, you can easily delete the cluster with:
Let’s now install StackGres.
Installing StackGres
The best way to install StackGres is through the official Helm chart. You can install it in this way:
curl -LO https://get.helm.sh/helm-v3.14.0-linux-amd64.tar.gz
tar -zxvf helm-v3.14.0-linux-amd64.tar.gz
sudo mv linux-amd64/helm /usr/local/bin/helm
Follow this page to install Helm on your machine.
For our particular setup, we use the following Helm commands:
helm repo add stackgres-charts https://stackgres.io/downloads/stackgres-k8s/stackgres/helm/
helm install --create-namespace --namespace stackgres stackgres-operator stackgres-charts/stackgres-operator
To confirm that the operator is running while also waiting for setup to complete, run the following commands:
kubectl wait -n stackgres deployment -l group=stackgres.io --for=condition=Available
kubectl get pods -n stackgres -l group=stackgres.io
As you run the first kubectl command, it should wait for the successful deployment, and the second command will list the pods running in the stackgres namespace.
NAME READY STATUS RESTARTS AGE
stackgres-operator-57bff75d-xlnfp 1/1 Running 1 (2m36s ago) 2m58s
Creating a StackGres Cluster
Here, we’ll create an SGCluster configured to fit Metis requirements. We’re going to use some resources created inline. Metis requires the pg_stat_statements extension, a separate user, and permissions to monitor databases.
Let’s create a password for the user that will get initialized based on the SQL command:
kubectl -n stackgres create secret generic metis-user-password-secret --from-literal=metis-create-user-sql="create user metis password 'admin123'"
Let’s now create a script that will create a database named metis and a user metis:
cat << EOF | kubectl apply -f -
apiVersion: stackgres.io/v1
kind: SGScript
metadata:
namespace: stackgres
name: cluster-scripts
spec:
scripts:
- name: create-metis-user
scriptFrom:
secretKeyRef:
name: metis-user-password-secret
key: metis-create-user-sql
- name: grant-monitor-to-metis
script: |
GRANT pg_monitor TO metis
- name: create-metis-database
script: |
CREATE DATABASE metis OWNER metis
- name: grant-conntent-to-metis-database
script: |
GRANT CONNECT ON DATABASE metis TO metis
- name: create-pgstatstatements-extension
database: metis
script: |
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
EOF
We can see the script has five parts. First, we create the user with a password and store the password in the Kubernetes secret. Next, we give pg_monitor permissions to the user. Next, we create the database. Then, we let the user connect to the database. Finally, we create the pg_stat_statements extension in the metis database.
We are now ready to create the Postgres cluster:
cat << EOF | kubectl apply -f -
apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
namespace: stackgres
name: cluster
spec:
postgres:
version: '15.0'
instances: 1
pods:
persistentVolume:
size: '5Gi'
managedSql:
scripts:
- sgScript: cluster-scripts
EOF
It should take a few seconds to a few minutes for the cluster to be up and running:
kubectl get pods -n stackgres
NAME READY STATUS RESTARTS AGE
cluster-0 6/6 Running 0 74s
Likewise, a database named metis must exist and be owned by the same user:
kubectl -n stackgres exec -it cluster-0 -c postgres-util -- psql -l metis
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
--------+----------+----------+---------+---------+------------+-----------------+--------------------
metis | metis | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =Tc/metis
...
Deploying Metis
We can now add monitoring of the database to Metis. Go to Metis and click Deploy:
Select Postgres and click Next:
Metis instructs us how to configure the user and grant necessary permissions. We already did that. Click Next.
Metis asks for the connection string now:
Let’s extract the server name:
kubectl get svc -n stackgres
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
cluster ClusterIP 10.99.23.208 <none> 5432/TCP,5433/TCP 2m34s
cluster-config ClusterIP None <none> <none> 2m34s
cluster-primary ExternalName <none> cluster.stackgres.svc.cluster.local <none> 2m34s
cluster-replicas ClusterIP 10.103.149.23 <none> 5432/TCP,5433/TCP 2m34s
cluster-rest ClusterIP 10.104.238.166 <none> 8008/TCP 2m34s
stackgres-operator ClusterIP 10.108.192.220 <none> 443/TCP 34m
stackgres-restapi ClusterIP 10.103.146.192 <none> 443/TCP 33m
The service name is cluster.stackgres.svc.cluster.local. The final URL should be like this:
postgresql://metis:admin123@cluster.stackgres.svc.cluster.local:5432/postgres?sslmode=disable
Click Next. Finally, click on Helm and copy the script to deploy Metis.
Extend the script with --namespace stackgres and run it. Your API key will be different as its a specific API key to be used with your project:
helm repo add metis https://charts.metisdata.io
helm install mmc metis/mmc-chart --namespace stackgres --set API_KEY=YOURAPIKEY --set-json CONNECTION_STRING='[{ "uri":"postgresql://metis:admin123@cluster.stackgres.svc.cluster.local:5432/metis?sslmode=disable" }]'
When you execute the script, you should get the following:
NAME: mmc
LAST DEPLOYED: Tue Jan 23 11:24:14 2024
NAMESPACE: stackgres
STATUS: deployed
REVISION: 1
TEST SUITE: None
We can verify that it works with:
kubectl get pods -n stackgres
NAME READY STATUS RESTARTS AGE
cluster-0 6/6 Running 0 25m
mmc-deployment-58dbb76fcc-vnnd5 1/1 Running 0 13s
You can now go to Metis Monitoring and see that the new host has been added:
When you click on the host, you should see the properties and databases:
You can also click on database metis and get all the details:
Notice that many charts are empty as it’s a completely new database.
Monitoring Live Database Activity
Let’s now add some data and run some queries in the database.
kubectl -n stackgres exec -it cluster-0 -c postgres-util -- psql --username=metis --dbname=metis -c "CREATE TABLE orders(region VARCHAR(100), amount INT, product VARCHAR(100))"
kubectl -n stackgres exec -it cluster-0 -c postgres-util -- psql --username=metis --dbname=metis -c "INSERT INTO orders(region, amount, product)VALUES ('EU', 10, 'Product1'), ('EU', 20, 'Product2'), ('US', 1, 'XYZ'), ('JP', 10, 'ABC')"
kubectl -n stackgres exec -it cluster-0 -c postgres-util -- psql --username=metis --dbname=metis -c "SELECT region, product, (SELECT SUM(amount) AS total_sales FROM orders AS o2 WHERE o2.region = o.region GROUP BY region)
FROM orders AS o
WHERE region IN (
SELECT region
FROM orders
GROUP BY region
HAVING SUM(amount) > (
SELECT SUM(amount)
FROM orders
) / 10
)"
We create table orders, insert some data, and then run a query that extracts the rows with some aggregation. This is the output:
region | product | total_sales
--------+----------+-------------
EU | Product1 | 30
EU | Product2 | 30
JP | ABC | 10
(3 rows)
You can now go to Metis and check the Table Sizes widget. It shows the orders table:
You can also go to Top Queries widget and notice that new queries are flowing to the database:
Metis captures the live activity of your database and can suggest how to improve things.