How to get CPU usage of PostgreSQL server?

Question

I'm reading about PostgreSQL monitoring tool. I get a lot of information like database size etc. but can't find info about CPU and memory usage.
Are these informations provided by PostgreSQL server or do I have to get it by my native client application (for example, find PID of PostgreSQL and call OS API function).

Answer 1

That's not really a PostgreSQL monitoring tool, that's PostgreSQL's internal facilities for diagnostics, monitoring and database statistics.

For host monitoring you want a tool like Icinga (modernized Nagios) or Zabbix. When monitoring PostgreSQL the check_postgres tool is likely to be of interest.

If you're interested in getting the CPU use of a particular PostgreSQL backend process from your own custom programs, you must SELECT pg_backend_pid() or get the pid of the task of interest from pg_stat_activity then use OS-level tools and APIs to collect the desired information.

If you desired, you could write a PostgreSQL C extension function to collect the information and return it as a row or rowset over the PostgreSQL protocol, so you could SELECT my_system_stats(). See:

• C-language functions
• Extensions

Essentially, you write a library in C using the PostgreSQL APIs, compile it using the pgxs make helper and have the server dynamically load the resulting shared object. Then you tell it what C functions are callable from SQL using CREATE FUNCTION. You can then call them like any other function. C extensions can call OS-level APIs or do pretty much whatever they want, though it is generally very unwise for them to create threads, write directly to PostgreSQL files/filehandles, or read/write PostgreSQL sockets.