file_fdw妙用无穷——从数据库读取系统信息

简介: PostgreSQL是最先进的开源数据库,其中一个非常给力的特性就是FDW:外部数据包装器(Foreign Data Wrapper)。通过FDW,用户可以用统一的方式从Pg中访问各类外部数据源。`file_fdw`就是其中随数据库附赠的两个fdw之一。随着pg10的更新,`file_fdw`也添加了一颗赛艇的功能:从程序输出读取。 小霸王妙用无穷,我们能通过`file_fdw`,轻松查看操

PostgreSQL是最先进的开源数据库,其中一个非常给力的特性就是FDW:外部数据包装器(Foreign Data Wrapper)。通过FDW,用户可以用统一的方式从Pg中访问各类外部数据源。file_fdw就是其中随数据库附赠的两个fdw之一。随着pg10的更新,file_fdw也添加了一颗赛艇的功能:从程序输出读取。

小霸王妙用无穷,我们能通过file_fdw,轻松查看操作系统信息,拉取网络数据,把各种各样的数据源轻松喂进数据库里统一查看管理。

安装与配置

file_fdw是Pg自带的组件,不需要奇怪的配置,在数据库中执行以下命令即可启用file_fdw

CREATE EXTENSION file_fdw;

启用FDW插件之后,需要创建一个实例,也是一行SQL搞定,创建一个名为fs的FDW Server实例。

CREATE SERVER fs FOREIGN DATA WRAPPER file_fdw;

创建外部表

举个栗子,如果我想从数据库中读取操作系统中正在运行的进程信息,该怎么做呢?

最典型,也是最常用的外部数据格式就是CSV啦。不过系统命令输出的结果并不是很规整:

>>> ps ux
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
vonng     2658  0.0  0.2 148428  2620 ?        S    11:51   0:00 sshd: vonng@pts/0,pts/2
vonng     2659  0.0  0.2 115648  2312 pts/0    Ss+  11:51   0:00 -bash
vonng     4854  0.0  0.2 115648  2272 pts/2    Ss   15:46   0:00 -bash
vonng     5176  0.0  0.1 150940  1828 pts/2    R+   16:06   0:00 ps -ux
vonng    26460  0.0  1.2 271808 13060 ?        S    10月26   0:22 /usr/local/pgsql/bin/postgres
vonng    26462  0.0  0.2 271960  2640 ?        Ss   10月26   0:00 postgres: checkpointer process
vonng    26463  0.0  0.2 271808  2148 ?        Ss   10月26   0:25 postgres: writer process
vonng    26464  0.0  0.5 271808  5300 ?        Ss   10月26   0:27 postgres: wal writer process
vonng    26465  0.0  0.2 272216  2096 ?        Ss   10月26   0:31 postgres: autovacuum launcher process
vonng    26466  0.0  0.1 126896  1104 ?        Ss   10月26   0:54 postgres: stats collector process
vonng    26467  0.0  0.1 272100  1588 ?        Ss   10月26   0:01 postgres: bgworker: logical replication launcher

可以通过awk,将ps的命令输出规整为分隔符为\x1F的csv格式。

ps aux | awk '{print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,substr($0,index($0,$11))}' OFS='\037'

正戏来啦!通过以下DDL创建一张外表定义

CREATE FOREIGN TABLE process_status (
  username TEXT,
  pid      INTEGER,
  cpu      NUMERIC,
  mem      NUMERIC,
  vsz      BIGINT,
  rss      BIGINT,
  tty      TEXT,
  stat     TEXT,
  start    TEXT,
  time     TEXT,
  command  TEXT
) SERVER fs OPTIONS (
PROGRAM 
$$
ps aux | awk '{print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,substr($0,index($0,$11))}' OFS='\037'
$$
,
FORMAT 'csv', DELIMITER E'\037', HEADER 'TRUE');

这里,关键是通过CREATE FOREIGN TABLE OPTIONS (xxxx)中的OPTIONS提供相应的参数,在PROGRAM参数中填入上面的命令,pg就会在查询这张表的时候自动执行此命令,并读取其输出。FORMAT参数可以指定为CSVDELIMITER参数指定为之前使用的\x1F,并通过HEADER 'TRUE'忽略CSV的第一行

那么结果如何呢?

file_fdw.png

有什么用

最简单的场景,原本系统指标监控需要编写各种监测脚本,部署在奇奇怪怪的地方。然后定期执行拉取metric,再存进数据库。现在通过file_fdw的方式,可以将感兴趣的指标直接录入数据库表,一步到位,而且维护方便,部署简单,更加可靠。在外表上加上视图,定期拉取聚合,将原本一个监控系统完成的事情,在数据库中一条龙解决了。

因为可以从程序输出读取结果,因此file_fdw可以与linux生态里各类强大的命令行工具配合使用,发挥出强大的威力。

其他栗子

诸如此类,实际上后来我发现Facebook貌似有一个类似的产品,叫OSQuery,也是干了差不多的事。通过SQL查询操作系统的指标。但明显PostgreSQL这种方法最简单粗暴高效啦,只要定义表结构,和命令数据源就能轻松对接指标数据,用不了一天就能做出一个功能差不多的东西来。

用于读取系统用户列表的DDL:

CREATE FOREIGN TABLE etc_password (
  username  TEXT,
  password  TEXT,
  user_id   INTEGER,
  group_id  INTEGER,
  user_info TEXT,
  home_dir  TEXT,
  shell     TEXT
) SERVER fs OPTIONS (
  PROGRAM 
$$
awk -F: 'NF && !/^[:space:]*#/ {print $1,$2,$3,$4,$5,$6,$7}' OFS='\037' /etc/passwd
$$
, 
  FORMAT 'csv', DELIMITER E'\037'
);

用于读取磁盘用量的DDL:

CREATE FOREIGN TABLE disk_free (
  file_system TEXT,
  blocks_1m   BIGINT,
  used_1m     BIGINT,
  avail_1m    BIGINT,
  capacity    TEXT,
  iused       BIGINT,
  ifree       BIGINT,
  iused_pct   TEXT,
  mounted_on  TEXT
) SERVER fs OPTIONS (PROGRAM 
$$
df -ml| awk '{print $1,$2,$3,$4,$5,$6,$7,$8,$9}' OFS='\037'
$$
, FORMAT 'csv', HEADER 'TRUE', DELIMITER E'\037'
);

当然,用file_fdw只是一个很Naive的FDW,譬如这里就只能读,不能改。

自己编写FDW实现增删改查逻辑也非常简单,例如Multicorn就是使用Python编写FDW的项目。

SQL over everything,让世界变的更简单~

目录
相关文章
|
存储 数据库 Java
Confluence 6 数据库表-系统信息(System information)
这些表格有存储数据相关的状态和 Confluence 站点的相关配置信息。 confversion 被用来在升级系统的时候确定那个数据库的版本应该使用,这个表格只对数据库升级有影响。
760 0
|
23天前
|
安全 关系型数据库 MySQL
MySQL安全最佳实践:保护你的数据库
本文深入探讨了MySQL数据库的安全防护体系,涵盖认证安全、访问控制、网络安全、数据加密、审计监控、备份恢复、操作系统安全、应急响应等多个方面。通过具体配置示例,为企业提供了一套全面的安全实践方案,帮助强化数据库安全,防止数据泄露和未授权访问,保障企业数据资产安全。
|
8天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
39 3
|
15天前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。
|
1月前
|
存储 运维 关系型数据库
从MySQL到云数据库,数据库迁移真的有必要吗?
本文探讨了企业在业务增长背景下,是否应从 MySQL 迁移至云数据库的决策问题。分析了 MySQL 的优势与瓶颈,对比了云数据库在存储计算分离、自动化运维、多负载支持等方面的优势,并提出判断迁移必要性的五个关键问题及实施路径,帮助企业理性决策并落地迁移方案。
|
3天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
3天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
2天前
|
SQL 关系型数据库 MySQL
Mysql数据恢复—Mysql数据库delete删除后数据恢复案例
本地服务器,操作系统为windows server。服务器上部署mysql单实例,innodb引擎,独立表空间。未进行数据库备份,未开启binlog。 人为误操作使用Delete命令删除数据时未添加where子句,导致全表数据被删除。删除后未对该表进行任何操作。需要恢复误删除的数据。 在本案例中的mysql数据库未进行备份,也未开启binlog日志,无法直接还原数据库。
|
8天前
|
Ubuntu 安全 关系型数据库
安装与配置MySQL 8 on Ubuntu,包括权限授予、数据库备份及远程连接指南
以上步骤提供了在Ubuntu上从头开始设置、配置、授权、备份及恢复一个基础但完整的MySQL环境所需知识点。
145 7
|
8天前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能(上)
最终建议:当前系统是完美的读密集型负载模型,优化重点应放在减少行读取量和提高数据定位效率。通过索引优化、分区策略和内存缓存,预期可降低30%的CPU负载,同时保持100%的缓冲池命中率。建议每百万次查询后刷新统计信息以持续优化
55 6