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



165
166
167
168
169
170
171
172
173
174
175
176
177
 
# File 'lib/pg_reports/modules/system.rb', line 165

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

#connected? ⇒ Boolean

Whether the database connection can execute a basic query. Used to tell "the connection itself is down" apart from "connected, but pg_stat_statements isn't set up yet".

Returns:

  • (Boolean) 


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

def connected?
  executor.execute("SELECT 1")
  true
rescue
  false
end

#current_database ⇒ String

Get current database name

Returns:

  • (String) —

    Current database name



190
191
192
193
194
195
 
# File 'lib/pg_reports/modules/system.rb', line 190

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



181
182
183
184
185
186
 
# File 'lib/pg_reports/modules/system.rb', line 181

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



119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
 
# File 'lib/pg_reports/modules/system.rb', line 119

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: 5) ⇒ Hash

Live metrics for dashboard monitoring

Parameters:

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

    Threshold in seconds for long queries

Returns:

  • (Hash) —

    Metrics data

Raises:

  • (StandardError) —

    If no data is returned



85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
 
# File 'lib/pg_reports/modules/system.rb', line 85

def live_metrics(long_query_threshold: 5)
  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.

Note: whether pg_stat_statements is in shared_preload_libraries cannot be read by a plain monitoring role (that requires the pg_read_all_settings role), so we never look at the setting. Instead we derive the state from signals every role can observe: can we run a query at all, does the extension exist in pg_extension, and is its view queryable.

Returns:

  • (Hash) —

    Status information



67
68
69
70
71
72
73
74
75
76
77
78
79
 
# File 'lib/pg_reports/modules/system.rb', line 67

def pg_stat_statements_status
  unless connected?
    return {connected: false, extension_installed: false, preloaded: false, ready: false}
  end

  installed = pg_stat_statements_available?
  {
    connected: true,
    extension_installed: installed,
    preloaded: pg_stat_statements_preloaded?,
    ready: installed && pg_stat_statements_preloaded?
  }
end