数据库说明

数据库使用的是时序数据库timescaledb。

数据安装

docker-compose 安装示例

version: '3.8'  
services:  
  timescaledb:  
    image: timescale/timescaledb:latest-pg16  # 或使用其他版本的 TimescaleDB 镜像  
    container_name: timescaledb
    environment:  
      POSTGRES_PASSWORD: wueasy123321  # 设置你的数据库密码  
    ports:  
      - "5432:5432"  # 将容器的 5432 端口映射到宿主机的 5432 端口  
    volumes:  
      - ./data:/var/lib/postgresql/data  # 持久化数据  
    restart: always  # 确保容器在 Docker 守护进程启动时自动启动

数据库表脚本

CREATE TABLE monitor_disk (
	TIME TIMESTAMPTZ NOT NULL,
	sys_no VARCHAR ( 64 ) NOT NULL,
	mountpoint VARCHAR ( 255 ) NOT NULL,
	total BIGINT NOT NULL,
	used BIGINT NOT NULL,
	read_count NUMERIC ( 20, 4 ) NULL,
	write_count NUMERIC ( 20, 4 ) NULL,
	read_bytes NUMERIC ( 20, 4 ) NULL,
	write_bytes NUMERIC ( 20, 4 ) NULL,
	read_time BIGINT NULL,
	write_time BIGINT NULL 
);
SELECT
	create_hypertable ( 'monitor_disk', by_range ( 'time', INTERVAL '1 day' ) );
CREATE INDEX idx_monitor_disk ON monitor_disk ( sys_no, mountpoint, TIME );
CREATE TABLE monitor_memory (
	TIME TIMESTAMPTZ NOT NULL,
	sys_no VARCHAR ( 64 ) NOT NULL,
	virtual_total BIGINT,
	virtual_free BIGINT,
	virtual_used BIGINT,
	swap_total BIGINT,
	swap_free BIGINT,
	swap_used BIGINT 
);
SELECT
	create_hypertable ( 'monitor_memory', by_range ( 'time', INTERVAL '1 day' ) );
CREATE INDEX idx_monitor_memory ON monitor_memory ( sys_no, TIME );
CREATE TABLE monitor_network (
	TIME TIMESTAMPTZ NOT NULL,
	sys_no VARCHAR ( 64 ) NOT NULL,
	network_name VARCHAR ( 64 ) NOT NULL,
	bytes_recv NUMERIC ( 20, 4 ),
	bytes_sent NUMERIC ( 20, 4 ),
	packets_recv NUMERIC ( 20, 4 ),
	packets_sent NUMERIC ( 20, 4 ) 
);
SELECT
	create_hypertable ( 'monitor_network', by_range ( 'time', INTERVAL '1 day' ) );
CREATE INDEX idx_monitor_network ON monitor_network ( sys_no, network_name, TIME );
CREATE TABLE monitor_sys_info (
	TIME TIMESTAMPTZ NOT NULL,
	sys_no VARCHAR ( 64 ) NOT NULL,
	platform VARCHAR ( 255 ),
	platform_family VARCHAR ( 255 ),
	platform_version VARCHAR ( 255 ),
	os VARCHAR ( 255 ),
	hostname VARCHAR ( 255 ),
	kernel_version VARCHAR ( 255 ),
	cpu_physical_cnt INT,
	cpu_logical_cnt INT 
);
SELECT
	create_hypertable ( 'monitor_sys_info', by_range ( 'time', INTERVAL '1 day' ) );
CREATE INDEX idx_monitor_sys_info ON monitor_sys_info ( sys_no, TIME );
CREATE TABLE monitor_alarm ( TIME TIMESTAMPTZ NOT NULL, sys_no VARCHAR ( 64 ) NOT NULL, UUID VARCHAR ( 40 ), status INT2, CONTENT TEXT );
SELECT
	create_hypertable ( 'monitor_alarm', by_range ( 'time', INTERVAL '1 day' ) );
CREATE INDEX idx_monitor_alarm ON monitor_alarm ( sys_no, TIME );
CREATE TABLE monitor_alarm_log (
	TIME TIMESTAMPTZ NOT NULL,
	sys_no VARCHAR ( 64 ) NOT NULL,
	UUID VARCHAR ( 40 ),
	business_no VARCHAR ( 64 ),
	business_name VARCHAR ( 128 ),
	URI TEXT 
	);
SELECT
	create_hypertable ( 'monitor_alarm_log', by_range ( 'time', INTERVAL '1 day' ) );
CREATE INDEX idx_monitor_alarm_log ON monitor_alarm_log ( sys_no, TIME );
CREATE TABLE monitor_basis (
	TIME TIMESTAMPTZ NOT NULL,
	sys_no VARCHAR ( 64 ) NOT NULL,
	cpu_total_percent NUMERIC ( 10, 4 ) NULL,
	connections BIGINT NULL,
	load_avg1 NUMERIC ( 10, 2 ) NULL,
	load_avg5 NUMERIC ( 10, 2 ) NULL,
	load_avg15 NUMERIC ( 10, 2 ) NULL 
);
SELECT
	create_hypertable ( 'monitor_basis', by_range ( 'time', INTERVAL '1 day' ) );
CREATE INDEX idx_monitor_basis ON monitor_basis ( sys_no, TIME );