postgres数据库表数据增量统计

最新推荐文章于 2025-05-10 10:45:09 发布

原创最新推荐文章于 2025-05-10 10:45:09 发布 · 3.9k 阅读

3 ·

CC 4.0 BY-SA版权

postgresql 专栏收录该内容

24 篇文章

订阅专栏

本文介绍了一个用于数据库统计和监控的自定义解决方案，包括创建用于记录数据库大小、表数量及表空间使用情况的表格，以及一个PL/pgSQL函数，该函数通过连接到不同的数据库实例来收集这些信息，并将其存储在统一的监控表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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