SHOW STATUS in Postgres

Helping a friend modify a MySQL module for Smokeping to work on Postgres today led us looking for Postgres’ equivalent of MySQL’s SHOW STATUS; command. It’s not a perfect match for the output of SHOW STATUS, but for the purposes of Smokeping it’ll do as all we’re really checking is whether the DB engine is responsive.

Turns out the linked version is outdated or something, because on his Postgres server (9.4) the pg_stat_activity table looks like this:

postgres=# \d pg_stat_activity
           View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Modifiers
------------------+--------------------------+-----------
 datid            | oid                      |
 datname          | name                     |
 pid              | integer                  |
 usesysid         | oid                      |
 usename          | name                     |
 application_name | text                     |
 client_addr      | inet                     |
 client_hostname  | text                     |
 client_port      | integer                  |
 backend_start    | timestamp with time zone |
 xact_start       | timestamp with time zone |
 query_start      | timestamp with time zone |
 state_change     | timestamp with time zone |
 waiting          | boolean                  |
 state            | text                     |
 query            | text                     |

So the updated query is:

SELECT datname,pid,query FROM pg_stat_activity ORDER BY pid;

… which seems to work. Boy is my Postgres rusty!

Horsham, VIC, Australia

Author:

fwaggle

Published:


Modified:


Filed under:


Location:

Horsham, VIC, Australia

Navigation: Older Entry Newer Entry