Monitoring

Both postmaster and postgres produce useful information for administrators. They have many flags to control the information they output. If desired, they can show user connection information, SQL queries, and detailed performance statistics.

When the postmaster starts, its output should be sent to a file in the POSTGRESQL home directory. That file can then be used to monitor database activity. See the postmaster and postgres manual pages for a complete list of output options. To specify the flags to be passed to each postgres process, use the postmaster -o flag.

Another way to monitor the database is by using ps. The ps operating system command displays information about processes, including data about the postmaster and postgres processes. This tool is helpful for analyzing POSTGRESQL activity, particularly for diagnosing problems. The ps command can display information about the following aspects of a process:

• Current CPU usage
• Total CPU usage
• Start time
• Memory usage
• Disk operations (on some operating systems)

Each operating system uses different ps  flags to output these values. A typical display is

 

        
        USER       PID %CPU      TIME STARTED    VSZ INBLK OUBLK COMMAND
        ...
        postgres 18923 45.4   0:27.79  1:15PM   2140    34     1 /usr/local/postgres/ ...
         

In this case, process 18923 is using 45.4% of the CPU, has used 27.79 seconds of CPU time, was started at 1:15 P.M., has read 34 blocks, and has written 1 block.

To identify who is using each postgres process, most operating systems allow ps to display the following connection information:

• User name
• User's network address
• Database
• SQL command keyword (SELECT, INSERT, UPDATE, DELETE, CREATE, idle, ...)

The ps command displays this information next to the name of each postgres process. A typical display is

 

        
          PID  TT  STAT      TIME COMMAND
        ...
        18923  ??  S      0:27.79 /usr/local/postgres/bin/postgres demouser localhost test SELECT
        ...
         

In this example, demouser, using process ID 18923, is connecting from the local machine to database test and executing a SELECT. Administrators can use ps to analyze who is connected to each database, the query command being run, and the system resources used.