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 andpassword
is the password. -
Grant privileges to the user.
There are dedicated
pg_read_all_stats
andpg_read_all_settings
roles in PostgreSQL 10 and later. For earlier versions, theSUPERUSER
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 viewpg_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. Thepg_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
andpg_stat_statements
views. Thepg_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.
- 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).
-
(For editions other than EDB Standard and EDB Enterprise) To configure PostgreSQL accessibility, edit the
postgresql.conf
configuration file and change thelisten_address
property value to:listen_address = '*'
Alternatively, you can append the IP address of the DPA server to a comma-separated list of addresses.
-
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. - 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. - 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 addingpg_stat_statements
entry toshared_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 ofpg_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
-
- 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:
- Connect to the PostgreSQL database instance with the DPA user account or superuser (for EDB Enterprise edition).
-
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:
- 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.
- Click Next.
-
Complete the Instance Options panel:
-
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.
-
(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.
-
(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.
- Click Next.
-
- Review the Summary panel:
- Review the information. If necessary, click Back to make any corrections.
- 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.