How does eG Enterprise Monitor PostgreSQL Server?

eG Enterprise employs agent-based or agentless techniques to monitor the PostgreSQL server. An eG agent periodically checks the status of critical database operations and proactively report problems.

Pre-requisites for Monitoring the PostgreSQL Server

  1. In order to monitor an on-premises PostgreSQL server, you need to manually create a special database user account in every PostgreSQL database instance that requires monitoring. When doing so, ensure that this user is vested with superuser privileges. The sample script we recommend for user creation for eG monitoring is:

    CREATEROLE eguser LOGIN

    ENCRYPTED PASSWORD {‘eguser password’}

    SUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

    Specify the name of this user in the Username text box while configuring the tests.

  2. In order to monitor a PosgreSQL server hosted on Microsoft Azure, you need to manually create a special database user account in every PostgreSQL database instance that requires monitoring. When doing so, ensure that this user is granted CONNECT privileges. The sample script we recommend for user creation for eG monitoring is:

    CREATEROLE eguser LOGIN

    ENCRYPTED PASSWORD {‘eguser password’}

    GRANT CONNECT ON DATABASE "<DB_NAME>" TO <USER_NAME>;

    Specify the name of this user in the Username text box while configuring the tests.

  3. For the eG agent to connect to the target PostgreSL server and collect the required metrics, administrators should specify the details of the eG agent in the pg_hba.conf file of the PostgreSQL server. The steps to be followed as discussed below:

    • Execute the show hba_file command from the command prompt or from the shell script of the PostgreSQL server console. Note that the command should be executed from a superuser session i.e., as a pgadmin user.

      Figure 1 : Executing the show hba_file

    • Once the command is executed successfully, the location of the pg_hba.conf file will be displayed as shown in Figure 1.

    • Open the pg_hba.conf file using any editor of your choice.

    • Append the details of the eG agent in the following section:

      # TYPE DATABASE USER ADDRESS METHOD

      For example, you can specify the details of the eG agent with IP address 172.16.14.106 as:

      host all all 172.16.14.106/32 scram-sha-256

      Figure 2 : Appending the IP address of the eG agent in pg_hba.conf file

  4. For the PostgreSQL Workload Testtest to run and collect metrics on the query execution statistics of the target server, the user with superuser privilege configured for that test should enable the pg_stat_statementsand then restart the Postgres server, To enable pg_stat_statements, run the following commands:

    CREATE EXTENSION pg_stat_statements;

    ALTER SYSTEM SET shared_preload_libraries=’pg_stat_statements’;

    ALTER SYSTEM SET pg_stat_statements.save = false;

Managing the PostgreSQL Server

The eG Enterprise cannot automatically discover the PostgreSQL server. This implies that you need to manually add the component for monitoring. Remember that the eG Enterprise automatically manages the components that are added manually. To manage a PostgreSQL Server component, do the following:

  1. Log into the eG administrative interface.
  2. eG Enterprise cannot automatically discover the PostgreSQL component. You need to manually add the server using the COMPONENTS page (see Figure 3) that appears when the Infrastructure -> Components -> Add/Modify menu sequence is followed. Remember that components manually added are managed automatically.

    Figure 3 : Adding the PostgreSQL

  3. Specify the Host IP and the Nick name of the Oracle Cluster in Figure 3. The Port number will be set as 5432 by default. If the server is listening on a different port, then override this settings.
  4. Then, click the Add button to register the changes.
  5. When you attempt to sign out, a list of unconfigured tests appears (see Figure 4).

    Figure 4 : List of tests to be configured for the PostgreSQL

  6. Click on the PostgreSQL Access test to configure it. To know how to configure the test, click here.
  7. Once all the tests are configured, signout of the eG administrative interface.