+# queries run once per cluster (via the 'postgres' database)
+
+- name: "archive_ready"
+ help: "number of WAL files waiting to be archived"
+ scope: cluster
+ max_version: 9.6
+ values:
+ - "archive_ready"
+ query: SELECT COUNT(*) AS archive_ready FROM pg_ls_dir('pg_xlog/archive_status') WHERE pg_ls_dir ~ '^[0-9a-fA-F]{24}\.ready$'
+
+- name: "archive_ready"
+ help: "number of WAL files waiting to be archived"
+ scope: cluster
+ min_version: 10
+ values:
+ - "archive_ready"
+ query: SELECT COUNT(*) AS archive_ready FROM pg_ls_dir('pg_wal/archive_status') WHERE pg_ls_dir ~ '^[0-9a-fA-F]{24}\.ready$'
+
+- name: "pg_stat_archiver"
+ help: "archiver statistics"
+ scope: cluster
+ min_version: 9.4
+ values:
+ - "archived_count"
+ - "last_archived_time"
+ - "failed_count"
+ - "last_failed_time"
+ - "stats_reset"
+ query: SELECT archived_count, failed_count FROM pg_stat_archiver
+
+- name: "pg_stat_replication"
+ help: "replication statistics"
+ scope: cluster
+ min_version: 9.2
+ max_version: 9.6
+ labels:
+ - "application_name"
+ values:
+ - "send_lag_bytes"
+ - "flush_lag_bytes"
+ - "replay_lag_bytes"
+ query: >-
+ SELECT
+ COALESCE(application_name, '')::text AS application_name,
+ COALESCE(pg_xlog_location_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_receive_location() ELSE pg_current_xlog_location() END, sent_location), 0) AS send_lag_bytes,
+ COALESCE(pg_xlog_location_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_receive_location() ELSE pg_current_xlog_location() END, flush_location), 0) AS flush_lag_bytes,
+ COALESCE(pg_xlog_location_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_receive_location() ELSE pg_current_xlog_location() END, replay_location), 0) AS replay_lag_bytes
+ FROM pg_stat_replication FULL JOIN (VALUES(0)) filler(i) ON TRUE
+
+- name: "pg_stat_replication"
+ help: "replication statistics"
+ scope: cluster
+ min_version: 10
+ labels:
+ - "application_name"
+ values:
+ - "send_lag_bytes"
+ - "flush_lag_bytes"
+ - "replay_lag_bytes"
+ query: >-
+ SELECT
+ COALESCE(application_name, '')::text AS application_name,
+ COALESCE(pg_wal_lsn_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_wal_receive_lsn() ELSE pg_current_wal_lsn() END, sent_lsn), 0) AS send_lag_bytes,
+ COALESCE(pg_wal_lsn_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_wal_receive_lsn() ELSE pg_current_wal_lsn() END, flush_lsn), 0) AS flush_lag_bytes,
+ COALESCE(pg_wal_lsn_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_wal_receive_lsn() ELSE pg_current_wal_lsn() END, replay_lsn), 0) AS replay_lag_bytes
+ FROM pg_stat_replication FULL JOIN (VALUES(0)) filler(i) ON TRUE
+ query: SELECT pg_xlog_location_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_receive_location() ELSE pg_current_xlog_location() END, checkpoint_location) "distance" FROM pg_control_checkpoint()
+
+- name: "LastCheckpointDistance"
+ help: "distance to the last checkpoint"
+ scope: cluster
+ min_version: 10
+ values:
+ - "distance"
+ query: SELECT pg_wal_lsn_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_wal_receive_lsn() ELSE pg_current_wal_lsn() END, checkpoint_lsn) "distance" FROM pg_control_checkpoint()
+
+- name: "waldistance"
+ help: "amount of WAL written"
+ scope: cluster
+ min_version: 9.2
+ max_version: 9.6
+ values:
+ - "location"
+ query: SELECT pg_xlog_location_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_receive_location() ELSE pg_current_xlog_location() END, '0/0') "location"
+
+- name: "waldistance"
+ help: "amount of WAL written"
+ scope: cluster
+ min_version: 10
+ values:
+ - "location"
+ query: SELECT pg_wal_lsn_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_wal_receive_lsn() ELSE pg_current_wal_lsn() END, '0/0') "location"
+ print PSQL "DO \$\$DECLARE ext name; BEGIN SELECT INTO ext extname FROM pg_extension WHERE extname = 'pg_stat_statements'; IF NOT FOUND THEN RAISE NOTICE 'Creating pg_stat_statements extension in cluster $version/$cluster'; CREATE EXTENSION pg_stat_statements; END IF; END\$\$ LANGUAGE plpgsql;\n";