通过psycopg2模块监控postgresql

最近做了关于postgresql数据库的简单监控，脚本如下：

import psycopg2
import sys

sqlItem = {
           'alive': "select 1;",  # 监控存活
           'active_connections': "select count(*) from pg_stat_activity where state = 'active';",  # 当前会话数
           'server_connections': "select count(*) from pg_stat_activity where backend_type = 'client backend'",  # 服务端进程数
           'idle_connections': "select count(*) from pg_stat_activity where state = 'idle'",  # 空闲连接
           'idle_tx_connections': "select count(*) from pg_stat_activity where state = 'idle in transaction'",  # 空闲连接事务
           'locks_waiting': "select count(*) from pg_stat_activity where backend_type = 'client backend' and wait_event_type like '%Lock%'",  # 锁等待
           'server_maxcon': "select setting::int from pg_settings where name = 'max_connections'",  # 服务端最大连接数
           'tx_commited': "select sum(xact_commit) from pg_stat_database",  # 事务提交
           'tx_rollbacked': "select sum(xact_rollback) from pg_stat_database",  # 事务回滚
           'qps': "select sum(calls) s from pg_stat_statements", # qps
           'scan_full_tables': "select sum(tup_returned) from pg_stat_database",
           'scan_index_rows': "select sum(tup_fetched) from pg_stat_database",
           'tup_inserted': "select sum(tup_inserted) from pg_stat_database",
           'tup_updated': "select sum(tup_updated) from pg_stat_database",
           'tup_deleted': "select sum(tup_deleted) from pg_stat_database",
           'deadlocks': "select sum(deadlocks) from pg_stat_database",  # 死锁
           'rep_write_delay': "select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) from pg_stat_replication",  # 主从相关
           'rep_flush_delay': "select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) from pg_stat_replication",  # 主从相关
           'rep_replay_delay': "select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) from pg_stat_replication",  # 主从相关
           'idle_transaction_5': "select count(*) from pg_stat_activity where state='idle in transaction' and now()-state_change > interval '5 second'", # 空闲事务超过5s
           'long_query_5': "select count(*) from pg_stat_activity where state='active' and now()-query_start > interval '5 second'",  # 超过5秒的慢查询
           'long_transaction_5': "select count(*) from pg_stat_activity where now()-xact_start > interval '5 second'",  # 慢事务
           # 'lock_waitting'       : "select count(*) from pg_stat_activity where wait_event_type is not null",
           'long_lock_waiting_5': "select count(*) from pg_stat_activity where wait_event_type is not null and now()-state_change > interval '5 second'"  # 
           }


def getItem(itemKey):
    conn = None
    result = None
    try:

        sqlText = sqlItem[itemKey]
        conn = psycopg2.connect(database="postgres", user="postgres", password="xxxxxxx", host="127.0.0.1",
                                port="5432")

        cur = conn.cursor()
        cur.execute(sqlText)
        rows = cur.fetchall()
        if len(rows) == 1:

            result = rows[0][0]
        else:
            result = None
        cur.close()
        conn.close()
    except Exception as e:
        print(e)

    finally:
        # print(conn.closed)
        if not conn.closed:
            conn.close()
    return result

if __name__ == '__main__':
    print(getItem(sys.argv[1]))