Module: PgHero::Methods::Maintenance

Included in:
Database
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 "    SELECT\n      c.oid::regclass::text as table,\n      (SELECT setting FROM pg_settings WHERE name = 'autovacuum_freeze_max_age')::int -\n      GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_before_autovacuum\n    FROM\n      pg_class c\n    LEFT JOIN\n      pg_class t ON c.reltoastrelid = t.oid\n    WHERE\n      c.relkind = 'r'\n      AND (SELECT setting FROM pg_settings WHERE name = 'autovacuum_freeze_max_age')::int - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) < 2000000\n    ORDER BY\n      transactions_before_autovacuum\n  SQL\nend\n"

#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 "    SELECT\n      schemaname AS schema,\n      relname AS table,\n      last_vacuum,\n      last_autovacuum,\n      last_analyze,\n      last_autoanalyze\n    FROM\n      pg_stat_user_tables\n    ORDER BY\n      1, 2\n  SQL\nend\n"

#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 "    SELECT\n      c.oid::regclass::text AS table,\n      2146483648 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_before_shutdown\n    FROM\n      pg_class c\n    LEFT JOIN\n      pg_class t ON c.reltoastrelid = t.oid\n    WHERE\n      c.relkind = 'r'\n      AND (2146483648 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < \#{threshold}\n    ORDER BY\n     2, 1\n  SQL\nend\n"