Watching a PostgreSQL Query

few years ago, I had to debug a very hard-to-reproduce bug with my production backend deployment. for some reason db writes were coming in late or not coming in at all. in order to monitor the state of my database, i created a small script that runs my query every n seconds.

#!/usr/bin/env bash
if [ "$1" == "help" ] || [ $# -eq 0 ]; then
    echo "usage: watch-psql 'select count(*) from users' 0.1 123 app";
    echo "                               ^                ^   ^   ^ ";
    echo "                             query            time pass db";
    exit 0;
fi

if [ -z "$1" ]; then
    echo "No query provided";
    exit 1;
fi

if [ -z "$2" ]; then
    echo "No time provided";
    exit 1;
fi

if [ -z "$3" ]; then
    echo "No password provided";
    exit 1;
fi

if [ -z "$4" ]; then
    echo "No db provided";
    exit 1;
fi

PGPASSWORD=$3 watch -n $2 "echo \"$1\" | psql -h localhost -U postgres -d $4"

the code is kinda crappy but it helped. nowadays i’d use datagrip’s built in refresh feature