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 



70
71
72
73
 
# File 'lib/pghero/methods/maintenance.rb', line 70

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

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



75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/pghero/methods/maintenance.rb', line 75

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 



31
32
33
34
 
# File 'lib/pghero/methods/maintenance.rb', line 31

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 



52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
# File 'lib/pghero/methods/maintenance.rb', line 52

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

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
28
29
 
# File 'lib/pghero/methods/maintenance.rb', line 8

def transaction_id_danger(threshold: 10000000, max_value: 2146483648)
  max_value = max_value.to_i
  threshold = threshold.to_i

  select_all <<~SQL
    SELECT
      n.nspname AS schema,
      c.relname AS table,
      #{quote(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 (#{quote(max_value)} - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < #{quote(threshold)}
    ORDER BY
     3, 1, 2
  SQL
end

#vacuum_progress ⇒ Object 



36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
# File 'lib/pghero/methods/maintenance.rb', line 36

def vacuum_progress
  if server_version_num >= 90600
    select_all <<~SQL
      SELECT
        pid,
        phase
      FROM
        pg_stat_progress_vacuum
      WHERE
        datname = current_database()
    SQL
  else
    []
  end
end