1.用于保存统计数据总量的表
CREATE TABLE IF NOT EXISTS recording_size_table(
id serial,
group_id int,
database_name varchar(255),
database_record int,
schema_record varchar,
tablespace_size varchar,
record_time varchar(32) DEFAULT to_char(now(), 'YYYY-MM-DD HH24:MI:SS')
)
WITH (
OIDS = FALSE
);
2.统计数据总量的函数
-- 创建函数
CREATE OR REPLACE FUNCTION table_recording_size(gaddr varchar, gport int, guser varchar, gpwd varchar) returns void AS
$body$
DECLARE
rcd record;
rcd_tmp record;
str varchar;
group_ids integer;
connect_sql varchar;
database_records integer;
schema_str varchar;
tablespace_str varchar;
exec_sql varchar;
conn varchar;
BEGIN
-- 设置group_ids
group_ids = (SELECT COALESCE(max(group_id), 0) + 1 AS max FROM recording_size_table)::integer;
-- 遍历所有数据库
FOR rcd IN SELECT datname, (tup_inserted - tup_deleted) AS counts FROM pg_stat_database
WHERE datname <> 'template0' AND datname <> 'template1' ORDER BY datname
loop
database_records = 0;
-- 创建dblink连接
conn = 'dbconn';
connect_sql = 'host=' || gaddr || ' port=' || gport || ' user=' || guser
|| ' password=' || gpwd || ' dbname=' || rcd.datname;
str = (SELECT dblink_connect(conn,connect_sql));
-- 创建统计表条数的函数
exec_sql = 'create or replace function count_numbers_of_table(sch varchar, tab varchar) returns integer as
$$
declare
rcd record;
begin
execute(''select count(*) from ''' || ' || sch' || ' || ''.''' || ' || tab) into rcd;
return rcd.count;
end;
$$ language plpgsql';
str = (select dblink_exec(conn, exec_sql));
-- 查询数据库的数据总量
exec_sql = 'select sum(count_numbers_of_table(pg_stat_user_tables.schemaname::text,
pg_stat_user_tables.relname::text)) as counts from pg_stat_user_tables group by schemaname';
for rcd_tmp in select * from dblink(conn, exec_sql) as a(counts integer)
loop
database_records = database_records + rcd_tmp.counts;
end loop;
-- 查询所有模式的数据总量
exec_sql = 'select schemaname, sum(count_numbers_of_table(pg_stat_user_tables.schemaname::text,
pg_stat_user_tables.relname::text)) as counts from pg_stat_user_tables group by schemaname';
schema_str = '{' || (SELECT string_agg('"'||schemaname||'":'||counts,',') AS record_size FROM (
select * from dblink(conn, exec_sql) as a(schemaname varchar, counts integer)) as b) || '}';
-- 断开连接
str = (SELECT dblink_disconnect(conn));
-- 保存查询数据
INSERT INTO recording_size_table(group_id, database_name, database_record, schema_record) VALUES (group_ids, rcd.datname, database_records, schema_str);
END loop;
-- 查询表空间大小
tablespace_str = '{'||(
SELECT string_agg('"'||spcname||'":'||spcsize,',') AS tablespace_str
FROM (SELECT spcname, pg_tablespace_size(pg_tablespace.spcname) AS spcsize
FROM pg_tablespace) AS spc
)||'}';
-- 插入表空间大小
EXECUTE('update recording_size_table set tablespace_size = ''' || tablespace_str || ''' where id = (select max(id) from recording_size_table)');
END;
$body$ LANGUAGE plpgsql;
-- drop函数
-- drop function if exists table_recording_size(gaddr varchar, gport int, guser varchar, gpwd varchar);
-- 执行函数
-- select table_recording_size('127.0.0.1', 5432, 'postgres', '123456');
-- 查询统计数据
-- select * from recording_size_table order by database_name, group_id;
-- delete from recording_size_table;
-- 断开dblink
-- SELECT dblink_disconnect('dbconn')