Module: PgHero::Methods::Maintenance

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

Instance Method Summary collapse

Instance Method Details

#autovacuum_danger ⇒ Object 



26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
# File 'lib/pghero/methods/maintenance.rb', line 26

def autovacuum_danger
  select_all <<-SQL
    SELECT
      c.oid::regclass::text as table,
      (SELECT setting FROM pg_settings WHERE name = 'autovacuum_freeze_max_age')::int -
      GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_before_autovacuum
    FROM
      pg_class c
    LEFT JOIN
      pg_class t ON c.reltoastrelid = t.oid
    WHERE
      c.relkind = 'r'
      AND (SELECT setting FROM pg_settings WHERE name = 'autovacuum_freeze_max_age')::int - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) < 2000000
    ORDER BY
      transactions_before_autovacuum
  SQL
end

#maintenance_info ⇒ Object 



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

def maintenance_info
  select_all <<-SQL
    SELECT
      schemaname AS schema,
      relname AS table,
      last_vacuum,
      last_autovacuum,
      last_analyze,
      last_autoanalyze
    FROM
      pg_stat_user_tables
    ORDER BY
      1, 2
  SQL
end

#transaction_id_danger(options = {}) ⇒ Object

www.postgresql.org/docs/9.1/static/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
 
# File 'lib/pghero/methods/maintenance.rb', line 8

def transaction_id_danger(options = {})
  threshold = options[:threshold] || 10000000
  select_all <<-SQL
    SELECT
      c.oid::regclass::text AS table,
      2146483648 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_before_shutdown
    FROM
      pg_class c
    LEFT JOIN
      pg_class t ON c.reltoastrelid = t.oid
    WHERE
      c.relkind = 'r'
      AND (2146483648 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < #{threshold}
    ORDER BY
     2, 1
  SQL
end