Module: PgHero::Methods::Maintenance

Included in:
Database
Defined in:
lib/pghero/methods/maintenance.rb

Instance Method Summary collapse

Instance Method Details

#analyze(table, verbose: false) ⇒ Object 



64
65
66
67
 
# File 'lib/pghero/methods/maintenance.rb', line 64

def analyze(table, verbose: false)
  execute "ANALYZE #{"VERBOSE " if verbose}#{quote_table_name(table)}"
  true
end

#analyze_tables(verbose: false, min_size: nil, tables: nil) ⇒ Object 



69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
# File 'lib/pghero/methods/maintenance.rb', line 69

def analyze_tables(verbose: false, min_size: nil, tables: nil)
  tables = table_stats(table: tables).reject { |s| %w(information_schema pg_catalog).include?(s[:schema]) }
  tables = tables.select { |s| s[:size_bytes] > min_size } if min_size
  tables.map { |s| s.slice(:schema, :table) }.each do |stats|
    begin
      with_transaction(lock_timeout: 5000, statement_timeout: 120000) do
        analyze "#{stats[:schema]}.#{stats[:table]}", verbose: verbose
      end
      success = true
    rescue ActiveRecord::StatementInvalid => e
      $stderr.puts e.message
      success = false
    end
    stats[:success] = success
  end
end

#autovacuum_danger ⇒ Object 



29
30
31
32
 
# File 'lib/pghero/methods/maintenance.rb', line 29

def autovacuum_danger
  max_value = select_one("SHOW autovacuum_freeze_max_age").to_i
  transaction_id_danger(threshold: 2000000, max_value: max_value)
end

#maintenance_info ⇒ Object 



46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
# File 'lib/pghero/methods/maintenance.rb', line 46

def maintenance_info
  select_all <<~SQL
    SELECT
      schemaname AS schema,
      relname AS table,
      last_vacuum,
      last_autovacuum,
      last_analyze,
      last_autoanalyze,
      n_dead_tup AS dead_rows,
      n_live_tup AS live_rows
    FROM
      pg_stat_user_tables
    ORDER BY
      1, 2
  SQL
end

#transaction_id_danger(threshold: 10000000, max_value: 2146483648) ⇒ Object

https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND "the system will shut down and refuse to start any new transactions once there are fewer than 1 million transactions left until wraparound" warn when 10,000,000 transactions left



8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
# File 'lib/pghero/methods/maintenance.rb', line 8

def transaction_id_danger(threshold: 10000000, max_value: 2146483648)
  sql = <<~SQL
    SELECT
      n.nspname AS schema,
      c.relname AS table,
      :max_value - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_left
    FROM
      pg_class c
    INNER JOIN
      pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN
      pg_class t ON c.reltoastrelid = t.oid
    WHERE
      c.relkind = 'r'
      AND (:max_value - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < :threshold
    ORDER BY
     3, 1, 2
  SQL
  select_all(sql, {max_value: max_value.to_i, threshold: threshold.to_i})
end

#vacuum_progress ⇒ Object 



34
35
36
37
38
39
40
41
42
43
44
 
# File 'lib/pghero/methods/maintenance.rb', line 34

def vacuum_progress
  select_all <<~SQL
    SELECT
      pid,
      phase
    FROM
      pg_stat_progress_vacuum
    WHERE
      datname = current_database()
  SQL
end