minimal collectd conf for postgres

postgresql-with-replication-collectd.conf

LoadPlugin postgresql

<Plugin postgresql>
  <Query bgwriter>
    Statement "SELECT checkpoints_timed, checkpoints_req, \
        buffers_checkpoint, buffers_clean, maxwritten_clean, \
        buffers_backend, buffers_alloc \
      FROM pg_stat_bgwriter"

    <Result>
      Type "pg_bgwriter"
      InstancePrefix "checkpoints_timed"
      ValuesFrom "checkpoints_timed"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "checkpoints_req"
      ValuesFrom "checkpoints_req"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "buffers_checkpoint"
      ValuesFrom "buffers_checkpoint"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "buffers_clean"
      ValuesFrom "buffers_clean"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "maxwritten_clean"
      ValuesFrom "maxwritten_clean"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "buffers_backend"
      ValuesFrom "buffers_backend"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "buffers_alloc"
      ValuesFrom "buffers_alloc"
    </Result>

    MaxVersion 90199
  </Query>

  <Query bgwriter>
    Statement "SELECT checkpoints_timed, checkpoints_req, \
        checkpoint_write_time, checkpoint_sync_time, \
        buffers_checkpoint, buffers_clean, maxwritten_clean, \
        buffers_backend, buffers_backend_fsync, buffers_alloc \
      FROM pg_stat_bgwriter"

    <Result>
      Type "pg_bgwriter"
      InstancePrefix "checkpoints_timed"
      ValuesFrom "checkpoints_timed"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "checkpoints_req"
      ValuesFrom "checkpoints_req"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "checkpoint_write_time"
      ValuesFrom "checkpoint_write_time"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "checkpoint_sync_time"
      ValuesFrom "checkpoint_sync_time"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "buffers_checkpoint"
      ValuesFrom "buffers_checkpoint"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "buffers_clean"
      ValuesFrom "buffers_clean"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "maxwritten_clean"
      ValuesFrom "maxwritten_clean"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "buffers_backend"
      ValuesFrom "buffers_backend"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "buffers_backend_fsync"
      ValuesFrom "buffers_backend_fsync"
    </Result>
    <Result>
      Type "pg_bgwriter"
      InstancePrefix "buffers_alloc"
      ValuesFrom "buffers_alloc"
    </Result>

    MinVersion 90200
  </Query>

  <Query pg_stat_replication>
    Statement "SELECT \
      pg_xlog_location_diff(pg_current_xlog_location(),sent_location) as send_delay, \
      pg_xlog_location_diff(pg_current_xlog_location(),write_location) as write_delay, \
      pg_xlog_location_diff(pg_current_xlog_location(),flush_location) as flush_delay, \
      pg_xlog_location_diff(pg_current_xlog_location(),replay_location) as replay_delay \
      from pg_stat_replication"

    <Result>
      Type "bytes"
      InstancePrefix "send_delay"
      ValuesFrom "send_delay"
    </Result>

    <Result>
      Type "bytes"
      InstancePrefix "write_delay"
      ValuesFrom "write_delay"
    </Result>

    <Result>
      Type "bytes"
      InstancePrefix "flush_delay"
      ValuesFrom "flush_delay"
    </Result>

    <Result>
      Type "bytes"
      InstancePrefix "replay_delay"
      ValuesFrom "replay_delay"
    </Result>

    MinVersion 90200
  </Query>

  <Query backends_total>
    Statement "SELECT count(*) AS count \
      FROM pg_stat_activity;"

    <Result>
      InstancePrefix "total"
      Type "pg_numbackends"
      ValuesFrom "count"
    </Result>
  </Query>

  <Query backends_total_active>
    Statement "SELECT count(*) AS count \
      FROM pg_stat_activity \
      WHERE current_query != '<IDLE>';"

    <Result>
      InstancePrefix "total_active"
      Type "pg_numbackends"
      ValuesFrom "count"
    </Result>
    MaxVersion 90199
  </Query>

  <Query backends_total_active>
    Statement "SELECT count(*) AS count \
      FROM pg_stat_activity \
      WHERE state <> 'idle';"

    <Result>
      InstancePrefix "total_active"
      Type "pg_numbackends"
      ValuesFrom "count"
    </Result>
    MinVersion 90200
  </Query>

  <Database "database_name">
    User "postgres"
    Query bgwriter
    Query pg_stat_replication
    Query backends
    Query backends_total
    Query backends_total_active
  </Database>
</Plugin>