postgres数据库表数据增量统计

秒速五厘米 2022-01-15 03:43 692阅读 0赞

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')