Capture querys for pgsql on kubernetes

pgsql_capture.sh

#!/bin/bash

# This script automates the process of collecting pgsql query logs from
# kubernetes. The idea comes from a fabric script used by instagram (circa
# 2012) https://gist.github.com/mikeyk/2307647
#
# Usage ./pgsql_capture.sh namespace -l app=pgsql
#
# The arguments are passed to `kubectl get pods --namespace` to find the pod to run against

COLLECT_SECONDS=30
PGSQL_CONF=/data/pgdata/postgresql.conf
CONTAINER=pgsql
OUTPUT=/tmp/pgfouine-$(date "+%Y-%m-%d:%H:%M:%S").html

set -e

namespace=${1}
shift
pod=$(kubectl --namespace=${namespace} get pods -o jsonpath='{.items[0].metadata.name}' $@)
remote="kubectl --namespace=${namespace} exec ${pod} --"

echo "Going to log all queries for ${pod} in namespace ${namespace} for ${COLLECT_SECONDS}s"
read -p "Are you sure? " -n 1 -r
echo    # (optional) move to a new line
if [[ ! $REPLY =~ ^[Yy]$ ]]
then
    exit 1
fi

set -x

$remote perl -pi -e "s/^log_min_duration_statement *= *(.*)/log_min_duration_statement = 0 #\1/" ${PGSQL_CONF}
$remote kill -HUP 1
sleep 30
$remote perl -pi -e "s/^log_min_duration_statement = 0 #(.*)/log_min_duration_statement = \1/" ${PGSQL_CONF}
$remote kill -HUP 1
kubectl --namespace=${namespace} logs -c ${CONTAINER} --tail=10000 ${pod} > /tmp/pgfouine.txt
docker run -i --rm keegancsmith/pgfouine-docker -logtype stderr - < /tmp/pgfouine.txt > ${OUTPUT}
open ${OUTPUT}