Uploaded image for project: 'Observium'
  1. Observium
  2. OBS-2003

postgresql.pl script does not work for postgresql greate than 9.2

Details

    • Bug
    • Resolution: Unresolved
    • Minor
    • None
    • Community Edition
    • Unix Agent
    • Ubuntu 16.04 LTS, Virtual Machine, Postgresql 9.5 Perl 5.22.1

    Description

      The unix-agent script "postgresql.pl" is not working on postgres 9.2 and above. From Postgresql 9.2 the "current_query" in pg_stat_activity was split to two columns "state" and "query" causing the postgresql.pl script bundled with observium to cast a "Execution failed" error message.
      Source: [https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2#pg_stat_activity_and_pg_stat_replication.27s_definitions_have_changed
      ]
      The workaround would be to make a check for postgres version and have two different queries.

      Best Regards.

      Attachments

        Activity

          [OBS-2003] postgresql.pl script does not work for postgresql greate than 9.2

          Lets just get the correct counts, for idle and the query type:

          # get the stats
          if ($version =~ /^[89]\.\d$/) {
              $cmd="select datname, usename, client_addr, state, query from pg_stat_activity";
          }$all=sqlHashRef($cmd);for (; $all=$query->fetchrow_hashref() ;) {
              # count the total number of connection to the server (right now)
              $cCount++; # increment the connection count
              if ($all->{datname}) {
                  push (@dDbs, $all->{datname});
              }
              if ($all->{usename}) {
                  push (@dUsr, $all->{usename});
              }
              if ($all->{client_addr}) {
                  push (@dHst, $all->{client_addr});
              }    # parse query type. probably useless
              # find idle, select, update, delete, other
              if ($all->{state} =~ /idle/i) {
                  $idle++;
              }
              else {
                  if (lc($all->{query}) =~ /^select/) {
                      $select++;
                  }
                  elsif (lc($all->{query}) =~ /^update/) {
                      $update++;
                  }
                  elsif (lc($all->{query}) =~ /^delete/) {
                      $delete++;
                  }
                  else {
                      $other++;
                  }
              }
          }
          

          This is counting the "idle in transaction" as just idle...

          mklapwijk Marcel Klapwijk added a comment - Lets just get the correct counts, for idle and the query type: # get the stats if ($version =~ /^[ 89 ]\.\d$/) { $cmd= "select datname, usename, client_addr, state, query from pg_stat_activity" ; }$all=sqlHashRef($cmd); for (; $all=$query->fetchrow_hashref() ;) { # count the total number of connection to the server (right now) $cCount++; # increment the connection count if ($all->{datname}) { push ( @dDbs , $all->{datname}); } if ($all->{usename}) { push ( @dUsr , $all->{usename}); } if ($all->{client_addr}) { push ( @dHst , $all->{client_addr}); } # parse query type. probably useless # find idle, select, update, delete, other if ($all->{state} =~ /idle/i) { $idle++; } else { if (lc($all->{query}) =~ /^select/) { $select++; } elsif (lc($all->{query}) =~ /^update/) { $update++; } elsif (lc($all->{query}) =~ /^delete/) { $delete++; } else { $other++; } } } This is counting the "idle in transaction" as just idle...
          tromp Tromp Wezelman added a comment - - edited

          Change the line:

          $cmd="select datname, usename, client_addr, current_query from pg_stat_activity";
          

          to:

          $cmd="select datname, usename, client_addr, state from pg_stat_activity"
          

          tromp Tromp Wezelman added a comment - - edited Change the line: $cmd="select datname, usename, client_addr, current_query from pg_stat_activity"; to: $cmd="select datname, usename, client_addr, state from pg_stat_activity"

          current_query supplies idle, select, update, delete, other counters. Is there an equivalent on the newer postgres?

          sid3windr Tom Laermans added a comment - current_query supplies idle, select, update, delete, other counters. Is there an equivalent on the newer postgres?

          Can confirm this is the also issue with Debian 9.4 with PostGreSQL 9.4. The fix/workaround from Michael Speth did also work in my case.

          Line 89 in postgresql.pl:

          $cmd="select datname, usename, client_addr from pg_stat_activity";
          

          Schoemaker Quentin Schoemaker added a comment - Can confirm this is the also issue with Debian 9.4 with PostGreSQL 9.4. The fix/workaround from Michael Speth did also work in my case. Line 89 in postgresql.pl: $cmd= "select datname, usename, client_addr from pg_stat_activity" ;

          I also have this problem with Postgresql 9.6.  The workaround I used was to remove current_query from the $cmd.  See below.

          $cmd="select datname, usename, client_addr from pg_stat_activity";

          I don't know what current_query is but it doesn't exist in postgresql 9.6.

          mspeth Michael Speth added a comment - I also have this problem with Postgresql 9.6.  The workaround I used was to remove current_query from the $cmd.  See below. $cmd= "select datname, usename, client_addr from pg_stat_activity" ; I don't know what current_query is but it doesn't exist in postgresql 9.6.
          kuhuda ahuda kuhuda added a comment - - edited

          Hello, same problem.

          Workaround:
          Deleting "pg_stat_activity" from this query:

          if ($version =~ /^[89]\.\d$/) {
          $cmd="select datname, usename, client_addr, current_query from pg_stat_activity";

          in postgresql.pl . Afterthat, i can again see Postgresql in Observium between Apps.

          kuhuda ahuda kuhuda added a comment - - edited Hello, same problem. Workaround: Deleting "pg_stat_activity" from this query: if ($version =~ /^ [89] \.\d$/) { $cmd="select datname, usename, client_addr, current_query from pg_stat_activity"; in postgresql.pl . Afterthat, i can again see Postgresql in Observium between Apps.

          People

            sid3windr Tom Laermans
            mbits Mads Boye
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated: