Documentation forDatabase Performance Analyzer

Register a PostgreSQL database instance and prepare for monitoring

Complete the following tasks to register a PostgreSQL database instance for monitoring with DPA. This procedure includes the following deployment types:

  • Self-managed
  • Amazon RDS
  • PostgreSQL-compatible Aurora
  • Azure

You can also use mass registration to registrations to register multiple database instances, or you can register database instances using scripts that call the DPA API.

Registering a PostgreSQL database instance is slightly different than registering other types of monitored database instances:

  • You cannot use the wizard to create the DPA monitoring user. Create the monitoring user manually, as described below.

  • If the DPA repository is an Oracle database, DPA stores performance data for monitored PostgreSQL database instances in the default tablespace of the repository user. You cannot change the default tablespace in the Register Instance Wizard. If you need to change the default tablespace, register the instance using mass registration.

  • You must configure each PostgreSQL database instance, as described below.

Task 1: Create the DPA monitoring user

Use these instructions to manually create the user that DPA uses to monitor a PostgreSQL database instance. The user will have the necessary rights and privileges.

DPA ignores data on the monitored database instance from the monitoring user. Make sure the monitoring user will not cause load on the monitored instance.

  1. Run the following SQL statement on the PostgreSQL database instance to create the DPA monitoring user:

    CREATE USER dpa_user WITH ENCRYPTED PASSWORD 'password';

    where dpa_user is the user name and password is the password.

  2. Grant privileges to the user.

    There are dedicated pg_read_all_stats and pg_read_all_settings roles in PostgreSQL 10 and later. For earlier versions, the SUPERUSER privilege is required.

    • For PostgreSQL 10.x and later:

      GRANT pg_read_all_stats, pg_read_all_settings, pg_signal_backend TO dpa_user;

    • For PostgreSQL 9.6.x in self-managed deployments:

      ALTER USER dpa_user WITH SUPERUSER;

    • For PostgreSQL 9.6.x in Amazon RDS and Amazon Aurora deployments:

      GRANT rds_superuser TO dpa_user;

    • For PostgreSQL 9.6.x in Azure deployments:

      GRANT azure_pg_admin TO dpa_user;

  3. If you are monitoring EDB Postgres version 10, you must give the DPA monitoring user access to the pg_stat_statements view.

    For EDB Postgres version 10, granting the the pg_read_all_stats role does not give the DPA monitoring user access to the view pg_stat_statements.

    To grant access, create a dedicated DPA schema and make a synonym of pg_stat_statements in it:

    CREATE SCHEMA dpa_schema;

    CREATE VIEW dpa_schema.pg_stat_statements AS SELECT * FROM enterprisedb.pg_stat_statements;

    GRANT USAGE ON SCHEMA dpa_schema TO dpa_user

    GRANT SELECT ON dpa_schema.pg_stat_statements TO dpa_user;

Task 2: Configure PostgreSQL database instances for DPA monitoring

Determine which monitoring mode to use

DPA offers two modes of monitoring PostgreSQL database instances. The monitoring mode you choose determines what configuration steps are required.

  • Limited monitoring queries only the pg_stat_activity view. The pg_stat_activity view is a system view containing information about database server processes activity. Limited monitoring:

    • Is sufficient for getting wait time information for queries.
    • Returns incomplete SQL texts, and query execution statistics might be missing.
  • Complete monitoring queries both the pg_stat_activity and pg_stat_statements views. The pg_stat_statements view contains execution statistics for all SQL statements executed by a server. Complete monitoring:

    • Provides complete SQL texts and query execution statistics.
    • Requires additional pg_stat_statements extension configuration (described in the following section).

    PostgreSQL is delivered as a set of mandatory and optional packages. The pg_stat_statements extension provides a means for tracking SQL statement execution statistics and is required for complete monitoring. This extension is included by default in PostgreSQL distributions for Linux and Windows OS. Installation of other extensions is platform-dependent. See https://www.postgresql.org/download/ for more information.

Configure each database instance

Complete the following steps to configure each PostgreSQL database instance that you want to monitor.

  1. Enable remote access to the PostgreSQL instance.

    Remote access is enabled by default for EDB Standard and EDB Enterprise editions. For those versions, you can skip step 1b below.

    1. Adjust firewall rules to allow an incoming connection from DPA to the monitored instance. Ensure that the port the PostgreSQL instances is listening on is open (port 5432 by default).
    2. (For editions other than EDB Standard and EDB Enterprise) To configure PostgreSQL accessibility, edit the postgresql.conf configuration file and change the listen_address property value to:

      listen_address = '*'

      Alternatively, you can append the IP address of the DPA server to a comma-separated list of addresses.

    3. To configure authentication methods for the DPA user, edit the pg_hba.conf configuration file and add the following host record:

      host all dpa_user all md5

      where dpa_user is the DPA monitoring user name created previously.

    4. If the pg_hba.conf configuration file restricts access to the monitored instance to a range of IP addresses, ensure that the DPA server is included in the IP address range.
    5. Restart the PostgreSQL server.
  2. If you want to perform complete monitoring, enable and configure the pg_stat_statements extension for Text Poll and Stats Poll functionality:

    1. Run the following command to determine if the extension is installed:

      SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

      If there is no installed version or you receive the error pg_stat_statements does not exist, you must load the extension (as described in the following step). The extension is loaded by adding pg_stat_statements entry to shared_preload_libraries because it requires additional shared memory.

    2. To load the pg_stat_statements extension (if needed) and configure it, perform one of the following tasks:

      • For on-premises deployments, edit the postgresql.conf file and add or modify the following entries:

        shared_preload_libraries = 'pg_stat_statements'

        track_activity_query_size = 2048

        pg_stat_statements.track = top

        Optionally, you can enter pg_stat_statements.track = all instead of pg_stat_statements.track = top.

      • For Amazon RDS deployments, use the AWS Console to modify your existing custom DB Parameter Group or create a new DB Parameter Group. Then enter the following parameter values:

        Parameter name Value
        pg_stat_statements.track ALL
        shared_preload_libraries pg_stat_statements
        track_activity_query_size 2048
      • For Azure deployments, modify your Server parameters to include the parameter values listed in the previous table.

    3. Restart the PostgreSQL server.
  3. Create the pg_stat_statements extension in the database. The extension is database-bound and must be created for each database.

    The pg_stat_statements extension must be created in the database used to connect to DPA.

    To create the extension: 

    1. Connect to the PostgreSQL database instance with the DPA user account or superuser (for EDB Enterprise edition).
    2. Execute following command:

      CREATE EXTENSION pg_stat_statements;

Task 3: Run the Register Instance Wizard

  1. In the upper-left corner of the DPA homepage, click Register DB Instance for Monitoring.
  2. Under Self-Managed, Amazon, or Azure, click PostgreSQL.
  3. Click Next.
  4. Complete the Connection Information panel:
    1. Enter the server name or IP address of the database instance and the port number.
    2. Under SSL mode, specify the type of secure socket layer (SSL) connections established between the instance and the DPA server:
      • Disable: SSL connections are not used.

      • Require: SSL is enabled, but no certificate checks are performed.

      • Verify-CA: SSL is enabled. The client verifies that the server is trustworthy by checking the certificate chain up to a trusted certificate authority (CA).

        If you select this option and DPA cannot access a trusted certificate, you are prompted to import a certificate into the DPA trust store. Click the arrow on the right to view certificate details.

      • Verify-Full: SSL is enabled. The client verifies the certificate chain and also verifies that the server hostname matches its certificate's Subject Alternative Name or Common Name (CN).

        If you select this option and DPA cannot access a trusted certificate, you are prompted to import a certificate into the DPA trust store. Click the arrow on the right to view certificate details.

    3. Select the authentication method used when the DPA monitoring user connects to this database instance.
    4. Enter the user name and password for the monitoring user account that you created previously.
    5. Click Next.
  5. Complete the Instance Options panel:
    1. Enter the name that DPA will display to identify this database instance.

      The Display name field defaults to the name retrieved from the database instance.

    2. (Optional) If you have existing database instance groups, you can assign this database instance to one of the groups.

      If you do not have database instance groups, the Instance group field is not shown.

    3. (Optional) If you have existing alert groups, you can assign this database instance to one or more groups.

      If you do not have alert groups, the Alert group field is not shown.

    4. Click Next.
  6. Review the Summary panel:
    1. Review the information. If necessary, click Back to make any corrections.
    2. When all information is correct, click Register.