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 for PostgreSQL
Amazon Aurora for PostgreSQL
Azure Database for PostgreSQL
Cloud SQL for PostgreSQL

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.

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.
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;
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.

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.

If you want to perform complete monitoring, enable and configure the pg_stat_statements extension for Text Poll and Stats Poll functionality:

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.

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 Single Server or Hyperscale deployments, modify your Server parameters to include the parameter values listed in the previous table.
For Azure Flexible Server deployments, allow-list the pg_stat_statements extension. For information about how to allow-list an extension, see PostgreSQL extensions in Azure Database for PostgreSQL - Flexible Server.

Restart the PostgreSQL server.

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;

(Optional) Task 3: Set up SSL communication for Google Cloud instances

If a PostgreSQL instance runs in the Google Cloud Platform and you want to use SSL communication, use the Google Cloud SQL Auth proxy to enable it. Run the Google Cloud SQL Auth proxy for that database instance on the DPA server to create a secure tunnel between DPA and the Cloud SQL instance. For more information, see About the Cloud SQL Auth proxy.

Task 4: Run the Register Instance Wizard

In the upper-left corner of the DPA home page, click Register DB Instance for Monitoring.
Click the PostgreSQL option under Self-managed, Amazon RDS and Aurora, Microsoft Azure, or Google Cloud SQL.
Click Next.
Complete the Connection Information panel:
1. Enter the server name or IP address of the database instance.
2. Enter the port number.
3. Enter the name of a database in this instance.
  The specified database is used during registration. DPA monitors all databases in the instance.
4. 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.
5. Select the authentication method used when the DPA monitoring user connects to this database instance.
6. Enter the user name and password for the monitoring user account that you created previously.
7. Click Next.
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 manually created instance groups, you can assign this database instance to one of the groups.
  
  If no manual groups exist, this option is not shown.
3. (Optional) If you have existing alert groups, you can assign this database instance to one or more groups.
  
  If no alert groups exist, or the existing groups do not match this instance's database type, this option is not shown.
4. Click Next.
Review the Summary panel:
1. Review the information. If necessary, click Back to make any corrections.
2. When all information is correct, click Register.

The scripts are not supported under any SolarWinds support program or service. The scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.