Preparing your Postgres database for monitoring Innovation Release

Monitoring user

When configuring the agent, you must first connect to the self-managed database you want to monitor and create a user that the agent will use to connect to the database. This user can be any Postgres database user with the pg_monitor role and the pg_read_all_data role who has CONNECT privilege on the target database.

For this example, create a user agent_user:

CREATE USER agent_user WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE <target_database> TO agent_user;
GRANT pg_monitor, pg_read_all_data TO agent_user;

Where <target_database> is the name of the database you want to monitor.

If you're using Postgres 16 or earlier, you must also grant the following permission to allow the agent to discover the Postgres log file location:

GRANT EXECUTE ON FUNCTION pg_catalog.pg_current_logfile() TO agent_user;

You must ensure that the agent user can log in to the database to which you plan to connect. For example, if you plan to connect to a database called hr:

GRANT CONNECT ON DATABASE hr TO USER agent_user;

The edb_internals schema and required extensions

To leverage the full monitoring and optimization features of Hybrid Manager (HM), you must enable extensions that add instrumentation to Postgres. You need these extensions to enable the Query Diagnostics and Recommendations tabs as well as some metrics.

To install the extensions:

1. Refer to the per-extension installation instructions for the correct commands for your platform and Postgres distribution:

   • EDB Wait States

   • EDB Query Advisor

   • EDB Stat Monitor

   In addition, pg_stat_statements must be enabled in the public schema. pg_stat_statements is installed with Postgres, so no additional installation is required.

   For example, on RHEL 9, to monitor EDB Advanced Server 17:

   → WrapCopy

   dnf install edb-as17-server-edb_wait_states \
               edb-as17-query-advisor \
               edb-as17-stat_monitor

2. While connected to the database, add the required preload modules to the Postgres shared_preload_libraries. For example, if the current value of shared_preload_libraries is empty:

   → WrapCopy

   ALTER SYSTEM SET 
   shared_preload_libraries="$libdir/pg_stat_statements","$libdir/edb_wait_states", "$libdir/query_advisor","$libdir/edb_stat_monitor";

3. As a superuser, create the schema in the database to which the agent will be connected and enable the pg_stat_statements extension as well as the extensions installed in the previous step:

   → WrapCopy

   CREATE SCHEMA edb_internals;
   CREATE EXTENSION edb_wait_states WITH SCHEMA edb_internals;
   CREATE EXTENSION query_advisor WITH SCHEMA edb_internals;
   CREATE EXTENSION edb_stat_monitor WITH SCHEMA edb_internals;
   CREATE EXTENSION pg_stat_statements;

4. Add the edb_internals schema to the search path. For example, as a superuser:

   → WrapCopy

   ALTER SYSTEM SET 
   search_path="$user", public, edb_internals;

5. Grant the agent user access to this schema:

   → WrapCopy

   GRANT USAGE ON SCHEMA edb_internals TO agent_user;
   GRANT SELECT ON ALL TABLES IN SCHEMA edb_internals TO agent_user;
   GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA edb_internals TO agent_user;

Postgres logging configuration

To collect Postgres logs, the Postgres logging collector must be enabled and the agent must have permission to read the log files. The simplest way to do this is to run the agent as the Postgres user. However, as a matter of security best practice, we recommend running the agent using a dedicated OS user.

To allow the agent OS user to read the log files, logs must be located outside of the data directory and have appropriate permissions. For example, setting the following parameters in postgresql.conf causes logs to be written to /var/log/postgres-lc and be readable by any user who's a member of the owner's group:

logging_collector=on
log_directory='/var/log/postgres-lc'
log_file_mode=0640
log_destination = 'jsonlog'

The new logging location must be owned by the Postgres user and readable by that user's group. Since the agent OS user is a member of the same group, it then also gets access to the log directory. For example:

mkdir /var/log/postgres-lc
chown postgres:postgres /var/log/postgres-lc
chmod 0750 /var/log/postgres-lc

Restart Postgres after completing these changes.