Sha256: df59e9c0721b2c6c26d848cfd85f9d63a73bc3e81c1b024f51e0e550e19d4245
Contents?: true
Size: 1.97 KB
Versions: 18
Compression:
Stored size: 1.97 KB
Contents
module PgHero
module Methods
module Maintenance
# http://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
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
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
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
end
end
end
Version data entries
18 entries across 18 versions & 1 rubygems