Register a PostgreSQL database instance and prepare for monitoring

This registration procedure applies to the following deployment types:

Self-managed
Amazon RDS for PostgreSQL
Amazon Aurora for PostgreSQL
Azure Database for PostgreSQL
Cloud SQL for PostgreSQL

Complete the following tasks to register a PostgreSQL database instance for monitoring with DPA.

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.

Differences in registering a PostgreSQL database instance

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 (including EDB Postgres 10.x and later) do one of the following:
  - Run the following commands to grant the minimum required privileges:
    
    GRANT pg_read_all_stats, pg_read_all_settings, pg_signal_backend TO dpa_user;
    
    GRANT ALL ON ALL TABLES IN SCHEMA public TO dpa_user;
    
    The second GRANT command enables the DPA monitoring user to collect execution plans, which it needs to generate index and table advisors. At a minimum, the monitoring user must be granted SELECT, INSERT, UPDATE, and DELETE privileges. You can use a more granular command to list specific privileges and tables. Examples include:
    
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE table_name TO dpa_user;
    
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dpa_user;
  - Grant the monitoring user SUPERUSER privileges using the commands in the next two bullets.
- 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;
  
  GRANT ALL ON ALL TABLES IN SCHEMA public TO dpa_user;
  
  The second GRANT command enables the DPA monitoring user to collect execution plans, which it needs to generate index and table advisors. At a minimum, the monitoring user must be granted SELECT, INSERT, UPDATE, and DELETE privileges. You can use a more granular command to list specific privileges and tables. Examples include:
  
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE table_name TO dpa_user;
  
  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public 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).
- Enables DPA to generate table and index advisors.
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.

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 = 4096
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	4096

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 and the pgstattuple extension in the database. These extensions are 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.
- The pgstattuple extension is required to enable DPA to display the Index Bloat Metrics percentage on a table advisor,
To create the extensions:
1. Connect to the PostgreSQL database instance with the DPA user account or superuser (for EDB Enterprise edition).
2. Execute following commands:
  
  CREATE EXTENSION pg_stat_statements;
  CREATE EXTENSION pgstattuple;

Task 3: (Optional) 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.

This option can be enabled either during or after registration. If you would like to collect CPU metrics for a PostgreSQL instance that is already registered, just perform the steps in this section.

Download and install the extension on your PostgreSQL database server using one of the following methods:
- Install the extension on a Linux or macOS server using 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
```
- Install the extension on a Linux or macOS server using an RPM package.
  1. Download the 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.
Run the following SQL command to install the extension in the PostgreSQL database instance:
```
CREATE EXTENSION system_stats;
```
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;
```

(Optional) Task 4: 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 5: 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.
On the Connection Information panel, enter the server name or IP address of the database instance.
Enter the port number.
Enter the name of a database in this instance.

The specified database is used during registration. DPA monitors all databases in the instance.
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.
Select the authentication method used when the DPA monitoring user connects to this database instance.
Enter the user name and password for the monitoring user account that you created previously, and then 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.

You can specify which database instances DPA collects execution plans from.

Troubleshooting the registration

`Certificate is not valid` error when Verify-CA or Verify-Full mode is selected

When Verify-CA or Verify-Full SSL mode is selected, the client checks the certificate chain up to a trusted certificate authority (CA). If the root certificate is signed by a custom CA, validation can fail with the message because the custom CA is not trusted:

Certificate is not valid. The SSL connection test failed.

Work-around: Import an intermediate certificate or a server certificate from the displayed certificate chain, or follow the instructions for creating a certificate chain in the PostgreSQL documentation topic PostgreSQL Secure TCP/IP Connections with SSL.

`Connection to the server failed` error when Verify-Full mode is selected

When Verify-Full SSL mode is selected, the connection to the database instance fails if the host name does not match the name specified in a certificate's Subject Alternative Name or CN (Common Name). Validation fails with the following message:

Connection to the server failed. If you are trying to connect to the server using SSL, verify that your SSL certificate is valid.

Work-around: Do either of the following:

Create a new certificate on the database server that matches the required database host name in its Subject Alternative Name or Common Name.
Use Verify-CA SSL mode instead.

Unable to connect to an instance when Verify-Ca SSL or Verify-Full mode is selected

When Verify-Ca or Verify-Full SSL mode is selected, in some environments DPA might be unable to connect to a PostgreSQL instance even after a trusted certificate is imported. This can happen because DPA uses its own SSL factory implementation by default for Verify-CA and Verify-Full modes.

Work-around: Do the following to override the default DPA behavior and use the PostgreSQL default SSL factory implementation instead:

Verify that the following files are in the required formats:
- The sslcert (client certificate) must be in PEM format with a *.crt extension.
- The sslkey (PKCS-8 client key) must be in PKCS8 or PKCS12 format with a *.pk8 or *.p12 extension.
- The sslrootcert (root certificate) must be in PEM format with a *.crt extension.
If your Certificate Authority certificates are not in one of those formats and you need to convert them, you can refer to the article Convert SSL Certificates into appropriate format using OpenSSL.

For more help configuring PostgreSQL JDBC SSL clients, see the PostgreSQL.org document Using SSL. For complete command line examples of how to export certificates in different formats, PostgreSQL suggests viewing the certdir Makefile.

When you use a PKCS-12 client certificate, the name or alias in the command line must be the actual string user. For example: openssl pkcs12 -export -name user ....

For information about PostgreSQL JDBC connection parameters, see Connection Parameters.
In the DPA Registration Wizard, click Advanced Connection Properties.

Under JDBC URL Properties, enter the following properties, separated by a semicolon:

Property	Value
`sslmode`	The SSL mode: `verify-ca` `verify-full`
`sslcert`	The location and file name of the client certificate.
`sslkey`	The location and file name of the client key.
`sslrootcert`	The location and file name of the root certificate.

For example:

sslmode=verify-ca;sslcert=D:\server.crt;sslkey=D:\server.key;sslrootcert=D:\root.crt

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.

Search SolarWinds Support