Module: PgReports::Modules::System

Extended by:
System
Included in:
System
Defined in:
lib/pg_reports/modules/system.rb

Overview

System-level database statistics Most report methods are generated from YAML definitions in lib/pg_reports/definitions/system/

Instance Method Summary collapse

Instance Method Details

#checkpoint_stats(limit: 10) ⇒ Object

Checkpoint stats — uses version-specific SQL because PostgreSQL 17+ moved checkpoint columns from pg_stat_bgwriter to pg_stat_checkpointer



141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/pg_reports/modules/system.rb', line 141

def checkpoint_stats(limit: 10)
  sql_file = (pg_version >= 170_000) ? :checkpoint_stats : :checkpoint_stats_legacy
  data = executor.execute_from_file(:system, sql_file)
  data = data.first(limit) if limit

  Report.new(
    title: "Checkpoint Statistics",
    data: data,
    columns: %w[checkpoints_timed checkpoints_requested checkpoint_write_time_sec
      checkpoint_sync_time_sec buffers_checkpoint buffers_clean
      bgwriter_stops buffers_alloc requested_pct stats_reset]
  )
end

#current_database ⇒ String

Get current database name

Returns:

  • (String) —

    Current database name



166
167
168
169
170
171
 
# File 'lib/pg_reports/modules/system.rb', line 166

def current_database
  result = executor.execute("SELECT current_database() AS database")
  result.first&.fetch("database", "unknown") || "unknown"
rescue
  "unknown"
end

#databases_list ⇒ Array<Hash>

Get list of all databases

Returns:

  • (Array<Hash>) —

    List of databases with sizes



157
158
159
160
161
162
 
# File 'lib/pg_reports/modules/system.rb', line 157

def databases_list
  executor.execute_from_file(:system, :databases_list)
rescue
  # Fallback to empty array if query fails
  []
end

#enable_pg_stat_statements! ⇒ Hash

Enable pg_stat_statements extension Tries to create extension, returns helpful error if it fails

Returns:

  • (Hash) —

    Result with success status and message



95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
 
# File 'lib/pg_reports/modules/system.rb', line 95

def enable_pg_stat_statements!
  # Check if already enabled
  if pg_stat_statements_available?
    return {success: true, message: "pg_stat_statements is already enabled"}
  end

  # Try to create extension
  begin
    executor.execute("CREATE EXTENSION IF NOT EXISTS pg_stat_statements")

    # Verify it worked
    if pg_stat_statements_available? && pg_stat_statements_preloaded?
      {success: true, message: "pg_stat_statements extension created successfully"}
    elsif pg_stat_statements_available?
      {
        success: false,
        message: "Extension created but not preloaded. Add 'pg_stat_statements' to shared_preload_libraries in postgresql.conf and restart PostgreSQL.",
        requires_restart: true
      }
    else
      {
        success: false,
        message: "Failed to create extension. Check database permissions.",
        requires_restart: false
      }
    end
  rescue => e
    error_message = e.message

    # Provide helpful message for common errors
    if error_message.include?("could not open extension control file") ||
        error_message.include?("extension \"pg_stat_statements\" is not available")
      {
        success: false,
        message: "pg_stat_statements is not installed. Add to postgresql.conf: " \
                 "shared_preload_libraries = 'pg_stat_statements' and restart PostgreSQL.",
        requires_restart: true
      }
    else
      {success: false, message: "Failed to create extension: #{error_message}"}
    end
  end
end

#live_metrics(long_query_threshold: 60) ⇒ Hash

Live metrics for dashboard monitoring

Parameters:

  • long_query_threshold (Integer) (defaults to: 60) —

    Threshold in seconds for long queries

Returns:

  • (Hash) —

    Metrics data

Raises:

  • (StandardError) —

    If no data is returned



61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/pg_reports/modules/system.rb', line 61

def live_metrics(long_query_threshold: 60)
  data = executor.execute_from_file(:system, :live_metrics,
    long_query_threshold: long_query_threshold)

  row = data.first

  # If no data returned, something is wrong with the query or permissions
  if row.nil? || row.empty?
    raise StandardError, "No statistics data returned. Check database permissions and pg_stat views."
  end

  {
    connections: {
      active: row["active_connections"].to_i,
      idle: row["idle_connections"].to_i,
      total: row["total_connections"].to_i,
      max: row["max_connections"].to_i,
      percent: row["connections_pct"].to_f
    },
    transactions: {
      total: row["total_transactions"].to_i,
      commit: row["xact_commit"].to_i,
      rollback: row["xact_rollback"].to_i
    },
    cache_hit_ratio: row["heap_hit_ratio"].to_f,
    long_running_count: row["long_running_count"].to_i,
    blocked_count: row["blocked_count"].to_i,
    timestamp: row["timestamp_epoch"].to_f
  }
end

#pg_stat_statements_available? ⇒ Boolean

pg_stat_statements availability check

Returns:

  • (Boolean) —

    Whether pg_stat_statements is available



23
24
25
26
27
28
29
30
 
# File 'lib/pg_reports/modules/system.rb', line 23

def pg_stat_statements_available?
  result = executor.execute(<<~SQL)
    SELECT EXISTS (
      SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements'
    ) AS available
  SQL
  result.first&.fetch("available", false) || false
end

#pg_stat_statements_preloaded? ⇒ Boolean

Note:

This method tries to query pg_stat_statements directly instead of checking shared_preload_libraries, which requires pg_read_all_settings role

Check if pg_stat_statements is preloaded and functional

Returns:

  • (Boolean) —

    Whether pg_stat_statements is preloaded



36
37
38
39
40
41
42
43
44
45
 
# File 'lib/pg_reports/modules/system.rb', line 36

def pg_stat_statements_preloaded?
  # If extension is not installed, it can't be preloaded
  return false unless pg_stat_statements_available?

  # Try to query pg_stat_statements - if it works, it's properly preloaded
  executor.execute("SELECT 1 FROM pg_stat_statements LIMIT 1")
  true
rescue
  false
end

#pg_stat_statements_status ⇒ Hash

Get pg_stat_statements status details

Returns:

  • (Hash) —

    Status information



49
50
51
52
53
54
55
 
# File 'lib/pg_reports/modules/system.rb', line 49

def pg_stat_statements_status
  {
    extension_installed: pg_stat_statements_available?,
    preloaded: pg_stat_statements_preloaded?,
    ready: pg_stat_statements_available? && pg_stat_statements_preloaded?
  }
end