Postgres: Status der Queries abfragen

Als Root folgende Zeile ausführen:

su postgres -c "psql -c \"select pid,substring(query,0,120),now() - query_start as elapsed from pg_stat_activity where query!='' and query!='DISCARD ALL' ORDER BY elapsed ASC;\""

oder:

psql -U postgres -c "SELECT pid,substring(query,0,120),now() - query_start AS elapsed FROM pg_stat_activity WHERE query!='' AND query!='DISCARD ALL' ORDER BY elapsed ASC;"

Probleme kann es bei der Peer Authentication von Postgres geben (Debian ist anders konfiguriert als Ubuntu).

Generell umgegehen kann an das Problem mit einem PHP-Script, dass die Long-Runner-Queries ermittelt und dann per Mail versendet.

< ?php
/**
 * Find out long running queries and mail them
 *
 */

$maxHours = 4;

$reportMail = "ht@whatever.net";

$dbs = array(
	'pgsql:host=your-db-server-here;port=5432;dbname=postgres;user=postgres;password=',
	'pgsql:host=another-db-server;port=5432;dbname=postgres;user=postgres;password='
);

foreach($dbs as $dsn) {
	preg_match("/host=([^\;]*)\;/", $dsn, $matches);
	$host = array_key_exists(1, $matches)? $matches[1] : 'UNKNOWN';
	try {
		$conn = new PDO($dsn);
		if ($conn) {
			$sql = "SELECT pid,datname,client_addr,substring(query,0,250),(now()-query_start) AS elapsed, 
                              (extract(EPOCH FROM (now()-query_start))/3600)::INTEGER AS hours, state, waiting 
                               FROM pg_stat_activity 
                               WHERE query!='' AND query!='DISCARD ALL' 
                               ORDER BY elapsed ASC;";
			$stmt = $conn->query($sql);
			if ($stmt) {
				$msgs = array();
				while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
					if ($row['state']!='idle') {
						continue;
					}
					if ($row['hours'] > $maxHours) {
						$msgs[] = print_r($row, true);
					}
				}
			}
			if (!empty($msgs)) {
				$msg = implode("\n", $msgs);
				mail($reportMail, "LONG-RUNNING-QUERIES: $host", $msg);
			}
		}
	}
	catch (PDOException $e) {
		echo $e->getMessage();
	}
}

Kann zB mit Cronjobs eingesetzt werden, um Queries zu finden, die eine zu lange Laufzeit haben.
File speichern in /root/find-long-running-queries.php

Eintrag in /etc/crontab:

05 5 * * * root php -f /root/find-long-running-queries.php