Skip to content

Examples

Questions kubectl cannot answer in one command, answered in one query.

Every query here runs in CI against a small shop namespace with a few things wrong in it, and the output under each is from that run. They assume you have finished Initialize, so server prod exists and its kinds are imported into schema k8s.

Which pods keep restarting?

CrashLoopBackOff is not a pod phase: a crash-looping pod is Running. It is not even a steady state of the container, which alternates between CrashLoopBackOff and the error from its last attempt, so filtering on it misses the pod half the time. The restart count only grows, and it lives on each container, a nested field kubectl cannot filter on.

SELECT namespace, name AS pod, phase, c->>'name' AS container,
       (c->>'restartCount')::int AS restarts,
       coalesce(c->'state'->'waiting'->>'reason',
                c->'lastState'->'terminated'->>'reason') AS why
  FROM k8s.core_pods,
       jsonb_array_elements(status->'containerStatuses') c
 WHERE (c->>'restartCount')::int > 0
 ORDER BY restarts DESC;

 namespace |       pod       |  phase  | container | restarts |        why        
-----------+-----------------+---------+-----------+----------+-------------------
 shop      | checkout-worker | Running | worker    |        3 | RunContainerError
(1 row)

Why is that pod not running?

The pod's state and the latest warning that explains it, on one row. kubectl get pods and kubectl get events are two commands and a squint.

SELECT DISTINCT ON (p.namespace, p.name)
       p.namespace, p.name AS pod, p.phase, e.reason, e.message
  FROM k8s.core_pods p
  JOIN k8s.core_events e ON e.namespace = p.namespace
                        AND e.involved_object->>'kind' = 'Pod'
                        AND e.involved_object->>'name' = p.name
 WHERE p.phase <> 'Running' AND e.type = 'Warning'
 ORDER BY p.namespace, p.name,
          coalesce(e.last_timestamp, e.event_time, e.creation_timestamp) DESC;

 namespace |            pod            |  phase  |      reason      |                                                                                                                                                                      message                                                                                                                                                                      
-----------+---------------------------+---------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 shop      | checkout-6b889d49cf-jt7bz | Pending | FailedScheduling | 0/1 nodes are available: 1 Insufficient cpu. no new claims to deallocate, preemption: 0/1 nodes are available: 1 Preemption is not helpful for scheduling.
 shop      | report                    | Pending | Failed           | Failed to pull image "registry.invalid/shop/report:1.4": failed to pull and unpack image "registry.invalid/shop/report:1.4": failed to resolve reference "registry.invalid/shop/report:1.4": failed to do request: Head "https://registry.invalid/v2/shop/report/manifests/1.4": dial tcp: lookup registry.invalid on 172.18.0.1:53: no such host
(2 rows)

Which rollouts are stuck, and why?

Four kinds in one statement: each Deployment short of its replicas, through its ReplicaSet to the pod that will not start, and the event that says why.

SELECT DISTINCT ON (d.namespace, d.name, p.name)
       d.namespace, d.name AS deployment,
       coalesce(d.ready_replicas, 0) || '/' || d.replicas AS ready,
       p.name AS pod, e.message AS why
  FROM k8s.apps_deployments d
  JOIN k8s.apps_replicasets r ON r.namespace = d.namespace
                             AND r.metadata->'ownerReferences'->0->>'name' = d.name
  JOIN k8s.core_pods p ON p.namespace = r.namespace
                      AND p.metadata->'ownerReferences'->0->>'name' = r.name
  LEFT JOIN k8s.core_events e ON e.namespace = p.namespace
                             AND e.involved_object->>'kind' = 'Pod'
                             AND e.involved_object->>'name' = p.name
                             AND e.type = 'Warning'
 WHERE coalesce(d.ready_replicas, 0) < d.replicas
   AND p.phase <> 'Running'
 ORDER BY d.namespace, d.name, p.name,
          coalesce(e.last_timestamp, e.event_time, e.creation_timestamp) DESC NULLS LAST;

 namespace | deployment | ready |            pod            |                                                                            why                                                                             
-----------+------------+-------+---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------
 shop      | checkout   | 0/1   | checkout-6b889d49cf-jt7bz | 0/1 nodes are available: 1 Insufficient cpu. no new claims to deallocate, preemption: 0/1 nodes are available: 1 Preemption is not helpful for scheduling.
(1 row)

Find it and fix it in one statement

A query can also write. This annotates each Deployment in shop using under a fifth of the memory it requests, so the finding sits on the object where kubectl describe will show it. Each row is a real Kubernetes update.

WITH used AS (
  SELECT m.namespace, m.name AS pod, sum(axiom_quantity(c->'usage'->>'memory')) AS bytes
    FROM k8s.metrics_k8s_io_pods m, jsonb_array_elements(m.containers) c
   GROUP BY 1, 2),
requested AS (
  SELECT p.namespace, p.name AS pod,
         r.metadata->'ownerReferences'->0->>'name' AS deployment,
         sum(axiom_quantity(c->'resources'->'requests'->>'memory')) AS bytes
    FROM k8s.core_pods p
    JOIN k8s.apps_replicasets r
      ON r.namespace = p.namespace AND r.name = p.metadata->'ownerReferences'->0->>'name',
         jsonb_array_elements(p.spec->'containers') c
   GROUP BY 1, 2, 3),
ratio AS (
  SELECT q.namespace, q.deployment, round(100 * sum(u.bytes) / sum(q.bytes)) AS pct
    FROM requested q JOIN used u USING (namespace, pod)
   GROUP BY 1, 2
  HAVING sum(q.bytes) > 0)
UPDATE k8s.apps_deployments d
   SET annotations = coalesce(d.annotations, '{}')
                     || jsonb_build_object('axiom/memory-used-pct', r.pct::text)
  FROM ratio r
 WHERE d.namespace = r.namespace AND d.name = r.deployment AND r.pct < 20
   AND d.namespace = 'shop'  -- one namespace at a time: this writes to the cluster
RETURNING d.namespace, d.name, r.pct;

 namespace |  name   | pct 
-----------+---------+-----
 shop      | catalog |   0
 shop      | web     |   1
(2 rows)

Changing the cluster covers the grant this needs, and what happens when two writers race.

Join the cluster to your own data

Axiom runs inside your application's Postgres, so the cluster joins to your own tables with no export in between. Given which customer owns which namespace:

CREATE TABLE tenants (namespace text PRIMARY KEY, customer text, plan text);
INSERT INTO tenants VALUES ('shop', 'Acme Corp', 'enterprise');

Which customers are affected by a failing pod right now, and why:

SELECT DISTINCT ON (p.namespace, p.name)
       t.customer, t.plan, p.name AS pod, e.reason
  FROM tenants t
  JOIN k8s.core_pods p ON p.namespace = t.namespace
  JOIN k8s.core_events e ON e.namespace = p.namespace
                        AND e.involved_object->>'kind' = 'Pod'
                        AND e.involved_object->>'name' = p.name
 WHERE e.type = 'Warning'
 ORDER BY p.namespace, p.name,
          coalesce(e.last_timestamp, e.event_time, e.creation_timestamp) DESC;

 customer  |    plan    |            pod            |      reason      
-----------+------------+---------------------------+------------------
 Acme Corp | enterprise | checkout-6b889d49cf-jt7bz | FailedScheduling
 Acme Corp | enterprise | checkout-worker           | BackOff
 Acme Corp | enterprise | report                    | Failed
(3 rows)

More patterns

Aggregate. Where pods are failing, and how:

SELECT namespace, phase, count(*)
  FROM k8s.core_pods
 WHERE phase <> 'Running'
 GROUP BY namespace, phase
 ORDER BY count(*) DESC;

 namespace |  phase  | count 
-----------+---------+-------
 shop      | Pending |     2
(1 row)

Filter on a promoted column. Deployments that never finished rolling out. The replica counts are bigint, so they compare as numbers with no cast, and a missing field is NULL rather than 0:

SELECT namespace, name, replicas, ready_replicas
  FROM k8s.apps_deployments
 WHERE coalesce(ready_replicas, 0) < replicas;

 namespace |   name   | replicas | ready_replicas 
-----------+----------+----------+----------------
 shop      | checkout |        1 |               
(1 row)

Filter on anything, through raw. kubectl offers label selectors and a few field selectors. Anything no column promotes is still in raw, such as every container that sets no memory limit:

SELECT d.namespace, d.name AS deployment, c->>'name' AS container
  FROM k8s.apps_deployments d,
       jsonb_array_elements(d.raw->'spec'->'template'->'spec'->'containers') c
 WHERE c->'resources'->'limits'->'memory' IS NULL;

     namespace      |       deployment       |       container        
--------------------+------------------------+------------------------
 kube-system        | metrics-server         | metrics-server
 local-path-storage | local-path-provisioner | local-path-provisioner
 shop               | checkout               | checkout
 shop               | web                    | web
(4 rows)

Ask several clusters at once. Each cluster is its own server and its own schema, so one question across them is a UNION ALL. This needs a second cluster and gateway, so it is the shape rather than something to paste:

-- after CREATE SERVER stage ... and IMPORT FOREIGN SCHEMA k8s FROM SERVER stage INTO stage
SELECT 'prod' AS cluster, namespace, name, phase FROM k8s.core_pods   WHERE phase <> 'Running'
UNION ALL
SELECT 'stage',           namespace, name, phase FROM stage.core_pods WHERE phase <> 'Running';

Bigger examples

Custom resources and writes may need a grant

The shipped RBAC in deploy/k8s/gateway-rbac.yaml reads broadly, covering workloads, nodes, networking, storage, events and metrics, so most examples work as imported. It never reads Secrets, and it writes only ConfigMaps and the example CRD.

A custom resource is readable if its operator ships an aggregate-to-view role. Otherwise, label a read-only ClusterRole for its API group axiom.dhilipkumars.github.io/aggregate-to-gateway: "true" (Restricting access shows one). To make a kind writable, add its verbs to the axiom-gateway ClusterRole. Neither needs a gateway restart; only revoking a grant does. Either way, re-import:

-- foreign tables are catalog objects; they do not follow an RBAC change
IMPORT FOREIGN SCHEMA k8s FROM SERVER prod INTO k8s;