Created
February 8, 2022 15:27
-
-
Save adamzwakk/3491fa8e839e698aa42e8f7cbbbf383f to your computer and use it in GitHub Desktop.
Get active queries from postgres and send them to influxdb (or comment out curl and echo the lines to be used with telegraf)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
PGSQL_HOST="localhost" | |
PGSQL_PORT="5432" | |
PGSQL_DATABASE="postgres" | |
PGSQL_USERNAME="postgres" | |
export PGPASSWORD="passwd" | |
THISHOST=`hostname` | |
INFLUXLINES="" | |
VALUE=`/usr/bin/psql -P pager=off -t -c "select usename, wait_event_type, wait_event, regexp_replace(regexp_replace( replace(query, E'\n', ''), E'[ \t]{2,}', ' ', 'g'), E'^[ ]{1,}', '', 'g') from pg_catalog.pg_stat_activity where state = 'active' and usename != 'replication' and usename != 'postgres'" 2> /dev/null` | |
if [ -z "$VALUE" ] | |
then | |
exit 1 | |
else | |
while IFS= read -r line; do | |
influxline=`echo "$line" | awk -v ntime="$ntime" -v host="$THISHOST" 'BEGIN {FS=OFS="|"} {gsub(/ /, "", $1); print "postgresql,host="host",username="$1" query=\""$4"\"\n"}'` | |
curl --request POST \ | |
"http://{influx-server}:8086/api/v2/write?org={org id}&bucket={bucket name}&precision=ns" \ | |
--header "Authorization: Token {api token}" \ | |
--header "Content-Type: text/plain; charset=utf-8" \ | |
--header "Accept: application/json" \ | |
--data-binary "$influxline" | |
done <<< "$VALUE" | |
fi |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment