How To 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.

Metis is a comprehensive database observability tool. It can track your live database activity, suggest improvements for schema, indexes, queries, configuration, and more. By onboarding your PostgreSQL server with Metis, you get access to live infrastructural metrics (CPU, memory usage) and database metrics (transactions, buffers, caches). Metis can look for anomalies in performance, index usage, or data distribution. See the documentation on how to monitor & troubleshoot your databases.

[.tip-box]This guide is also available on video.[.tip-box]

OnGres (StackGres)

StackGres is the full-stack Postgres Platform. Fully open-source software to run your own Postgres-as-a-Service on any cloud or on-prem. StackGres is a project from OnGres, the Postgres laser-focused startup ("OnGres" means "ON postGRES").

StackGres is a Kubernetes Operator for Postgres. It allows you to create production-ready Postgres clusters in seconds. No advanced Postgres expertise is required. You can use the built-in Web Console or the high-level Kubernetes CRDs for CLI and GitOps.

With StackGres, writing a simple YAML manifest (or point-and-click on the Web Console) is all that is needed to create production-ready Postgres clusters including high availability with Patroni, replication, connection pooling, automated backups, monitoring, and centralized logs.

With support for more than 150 Postgres extensions, StackGres is the most extensible Postgres platform available. It also provides support for Babelfish for Postgres (which brings SQL Server compatibility, at the wire protocol level, SQL and T-SQL) and integrations with Citus for sharding Postgres, Timescale for time-series, Supabase and now, FerretDB.

Metis

Metis is a powerful observability tool that can track all the aspects of your databases and database servers. It integrates with your database server with the help of an agent that you need to deploy in your cluster so it can monitor the ongoing activity.

Metis is distributed as a Docker container. You can install it in any environment supporting OCI containers, including AWS ECS, Kubernetes, AKS, or GKE. This makes using Metis straightforward and lets you configure everything in seconds.

Metis connects to your database and extracts details of the database activity. This includes metrics around infrastructure (CPU, memory usage, files opened), databases (transactions, caches, temporary files), live queries (and their execution plans), configuration, schema details, index usage, and much more. Metis explains what is happening in your databases. Metis can look for anomalies and detect drifts between environments. Metis can suggest actionable performance improvements and notify you when things degrade.

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:

minikube start

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:

minikube delete

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.

Conclusion

Throughout this article, we have explored the process of setting up a Kubernetes cluster, deploying the StackGres operator, and running Metis on top of it. We have covered steps such as creating an SGCluster, and monitoring basic database operations with Metis.

If you're eager to try Metis and experience its capabilities firsthand, we encourage you to try it out in your own Kubernetes environment, through the StackGres operator.

Here's the Stackgres runbook and Metis Guide to get you started.

If you encounter any issue or just wish to say "Hi!" and tell us how Metis works on StackGres, you may drop a line at the:

• Metis Discord
• StackGres Community Slack

‍