collectd.conf

# this configuration file was originally provided to me by Lloyd Albin
# from Fred Hutch. he runs the SEAPUG Seattle PostgreSQL Users Group.
# thanks Lloyd!

LoadPlugin postgresql
<Plugin postgresql>
    <Query connections>
        Statement "SELECT count(*) AS connections FROM pg_stat_activity;"
        <Result>
            Type pg_conns
            ValuesFrom connections
        </Result>
    </Query>

    <Query connection_states>
        Statement "
            SELECT a.state, TRUE AS waiting, COALESCE(b.connections,0) AS connections FROM (
                SELECT 'active' AS state
                UNION ALL
                SELECT 'idle'
                UNION ALL
                SELECT 'idle in transaction'
                UNION ALL
                SELECT 'idle in transaction (aborted)'
                UNION ALL
                SELECT 'fastpath'
                UNION ALL
                SELECT 'disabled'
            ) a
            LEFT JOIN (
                SELECT state, COALESCE(wait_event_type = 'Lock', FALSE) AS waiting, count(*) as connections
                FROM pg_stat_activity
                GROUP BY pg_stat_activity.state, pg_stat_activity.wait_event_type
            ) b ON a.state = b.state AND b.waiting = TRUE
            UNION ALL
            SELECT a.state, FALSE AS waiting, COALESCE(b.connections,0) FROM (
                SELECT 'active' AS state
                UNION ALL
                SELECT 'idle'
                UNION ALL
                SELECT 'idle in transaction'
                UNION ALL
                SELECT 'idle in transaction (aborted)'
                UNION ALL
                SELECT 'fastpath'
                UNION ALL
                SELECT 'disabled'
            ) a
            LEFT JOIN (
                SELECT state, COALESCE(wait_event_type = 'Lock', FALSE) AS waiting, count(*) as connections
                FROM pg_stat_activity
                GROUP BY pg_stat_activity.state, pg_stat_activity.wait_event_type
            ) b ON a.state = b.state AND b.waiting = FALSE;
        "
        <Result>
            Type pg_connection_state
            InstancesFrom "state" "waiting"
            ValuesFrom connections
        </Result>
    </Query>

    <Query concurrent_txns>
        Statement "
            SELECT a.datname, FALSE AS waiting, COALESCE(b.txns,0) AS txns
            FROM pg_database a
            LEFT JOIN (
                SELECT datname, COALESCE(wait_event_type = 'Lock', FALSE) AS waiting, count(*) AS txns
                FROM pg_stat_activity
                WHERE state != 'idle'
                GROUP BY datname, wait_event_type
            ) b ON a.datname = b.datname AND b.waiting = FALSE
            WHERE a.datname NOT LIKE 'test%'
            UNION ALL
            SELECT a.datname, TRUE AS waiting, COALESCE(b.txns,0)
            FROM pg_database a
            LEFT JOIN (
                SELECT datname, COALESCE(wait_event_type = 'Lock', FALSE) AS waiting, count(*) AS txns
                FROM pg_stat_activity
                WHERE state != 'idle'
                GROUP BY datname, wait_event_type
            ) b ON a.datname = b.datname AND b.waiting = TRUE
            WHERE a.datname NOT LIKE 'test%';
        "
        <Result>
            Type pg_concurrent_txns
            InstancesFrom "datname" "waiting"
            ValuesFrom txns
        </Result>
    </Query>

    <Query connection_state>
        Statement "
            SELECT
                a.count as locked,
                b.count as active,
                c.count as idle,
                d.count as idle_transaction,
                e.count AS idle_trans_abort,
                f.count AS fastpath,
                g.count AS disabled
            FROM (
                SELECT count(*)
                FROM pg_stat_activity
                WHERE COALESCE(wait_event_type = 'Lock', FALSE)
            ) AS a, (
                SELECT count(*)
                FROM pg_stat_activity
                WHERE state = 'active' AND NOT COALESCE(wait_event_type = 'Lock', FALSE)
            ) AS b, (
                SELECT count(*)
                FROM pg_stat_activity
                WHERE state = 'idle' AND NOT COALESCE(wait_event_type = 'Lock', FALSE)
            ) AS c, (
                SELECT count(*)
                FROM pg_stat_activity
                WHERE state = 'idle in transaction' AND NOT COALESCE(wait_event_type = 'Lock', FALSE)
            ) AS d, (
                SELECT count(*)
                FROM pg_stat_activity
                WHERE state = 'idle in transaction (aborted)' AND NOT COALESCE(wait_event_type = 'Lock', FALSE)
            ) AS e, (
                SELECT count(*)
                FROM pg_stat_activity
                WHERE state = 'fastpath' AND NOT COALESCE(wait_event_type = 'Lock', FALSE)
            ) AS f, (
                SELECT count(*)
                FROM pg_stat_activity
                WHERE state = 'disabled' AND NOT COALESCE(wait_event_type = 'Lock', FALSE)
            ) AS g;
        "
        <Result>
            Type pg_connections
            ValuesFrom locked active idle idle_transaction idle_trans_abort fastpath disabled
        </Result>
    </Query>

    <Query connection_state_by_database>
        Statement "
            SELECT
                f.datname,
                COALESCE(a.count,0) as locked,
                COALESCE(b.count,0) as active,
                COALESCE(c.count,0) as idle,
                COALESCE(d.count,0) as idle_transaction,
                COALESCE(e.count,0) AS idle_trans_abort,
                COALESCE(g.count,0) AS fastpath,
                COALESCE(h.count,0) AS disabled
            FROM (
                SELECT datname
                FROM pg_stat_activity
                WHERE datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS f
            LEFT JOIN (
                SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS a ON f.datname = a.datname
            LEFT JOIN (
                SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE state = 'active' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS b ON f.datname = b.datname
            LEFT JOIN (
                SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE state = 'idle' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS c ON f.datname = c.datname
            LEFT JOIN (
                SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE state = 'idle in transaction' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS d ON f.datname = d.datname
            LEFT JOIN (
                SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE state = 'idle in transaction (aborted)' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS e ON f.datname = e.datname
            LEFT JOIN (
                SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE state = 'fastpath' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS g ON f.datname = g.datname
            LEFT JOIN (
                SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE state = 'disabled' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS h ON f.datname = h.datname;
        "
        <Result>
            Type pg_connections
            InstancesFrom "datname"
            ValuesFrom locked active idle idle_transaction idle_trans_abort fastpath disabled
        </Result>
    </Query>

    <Query database_size>
        Statement "
            SELECT pg_database.datname AS database, pg_database_size(pg_database.oid) AS size
            FROM pg_database
            WHERE datname NOT LIKE 'test%'
            ORDER BY pg_database.datname;
        "
        <Result>
            Type pg_databases
            InstancesFrom "database"
            ValuesFrom size
        </Result>
    </Query>

    <Query database_transactions>
        Statement "
            SELECT datname AS database, txns
            FROM (
                SELECT foo.freez::int, age(datfrozenxid) AS txns, datname
                FROM pg_database d
                JOIN (
                    SELECT setting AS freez
                    FROM pg_settings
                    WHERE name = 'autovacuum_freeze_max_age'
                ) AS foo ON (true)
                WHERE d.datallowconn AND d.datname NOT LIKE 'test%'
            ) AS foo2;
        "
        <Result>
            Type pg_txns
            InstancesFrom "database"
            ValuesFrom txns
        </Result>
    </Query>

    <Query transactions>
        Statement "
            SELECT sum(xact_commit + xact_rollback)
            FROM pg_stat_database;
        "
        <Result>
            Type pg_db_txns
            ValuesFrom sum
        </Result>
    </Query>

    <Query buffercache>
        Statement "
            SELECT
                CASE pg_buffercache.usagecount
                    WHEN 0 THEN 'Just Read - Least Used'
                    WHEN 1 THEN 'Low Usage'
                    WHEN 2 THEN 'Medium Low Usage'
                    WHEN 3 THEN 'Medium High Usage'
                    WHEN 4 THEN 'High Usage'
                    WHEN 5 THEN 'Heavily Used'
                    ELSE 'Unused'
                END AS usage,
                CASE pg_buffercache.isdirty
                    WHEN TRUE THEN 'WAL Only'
                    ELSE 'Normal'
                END AS written,
                count(*) * ((
                    SELECT pg_settings.setting::bigint AS setting
                    FROM pg_settings
                    WHERE pg_settings.name = 'block_size'::text
                )) AS size
            FROM pg_buffercache
            GROUP BY pg_buffercache.usagecount, pg_buffercache.isdirty
            ORDER BY pg_buffercache.isdirty, pg_buffercache.usagecount;
        "
        <Result>
            Type pg_buffercache
            InstancesFrom "usage", "written"
            ValuesFrom size
        </Result>
    </Query>

    <Query buffercache_databases>
        Statement "
            SELECT datname, COALESCE(count(*) * ((
                    SELECT pg_settings.setting::bigint AS setting
                    FROM pg_settings
                    WHERE pg_settings.name = 'block_size'::text
            ))) AS shared_buffers
            FROM pg_database a
            LEFT JOIN pg_buffercache b ON a.oid = b.reldatabase
            WHERE datname NOT LIKE 'test%'
            GROUP BY datname;
        "
        <Result>
            Type pg_buffercache_databases
            InstancesFrom "datname"
            ValuesFrom shared_buffers
        </Result>
    </Query>

    <Query database_commit_ratio_by_database>
        Statement "
            SELECT round(100.*sd.xact_commit/(sd.xact_commit+sd.xact_rollback), 2) AS dcommitratio, d.datname, u.usename
            FROM pg_stat_database sd
            JOIN pg_database d ON (d.oid=sd.datid)
            JOIN pg_user u ON (u.usesysid=d.datdba)
            WHERE sd.xact_commit+sd.xact_rollback<>0 AND d.datname NOT LIKE 'test%';
        "
        <Result>
            Type pg_commit_ratio
            InstancesFrom "datname"
            ValuesFrom dcommitratio
        </Result>
    </Query>

    <Query database_commit_ratio>
        Statement "
            SELECT round(100.*sd.xact_commit/(sd.xact_commit+sd.xact_rollback), 2) AS dcommitratio, d.datname, u.usename
            FROM pg_stat_database sd
            JOIN pg_database d ON (d.oid=sd.datid)
            JOIN pg_user u ON (u.usesysid=d.datdba)
            WHERE sd.xact_commit+sd.xact_rollback<>0 AND d.datname = $1;
        "
        Param database
        <Result>
            Type pg_commit_ratio
            InstancesFrom "datname"
            ValuesFrom dcommitratio
        </Result>
    </Query>

    <Query database_stats>
        Statement "
            SELECT
                datname,
                numbackends AS backends,
                xact_commit AS commits,
                xact_rollback AS rollbacks,
                blks_read * ((
                    SELECT pg_settings.setting::bigint AS setting
                    FROM pg_settings
                    WHERE pg_settings.name = 'block_size'::text
                )) AS read,
                blks_hit * ((
                    SELECT pg_settings.setting::bigint AS setting
                    FROM pg_settings
                    WHERE pg_settings.name = 'block_size'::text
                )) AS hit,
                COALESCE((SELECT SUM(idx_scan) FROM pg_stat_user_indexes),0) AS idxscan,
                COALESCE((SELECT SUM(idx_tup_read) FROM pg_stat_user_indexes),0) AS idxtupread,
                COALESCE((SELECT SUM(idx_tup_fetch) FROM pg_stat_user_indexes),0) AS idxtupfetch,
                COALESCE((SELECT SUM(idx_blks_read) * ((
                    SELECT pg_settings.setting::bigint AS setting
                    FROM pg_settings
                    WHERE pg_settings.name = 'block_size'::text
                )) FROM pg_statio_user_indexes),0) AS idxblksread,
                COALESCE((SELECT SUM(idx_blks_hit) * ((
                    SELECT pg_settings.setting::bigint AS setting
                    FROM pg_settings
                    WHERE pg_settings.name = 'block_size'::text
                )) FROM pg_statio_user_indexes),0) AS idxblkshit,
                COALESCE((SELECT SUM(seq_scan) FROM pg_stat_user_tables),0) AS seqscan,
                COALESCE((SELECT SUM(seq_tup_read) FROM pg_stat_user_tables),0) AS seqtupread,
                tup_returned AS ret,
                tup_fetched AS fetch,
                tup_inserted AS ins,
                tup_updated AS upd,
                tup_deleted AS del,
                deadlocks,
                blk_read_time,
                blk_write_time
            FROM pg_stat_database
            WHERE datname = $1;
        "
        Param database
        <Result>
            Type pg_bkends
            InstancesFrom "datname"
            ValuesFrom backends
        </Result>
        <Result>
            Type pg_xact_commit
            InstancesFrom "datname"
            ValuesFrom commits
        </Result>
        <Result>
            Type pg_xact_rollback
            InstancesFrom "datname"
            ValuesFrom rollbacks
        </Result>
        <Result>
            Type pg_blks_read
            InstancesFrom "datname"
            ValuesFrom read
        </Result>
        <Result>
            Type pg_blks_hit
            InstancesFrom "datname"
            ValuesFrom hit
        </Result>
        <Result>
            Type pg_idx_scan
            InstancesFrom "datname"
            ValuesFrom idxscan
        </Result>
        <Result>
            Type pg_idx_tup_read
            InstancesFrom "datname"
            ValuesFrom idxtupread
        </Result>
        <Result>
            Type pg_idx_tup_fetch
            InstancesFrom "datname"
            ValuesFrom idxtupfetch
        </Result>
        <Result>
            Type pg_idx_blks_read
            InstancesFrom "datname"
            ValuesFrom idxblksread
        </Result>
        <Result>
            Type pg_idx_blks_hit
            InstancesFrom "datname"
            ValuesFrom idxblkshit
        </Result>
        <Result>
            Type pg_seq_scan
            InstancesFrom "datname"
            ValuesFrom seqscan
        </Result>
        <Result>
            Type pg_seq_tup_read
            InstancesFrom "datname"
            ValuesFrom seqtupread
        </Result>
        <Result>
            Type pg_tup_returned
            InstancesFrom "datname"
            ValuesFrom ret
        </Result>
        <Result>
            Type pg_tup_fetched
            InstancesFrom "datname"
            ValuesFrom fetch
        </Result>
        <Result>
            Type pg_tup_inserted
            InstancesFrom "datname"
            ValuesFrom ins
        </Result>
        <Result>
            Type pg_tup_updated
            InstancesFrom "datname"
            ValuesFrom upd
        </Result>
        <Result>
            Type pg_tup_deleted
            InstancesFrom "datname"
            ValuesFrom del
        </Result>
        <Result>
            Type pg_deadlocks
            InstancesFrom "datname"
            ValuesFrom deadlocks
        </Result>
        <Result>
            # if track_io_timing is enabled, otherwise zero
            Type pg_blk_read_time
            InstancesFrom "datname"
            ValuesFrom blk_read_time
        </Result>
        <Result>
            # if track_io_timing is enabled, otherwise zero
            Type pg_blk_write_time
            InstancesFrom "datname"
            ValuesFrom blk_write_time
        </Result>
    </Query>

    <Query database_stats_by_database>
        Statement "
            SELECT
                datname,
                numbackends AS backends,
                xact_commit AS commits,
                xact_rollback AS rollbacks,
                blks_read * ((
                    SELECT pg_settings.setting::bigint AS setting
                    FROM pg_settings
                    WHERE pg_settings.name = 'block_size'::text
                )) AS read,
                blks_hit * ((
                    SELECT pg_settings.setting::bigint AS setting
                    FROM pg_settings
                    WHERE pg_settings.name = 'block_size'::text
                )) AS hit,
                COALESCE((SELECT SUM(idx_scan) FROM pg_stat_user_indexes),0) AS idxscan,
                COALESCE((SELECT SUM(idx_tup_read) FROM pg_stat_user_indexes),0) AS idxtupread,
                COALESCE((SELECT SUM(idx_tup_fetch) FROM pg_stat_user_indexes),0) AS idxtupfetch,
                COALESCE((SELECT SUM(idx_blks_read) * ((
                    SELECT pg_settings.setting::bigint AS setting
                    FROM pg_settings
                    WHERE pg_settings.name = 'block_size'::text
                )) FROM pg_statio_user_indexes),0) AS idxblksread,
                COALESCE((SELECT SUM(idx_blks_hit) * ((
                    SELECT pg_settings.setting::bigint AS setting
                    FROM pg_settings
                    WHERE pg_settings.name = 'block_size'::text
                )) FROM pg_statio_user_indexes),0) AS idxblkshit,
                COALESCE((SELECT SUM(seq_scan) FROM pg_stat_user_tables),0) AS seqscan,
                COALESCE((SELECT SUM(seq_tup_read) FROM pg_stat_user_tables),0) AS seqtupread,
                tup_returned AS ret,
                tup_fetched AS fetch,
                tup_inserted AS ins,
                tup_updated AS upd,
                tup_deleted AS del,
                deadlocks,
                blk_read_time,
                blk_write_time
            FROM pg_stat_database
            WHERE datname NOT LIKE 'test%';
        "
        <Result>
            Type pg_bkends
            InstancesFrom "datname"
            ValuesFrom backends
        </Result>
        <Result>
            Type pg_xact_commit
            InstancesFrom "datname"
            ValuesFrom commits
        </Result>
        <Result>
            Type pg_xact_rollback
            InstancesFrom "datname"
            ValuesFrom rollbacks
        </Result>
        <Result>
            Type pg_blks_read
            InstancesFrom "datname"
            ValuesFrom read
        </Result>
        <Result>
            Type pg_blks_hit
            InstancesFrom "datname"
            ValuesFrom hit
        </Result>
        <Result>
            Type pg_idx_scan
            InstancesFrom "datname"
            ValuesFrom idxscan
        </Result>
        <Result>
            Type pg_idx_tup_read
            InstancesFrom "datname"
            ValuesFrom idxtupread
        </Result>
        <Result>
            Type pg_idx_tup_fetch
            InstancesFrom "datname"
            ValuesFrom idxtupfetch
        </Result>
        <Result>
            Type pg_idx_blks_read
            InstancesFrom "datname"
            ValuesFrom idxblksread
        </Result>
        <Result>
            Type pg_idx_blks_hit
            InstancesFrom "datname"
            ValuesFrom idxblkshit
        </Result>
        <Result>
            Type pg_seq_scan
            InstancesFrom "datname"
            ValuesFrom seqscan
        </Result>
        <Result>
            Type pg_seq_tup_read
            InstancesFrom "datname"
            ValuesFrom seqtupread
        </Result>
        <Result>
            Type pg_tup_returned
            InstancesFrom "datname"
            ValuesFrom ret
        </Result>
        <Result>
            Type pg_tup_fetched
            InstancesFrom "datname"
            ValuesFrom fetch
        </Result>
        <Result>
            Type pg_tup_inserted
            InstancesFrom "datname"
            ValuesFrom ins
        </Result>
        <Result>
            Type pg_tup_updated
            InstancesFrom "datname"
            ValuesFrom upd
        </Result>
        <Result>
            Type pg_tup_deleted
            InstancesFrom "datname"
            ValuesFrom del
        </Result>
        <Result>
            Type pg_deadlocks
            InstancesFrom "datname"
            ValuesFrom deadlocks
        </Result>
        <Result>
            # if track_io_timing is enabled, otherwise zero
            Type pg_blk_read_time
            InstancesFrom "datname"
            ValuesFrom blk_read_time
        </Result>
        <Result>
            # if track_io_timing is enabled, otherwise zero
            Type pg_blk_write_time
            InstancesFrom "datname"
            ValuesFrom blk_write_time
        </Result>
    </Query>

    <Query pg_xlog>
        Statement "
            SELECT count(*) AS xlog_count
            FROM pg_ls_dir('pg_wal')
            WHERE pg_ls_dir ~ E'^[0-9A-F]{24}\$';
        "
        <Result>
            Type pg_xlog
            ValuesFrom xlog_count
        </Result>
    </Query>

    <Query pg_trig_disabled>
        Statement "
            SELECT count(*) AS pg_trig_disabled
            FROM pg_trigger
            WHERE tgenabled = 'D';
        "
        <Result>
            Type pg_trig_disabled
            ValuesFrom pg_trig_disabled
        </Result>
    </Query>

    <Query pg_hit_ratio>
        Statement "
            SELECT
                round(100.*sd.blks_hit/(sd.blks_read+sd.blks_hit), 2) AS dhitratio,
                d.datname
            FROM pg_stat_database sd
            JOIN pg_database d ON (d.oid=sd.datid)
            WHERE sd.blks_read+sd.blks_hit<>0 AND d.datname NOT LIKE 'test%';
        "
        <Result>
            Type pg_hit_ratio
            InstancesFrom "datname"
            ValuesFrom dhitratio
        </Result>
    </Query>

    <Query pg_conflicts>
        # only contains data for slaves because masters don't have conflicts
        Statement "
            SELECT datname, confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock
            FROM pg_stat_database_conflicts
            WHERE datname NOT LIKE 'test%';
        "
        <Result>
            Type pg_conflicts
            InstancesFrom "datname"
            ValuesFrom confl_tablespace confl_lock confl_snapshot confl_bufferpin confl_deadlock
        </Result>
    </Query>

    <Query a_server_location>
        # For A Servers Only
        Statement "
            SELECT
                CASE
                    WHEN pg_is_in_recovery() THEN 0
                    ELSE (
                        SELECT
                            ('x00000000FF000000'::bit(64)::bigint *
                            ('x' || lpad(split_part(pg_current_wal_lsn::text, '/', 1),16,'0'))::bit(64)::bigint) +
                            ('x' || lpad(split_part(pg_current_wal_lsn::text, '/', 2),16,'0'))::bit(64)::bigint AS location
                        FROM pg_current_wal_lsn()
                    )
            END AS location;
        "
        <Result>
            Type pg_a_location
            ValuesFrom location
        </Result>
    </Query>

    <Query b_server_location>
        # For B Servers Only
        Statement "
            SELECT
            COALESCE(('x00000000FF000000'::bit(64)::bigint *
            ('x' || lpad(split_part(pg_last_wal_receive_lsn::text, '/', 1),16,'0'))::bit(64)::bigint) +
            ('x' || lpad(split_part(pg_last_wal_receive_lsn::text, '/', 2),16,'0'))::bit(64)::bigint,0) AS receive,
            COALESCE(('x00000000FF000000'::bit(64)::bigint *
            ('x' || lpad(split_part(pg_last_wal_replay_lsn::text, '/', 1),16,'0'))::bit(64)::bigint) +
            ('x' || lpad(split_part(pg_last_wal_replay_lsn::text, '/', 2),16,'0'))::bit(64)::bigint,0) AS replay
            FROM pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
        "
        <Result>
            Type pg_b_location
            ValuesFrom receive replay
        </Result>
    </Query>

    <Query query_length>
        Statement "
            SELECT a.datname, COALESCE(b.query_length,0) AS seconds, COALESCE(ROUND(b.query_length)/60,0) AS minutes, COALESCE(ROUND(b.query_length)/60/60,0) AS hours
            FROM pg_database a
            LEFT JOIN (
            SELECT datname, EXTRACT(EPOCH FROM max(clock_timestamp()-query_start)) AS query_length
                FROM pg_stat_activity
                WHERE state = 'active' AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) b ON a.datname = b.datname
            WHERE a.datname NOT LIKE 'test%';
        "
        <Result>
            Type pg_query_length
            InstancesFrom "datname"
            ValuesFrom seconds
        </Result>
        <Result>
            Type pg_query_length_min
            InstancesFrom "datname"
            ValuesFrom minutes
        </Result>
        <Result>
            Type pg_query_length_hour
            InstancesFrom "datname"
            ValuesFrom hours
        </Result>
    </Query>

    <Query query_length_server>
        Statement "
            SELECT COALESCE(b.query_length,0) AS seconds, COALESCE(ROUND(b.query_length)/60,0) AS minutes, COALESCE(ROUND(b.query_length)/60/60,0) AS hours
            FROM (
            SELECT EXTRACT(EPOCH FROM max(clock_timestamp()-query_start)) AS query_length
                FROM pg_stat_activity
                WHERE state = 'active' AND datname NOT LIKE 'test%'
            ) b;
        "
        <Result>
            Type pg_query_length
            ValuesFrom seconds
        </Result>
        <Result>
            Type pg_query_length_min
            ValuesFrom minutes
        </Result>
        <Result>
            Type pg_query_length_hour
            ValuesFrom hours
        </Result>
    </Query>

    <Query wait_length>
        Statement "
            SELECT a.datname, COALESCE(b.query_length,0) AS seconds
            FROM pg_database a
            LEFT JOIN (
                SELECT datname, EXTRACT(EPOCH FROM max(clock_timestamp()-query_start)) AS query_length
                FROM pg_stat_activity
                WHERE COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' GROUP BY datname
            ) b ON a.datname = b.datname
            WHERE a.datname NOT LIKE 'test%';
        "
        <Result>
            Type pg_wait_length
            InstancesFrom "datname"
            ValuesFrom seconds
        </Result>
    </Query>

    <Query wait_length_server>
        Statement "
            SELECT COALESCE(EXTRACT(EPOCH FROM max(clock_timestamp()-query_start)),0) AS seconds
            FROM pg_stat_activity
            WHERE COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%';
        "
        <Result>
            Type pg_wait_length
            ValuesFrom seconds
        </Result>
    </Query>

    <Query transaction_length>
        Statement "
            SELECT a.datname, COALESCE(b.xact_length,0) AS seconds
            FROM pg_database a
            LEFT JOIN (
                SELECT datname, EXTRACT(EPOCH FROM max(clock_timestamp()-xact_start)) AS xact_length
                FROM pg_stat_activity
                WHERE xact_start IS NOT NULL AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) b ON a.datname = b.datname
            WHERE a.datname NOT LIKE 'test%';
        "
        <Result>
            Type pg_xact_length
            InstancesFrom "datname"
            ValuesFrom seconds
        </Result>
    </Query>

     <Query transaction_length_server>
         Statement "
             SELECT COALESCE(EXTRACT(EPOCH FROM max(clock_timestamp()-xact_start)),0) AS seconds
             FROM pg_stat_activity
             WHERE xact_start IS NOT NULL AND datname NOT LIKE 'test%';
         "
         <Result>
             Type pg_xact_length
             ValuesFrom seconds
         </Result>
    </Query>

    <Query query_database_xxx>
        Statement "
            SELECT
                pg_database.datname AS database,
            pg_database_size(pg_database.oid) AS size,
                COALESCE(a.count,0) AS locked,
                COALESCE(b.count,0) AS active,
                COALESCE(c.count,0) AS idle,
                COALESCE(d.count,0) AS idle_transaction,
                COALESCE(e.count,0) AS fastpath,
                COALESCE(f.count,0) AS disabled,
                pg_stat_database.numbackends AS backends,
                pg_stat_database.xact_commit AS commits,
                pg_stat_database.xact_rollback AS rollbacks,
                pg_stat_database.blks_read,
                pg_stat_database.blks_hit,
                pg_stat_database.tup_returned AS ret,
                pg_stat_database.tup_fetched AS fetch,
                pg_stat_database.tup_inserted AS ins,
            pg_stat_database.tup_updated AS upd,
                pg_stat_database.tup_deleted AS del,
                pg_stat_database.deadlocks,
                pg_stat_database.blk_read_time,
                pg_stat_database.blk_write_time,
                COALESCE(g.query_length,0) AS query_length_seconds,
                COALESCE(ROUND(g.query_length)/60,0) AS query_length_minutes,
                COALESCE(ROUND(g.query_length)/60/60,0) AS query_length_hours,
                COALESCE(h.shared_buffers,0) AS shared_buffers
            FROM pg_database
            LEFT JOIN (
                   SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS a USING (datname)
            LEFT JOIN (
                SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE state = 'active' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS b USING (datname)
            LEFT JOIN (
                SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE state = 'idle' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS c USING (datname)
            LEFT JOIN (
                SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE state = 'idle in transaction' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS d USING (datname)
            LEFT JOIN (
                SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE state = 'fastpath function call' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS e USING (datname)
            LEFT JOIN (
                SELECT datname, count(*)
                FROM pg_stat_activity
                WHERE state = 'disabled' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) AS f USING (datname)
            LEFT JOIN pg_stat_database USING (datname)
            LEFT JOIN (
                SELECT datname, EXTRACT(EPOCH FROM max(clock_timestamp()-query_start)) AS query_length
                FROM pg_stat_activity
                WHERE state = 'active' AND datname NOT LIKE 'test%'
                GROUP BY datname
            ) g USING (datname)
            LEFT JOIN (
                SELECT count(*) * ((
                    SELECT pg_settings.setting::bigint AS setting
                    FROM pg_settings
                    WHERE pg_settings.name = 'block_size'::text
                )) AS shared_buffers,
                CASE
                    WHEN pg_database.datname IS NULL THEN 'Free Space'::name
                    ELSE pg_database.datname
                END AS datname
                FROM pg_buffercache
                LEFT JOIN pg_database ON pg_buffercache.reldatabase = pg_database.oid
                GROUP BY pg_database.datname
            ) h USING (datname)
            WHERE AND datname NOT LIKE 'test%'
            ORDER BY datname;
        "
        <Result>
            Type pg_database_xxx
            InstancesFrom "database"
            ValuesFrom backends shared_buffers deadlocks commits rollbacks
        </Result>
        <Result>
            Type pg_database_conns_xxx
            InstancesFrom "database"
            ValuesFrom size locked active idle idle_transaction fastpath disabled
        </Result>
        <Result>
            Type pg_database_blocks_xxx
            InstancesFrom "database"
            ValuesFrom blks_read blks_hit blk_read_time blk_write_time
        </Result>
        <Result>
            Type pg_database_queries_xxx
            InstancesFrom "database"
            ValuesFrom query_length_seconds query_length_minutes query_length_hours
        </Result>
        <Result>
            Type pg_database_results_xxx
            InstancesFrom "database"
            ValuesFrom ret fetch ins upd del
        </Result>
    </Query>

    <Database postgres>
        Instance "localhost"
        Host "localhost"
        Port "5432"
        User "postgres"
        Query connections
        Query connection_states
        Query connection_state
        Query database_size
        Query buffercache
        Query buffercache_databases
        Query database_transactions
        Query transactions
        Query concurrent_txns
        Query pg_hit_ratio
        Query pg_xlog
        Query connection_state_by_database
        Query database_commit_ratio_by_database
        Query database_stats_by_database
        Query a_server_location
        Query b_server_location
        Query query_length
        Query query_length_server
        Query wait_length
        Query wait_length_server
        Query transaction_length
        Query transaction_length_server
        Query pg_conflicts
        Query query_database_xxx
    </Database>
</Plugin>

# vim: set ft=config :

You will need to add this to your collectd types.db.local file.

pg_connections           locked:gauge:0:U, active:gauge:0:U, idle:gauge:0:U, idle_transaction:gauge:0:U, idle_trans_abort:gauge:0:U, fastpath:gauge:0:U, disabled:gauge:0:U
pg_databases             size:gauge:0:U
pg_buffercache           size:gauge:0:U
pg_txns                  txns:gauge:0:U
pg_buffercache_databases size:gauge:0:U
pg_commit_ratio          dcommitratio:gauge:0:U
pg_bkends                backends:gauge:0:U
pg_xact_commit           commits:counter:0:U
pg_xact_rollback         rollbacks:counter:0:U
pg_blks_read             read:counter:0:U
pg_blks_hit              hit:counter:0:U
pg_idx_scan              idxscan:counter:0:U
pg_idx_tup_read          idxtupread:counter:0:U
pg_idx_tup_fetch         idxtupfetch:counter:0:U
pg_idx_blks_read         idxblksread:counter:0:U
pg_idx_blks_hit          idxblkshit:counter:0:U
pg_seq_scan              seqscan:counter:0:U
pg_seq_tup_read          seqtupread:counter:0:U
pg_tup_returned          ret:counter:0:U
pg_tup_fetched           fetch:counter:0:U
pg_tup_inserted          ins:counter:0:U
pg_tup_updated           upd:counter:0:U
pg_tup_deleted           del:counter:0:U
pg_deadlocks             deadlocks:counter:0:U
pg_blk_read_time         blk_read_time:gauge:0:U
pg_blk_write_time        blk_write_time:gauge:0:U
pg_xlog                  xlog_count:gauge:0:U
pg_trig_disabled         pg_trig_disabled:gauge:0:U
pg_hit_ratio             pg_hit_ratio:gauge:100:U
pg_concurrent_txns       txns:gauge:0:U
pg_connection_state      connections:gauge:0:U
pg_db_txns               txns:counter:0:U
pg_conflicts             tblspace:counter:0:U, lock:counter:0:U, snapshot:counter:0:U, bufferpin:counter:0:U, deadlock:counter:0:U
pg_a_location            location:gauge:0:U
pg_b_location            receive:gauge:0:U, replay:gauge:0:U
pg_conns                 connections:gauge:0:U
pg_query_length          query_length:gauge:0:U
pg_query_length_min      query_length:gauge:0:U
pg_query_length_hour     query_length:gauge:0:U
pg_wait_length           wait_length:gauge:0:U
pg_xact_length           xact_length:gauge:0:U
pg_database_xxx          backends:gauge:0:U, shared_buffers:gauge:0:U, deadlocks:gauge:0:U, commits:gauge:0:U, rollbacks:gauge:0:U
pg_database_conns_xxx    size:gauge:0:U, locked:gauge:0:U, active:gauge:0:U, idle:gauge:0:U, idle_transaction:gauge:0:U, fastpath:gauge:0:U, disabled:gauge:0:U
pg_database_blocks_xxx   blks_read:gauge:0:U, blks_hit:gauge:0:U, blk_read_time:gauge:0:U, blk_write_time:gauge:0:U
pg_database_queries_xxx  query_length_seconds:gauge:0:U, query_length_minutes:gauge:0:U, query_length_hours:gauge:0:U
pg_database_results_xxx  ret:gauge:0:U, fetch:gauge:0:U, ins:gauge:0:U, upd:gauge:0:U, del:gauge:0:U