Documentation forDatabase Performance Analyzer

Enable DPA to collect CPU metrics from a PostgreSQL instance

To enable DPA to collect CPU metrics from a PostgreSQL instance, the system_stats extension must be installed in the PostgreSQL instance. The system_stats extension is a library of stored procedures that provide access to database server metrics.

Task 1: Download and install the extension on your PostgreSQL database server

Install the extension on a Linux or macOS server

You have two options for installing the extension on a Linux or macOS server.

Option 1: Use the PGXS framework

You can build the module using the PGXS framework, which is the PostgreSQL build infrastructure for extensions.

  1. Download the tar.gz file from the system_stats repository in Github.

  2. Move the downloaded file to an appropriate directory, and run a command such as the following to extract the contents.

    tar -zxvf system_stats-1.0.tar.gz

  3. Make sure the PATH environment variable includes that directory where you extracted the .tar file.

  4. Compile and install the code. For example:

    cd system_stats-1.0
PATH="/usr/local/pgsql/bin:$PATH" make USE_PGXS=1
sudo PATH="/usr/local/pgsql/bin:$PATH" make install USE_PGXS=1

Option 2: Use an RPM package

  1. Go to https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-latest-x86_64/ and download the following package:

    system_stats_10-1.0-1.rhel8.x86_64.rpm

  2. To install the RPM package, run the following command from a command prompt:

    rpm -ivh packageName

Install the extension on a Windows server

You can build the module using the Visual Studio project file.

  1. Download the .zip file from the system_stats repository in Github.

  2. Extract the .zip file to $PGSRC\contrib\system_stats.

  3. Set the PG_INCLUDE_DIR and PG_LIB_DIR environment variables to make sure the PostgreSQL include and lib directories can be found for compilation. For example:

    PG_INCLUDE_DIR=C:\Program Files\PostgreSQL\12\include
​​​​​​​PG_LIB_DIR=C:\Program Files\PostgreSQL\12\lib

  4. Open the Visual Studio project file system_stats.vcxproj in the \system_stats directory, and build the project.

Task 2: Install the extension in the PostgreSQL database instance

Run the following SQL command to install the extension in the PostgreSQL database instance:

CREATE EXTENSION system_stats;

Task 3: Grant permissions to the DPA user

To give DPA access to the collected metrics, add the DPA user to the monitor_system_stats role:

GRANT monitor_system_stats to dpa_user;