Administrator Queries

# CONNECT to the postgres pod and start a psql session
kubectl exec -it postgres-statefulset-0  -- psql -U postgres -d cerebrum
# Run this query
COPY (
SELECT DISTINCT
   ds.name AS name,
   dsr.name AS object_type,
   ds.id AS object_id,
   dss.name AS source_name,
   r.name AS upstream_name,
   rr.name AS upstream_object_type,
   r.signature AS upstream_object_signature,
   r.id AS upstream_id,
   rs.name AS upstream_source_name
FROM node ds
INNER JOIN node_ref dsr ON dsr.id = ds.node_ref_id
INNER JOIN source dss ON dss.id =  ds.source_id
INNER JOIN edge ON edge.source_node_id = ds.id AND edge.source_node_ref_id = ds.node_ref_id
INNER JOIN node r ON r.id = edge.target_node_id AND r.node_ref_id = edge.target_node_ref_id
INNER JOIN source rs ON rs.id = r.source_id
INNER JOIN node_ref rr ON rr.id = r.node_ref_id
WHERE ds.node_ref_id IN (15,27)
AND edge.edge_ref_id IN (6,26,32)
AND edge.source_node_ref_id IN (15,27)
AND edge.target_node_ref_id IN (3,4)
AND r.node_ref_id IN (3,4)
ORDER BY source_name, object_type, name, upstream_source_name, upstream_object_type, upstream_object_signature
) TO '/tmp/extract.csv' DELIMITER ',' CSV HEADER;
# Exist out of postgres pod.
# Use kubectl to copy the csv out of the pod.
kubectl cp postgres-statefulset-0:/tmp/extract.csv extract_table_size.csv