Let's say you have a cluster of Postgres database servers and you want a way to easily monitor their CPU and memory usage. You could install any of the system monitoring daemons like Nagios, Collectd, Munin, etc, but that means opening up another port in your firewall, and maintaning another running process. If all you're after is basic info like CPU and memory usage, there's an easier way!
We'll create two database tables that will allow you to query CPU and memory usage from within the database connection. This way your applications can monitor the health of the servers without needing to worry about another connection or another protocol.
You can run these commands on the master database and they will propagate to all the slave databases as well.
First, load the file foreign data wrapper and create the foreign data server:
CREATE EXTENSION file_fdw; CREATE SERVER fileserver FOREIGN DATA WRAPPER file_fdw;
Then we'll create the table that loads CPU loadavg from the /proc/loadavg file:
CREATE FOREIGN TABLE loadavg (one text, five text, fifteen text, scheduled text, pid text) SERVER fileserver OPTIONS (filename '/proc/loadavg', format 'text', delimiter ' ');
Creating the table that will let you query memory info is similar:
CREATE FOREIGN TABLE meminfo (stat text, value text) SERVER fileserver OPTIONS (filename '/proc/meminfo', format 'csv', delimiter ':');
Now you can run SELECT queries to see the info!
postgres=# SELECT * FROM loadavg; one | five | fifteen | scheduled | pid ------+------+---------+-----------+------- 0.00 | 0.01 | 0.05 | 1/159 | 21454 (1 row)
postgres=# SELECT * FROM meminfo;
stat | value
-------------------+------------------------
MemTotal | 30759060 kB
MemFree | 29438908 kB
Buffers | 109452 kB
Cached | 749156 kB
SwapCached | 0 kB
Active | 502216 kB
Inactive | 400444 kB
Active(anon) | 59128 kB
Inactive(anon) | 128188 kB
Active(file) | 443088 kB
Inactive(file) | 272256 kB
Unevictable | 0 kB
Mlocked | 0 kB
SwapTotal | 0 kB
SwapFree | 0 kB
Dirty | 252 kB
Writeback | 0 kB
AnonPages | 44120 kB
Mapped | 146932 kB
Shmem | 143264 kB
Slab | 86020 kB
SReclaimable | 57200 kB
SUnreclaim | 28820 kB
KernelStack | 1408 kB
PageTables | 6324 kB
NFS_Unstable | 0 kB
Bounce | 0 kB
WritebackTmp | 0 kB
CommitLimit | 15379528 kB
Committed_AS | 4450676 kB
VmallocTotal | 34359738367 kB
VmallocUsed | 65880 kB
VmallocChunk | 34359670376 kB
HardwareCorrupted | 0 kB
AnonHugePages | 0 kB
HugePages_Total | 0
HugePages_Free | 0
HugePages_Rsvd | 0
HugePages_Surp | 0
Hugepagesize | 2048 kB
DirectMap4k | 31465472 kB
DirectMap2M | 0 kB
(42 rows)
Of course you can always query specific parts of meminfo like this:
postgres=# SELECT * FROM meminfo WHERE stat IN ('MemTotal','MemFree');
stat | value
----------+---------------------
MemTotal | 30759060 kB
MemFree | 29438452 kB
(2 rows)
As you can see, the meminfo data is a little messy because of the whitespace in /proc/meminfo, so this will require some cleanup in your code that is querying the data in order to be useful. But at least you can get at the data from the system easier this way!
Let me know if you do anything fun with this!
