Collectd+postgres for db-datanommer01

5 files changed, 46 insertions, 1 deletions

diff --git a/inventory/host_vars/db-datanommer01.phx2.fedoraproject.org b/inventory/host_vars/db-datanommer01.phx2.fedoraproject.org
index 0bd76efa6..5f02aeefb 100644
--- a/inventory/host_vars/db-datanommer01.phx2.fedoraproject.org
+++ b/inventory/host_vars/db-datanommer01.phx2.fedoraproject.org
@@ -9,6 +9,11 @@ eth0_ip: 10.5.126.109
 vmhost: virthost14.phx2.fedoraproject.org
 datacenter: phx2
 
+# This is a generic list, monitored by collectd
+databases:
+- datanommer
+
+# This is a more strict list, to be made publicly available
 dbs_to_backup:
 - datanommer
 
diff --git a/playbooks/groups/postgresl-server.yml b/playbooks/groups/postgresl-server.yml
index 9720d6a5b..cf958d315 100644
--- a/playbooks/groups/postgresl-server.yml
+++ b/playbooks/groups/postgresl-server.yml
@@ -38,8 +38,9 @@
   - denyhosts
   - nagios_client
   - fas_client
-  - collectd/base
   - postgresql_server
+  - collectd/base
+  - collectd/postgres  # This requires a 'databases' var to be set in host_vars
 
   tasks:
   - include: "{{ tasks }}/hosts.yml"
diff --git a/roles/collectd/postgres/tasks/main.yml b/roles/collectd/postgres/tasks/main.yml
new file mode 100644
index 000000000..9a6ac2b3b
--- /dev/null
+++ b/roles/collectd/postgres/tasks/main.yml
@@ -0,0 +1,14 @@
+---
+
+- name: Copy in /usr/local/bin/collectd-postgres.sh
+  template: >
+    src=collectd-postgres.sh
+    dest=/usr/local/bin/collectd-postgres.sh
+    mode=0755
+  notify: restart collectd
+
+- name: Copy in /etc/collectd.d/postgres.conf
+  template: >
+    src=postgres.conf
+    dest=/etc/collectd.d/postgres.conf
+  notify: restart collectd
diff --git a/roles/collectd/postgres/templates/collectd-postgres.sh b/roles/collectd/postgres/templates/collectd-postgres.sh
new file mode 100644
index 000000000..8890a8b45
--- /dev/null
+++ b/roles/collectd/postgres/templates/collectd-postgres.sh
@@ -0,0 +1,22 @@
+#!/bin/bash
+
+pause=10
+host=$( hostname -s )
+
+while getopts "h:p:s:" c; do
+       case $c in
+               h)      host=$OPTARG;;
+               p)      pause=$OPTARG;;
+               *)      echo "Usage: /bin/bash [-h <hostname>] [-p <seconds>]";;
+       esac
+done
+
+
+ while [ $? -eq 0 ] ; do
+   time="$(date +%s)"
+   for db in {{ databases.join(' ') }} 
+   do
+      echo "PUTVAL $host/pg_conns/pg_conns-$db $time:$(ps auxwww | grep -c $db)"
+   done
+   sleep $pause
+ done
diff --git a/roles/collectd/postgres/templates/postgres.conf b/roles/collectd/postgres/templates/postgres.conf
new file mode 100644
index 000000000..5d5387726
--- /dev/null
+++ b/roles/collectd/postgres/templates/postgres.conf
@@ -0,0 +1,3 @@
+<Plugin exec>
+    Exec "nobody" "/usr/local/bin/collectd_postgres.sh" "-h" "{{ ansible_hostname }}" "-p" "10"
+</Plugin>