PostgreSQL DBA之数据库指标监控

-- 创建一个视图，提供有关PostgreSQL数据库大小的信息
CREATE OR REPLACE VIEW view_database_size AS
SELECT pg_database.datname as "Database",
pg_size_pretty(pg_database_size(pg_database.datname)) as "Size"
FROM pg_database;
 
-- 创建一个视图，提供有关数据库中每个表的大小信息
CREATE OR REPLACE VIEW view_table_size AS
SELECT
    table_schema || '.' || table_name as "Table",
    pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) as "Size"
FROM
    information_schema.tables
ORDER BY
    pg_total_relation_size(table_schema || '.' || table_name) DESC;
 
-- 创建一个函数，返回数据库的Bloat Factor
CREATE OR REPLACE FUNCTION get_bloat_factor(database_name text) RETURNS numeric AS $$
DECLARE
    bloat_ratio numeric;
BEGIN
    EXECUTE 'SELECT n_dead_tup / n_live_tup INTO ' || bloat_ratio || ' FROM pg_stat_user_tables WHERE n_live_tup + n_dead_tup <> 0';
    RETURN bloat_ratio;
END;
$$ LANGUAGE plpgsql;

这个代码实例提供了创建视图和函数的基本模板，用于监控PostgreSQL数据库的大小和表的碎片程度。在实际使用时，需要根据具体的监控需求进行调整和扩展。