PostgreSQL

This SAM application monitor template assesses the performance of a PostgreSQL 9 Server database by retrieving performance data from the built-in pg_stat_database, pg_locks, pg_trigger, pg_proc, pg_stat_all_tables and pg_indexes views.

This template supports the SolarWinds Platform Agent for Linux.

Prerequisites

On the SolarWinds Platform, download and install the PostgreSQL 9 ODBC driver from the following location: http://www.postgresql.org/ftp/odbc/versions/msi/. On the PostgreSQL Server, find the following file:

C:\Program Files\PostgreSQL\9.0\data\pg_hba.conf

and add the following line to allow remote connections for the SolarWinds Platform server:

hostallall192.168.3.198/32trust

where 192.168.3.198 is IP address of your SolarWinds Platform server.

Configurations for SolarWinds Platform agent for Linux

For the following instructions, you should have PostgreSQL installed on a Linux-based computer.

1. Log in to the Linux-based computer with administrator privileges.

   You will need to create a user account on the server and in the PostgreSQL database.

2. (Required) To begin, you should have unixODBC installed to support Microsoft Windows ODBC. If not, download and install the unixODBC driver to the Linux-based computer.

   Command: yum install unixODBC

3. Download and install the PostgreSQL ODBC driver on the target Linux-based computer.

   Command: yum install postgresql-odbc

4. Create a user account for the database.

   The example of commands adds a user account dbuserID with the password Password to the Linux-based computer. The next commands login as the database superuser to the ProgreSQL server and modify the created user.

   # adduser dbuserID
   # passwd Password
   # su - postgres
   $ psql -d template1 -U postgres

   At the prompt, create the dbuserID account with the Password password:

   template1=# CREATE USER dbuserID WITH PASSWORD 'Password';

   Next, grant privileges for your PostgreSQL database (TestDatabase) to the user account:

   template1=# GRANT ALL PRIVILEGES ON DATABASE TestDatabase to dbuserID;

   Close the template:

   template1=# \q

5. Test access to the database using the newly created account credentials.

   $ su - dbuserID
   $ psql -d TestDatabase -U dbuserID

Configure and assign a SAM template with the credentials.

1. In the SolarWinds Platform Web Console, click Settings > All Settings > SAM Settings > Manage Templates.
2. Search for the PostgreSQL template. SolarWinds recommends creating a copy of the template. Select the template and click Copy.
3. Modify the settings of the template and component monitors based on the metrics you want to monitor.
4. To enter credentials, select the component monitor check boxes and click Assign Credentials.
5. Enter the credentials you created for the database and click OK.
6. Test the access for the template by assigning it to a node. Click Test Node to assign and test the access. If you encounter issues, verify the credentials and privileges for the account.

Troubleshooting

If you receive the error "FATAL: Peer authentication method failed for user 'xxx'", change the authentication method to md5.

Credentials

Database user name and password.

Component monitors

Click here for an overview about SAM application monitor templates and component monitors. SAM API Poller templates are also available.

Components without predetermined threshold values will provide guidance such as "Use the lowest threshold possible" or "Use the highest threshold possible" to help you find a threshold appropriate for your application.

Database Cache Hit Ratio (%)

This counter returns the percentage of pages found in the buffer cache without having to read from the disk in the specified database. This is the formula used: “cache reads”/(”cache reads”+”physical reads”)*100

This ratio should exceed 90%, and ideally be over 99%. If your counter is lower than 90%, you should consider adding more RAM if possible. A higher ratio value returned indicates improved performance by your server.

By default, this counter looks in the postgres database. If you want to monitor another database, you should change the database name in the Connection String field: (…DataBase=postgres;…) and the Sql Query field: (…where datname = 'postgres').

Database Success Rate (%)

This counter returns the percentage of successful transactions in the specified database. This is the formula used: “committed transactions”/(”committed transactions”+”rolled back transactions”)*100

This ratio should exceed 90%, and ideally be over 99%.

By default, this counter looks in the postgres database. If you want to monitor another database, you should change the database name in the Connection String field: (…DataBase=postgres;…) and Sql Query field: (…where datname = 'postgres').

Total Active Server Connections

This counter returns the total number of server connections that are active. You should set the thresholds appropriate to your environment.

Active Connections in Specified Database

This counter returns the number of connections to the specified database. You should set the thresholds appropriate to your environment.

By default, this counter looks in the postgres database. If you want to monitor another database, you should change the database name in the Connection String field, (…DataBase=postgres;…) and the Sql Query field: (…where datname = 'postgres').

Database Size (MB)

This counter returns the size of the specified database in MB.

Note: By default, this counter looks in the postgres database. If you want to monitor another database, you should change the database name in the Connection String field, (…DataBase=postgres;…) and the Sql Query field, (…pg_database_size('postgres')…).

Current Number of Locks on Server

This counter returns the total number of locks on the server.

This counter should be as low as possible. For more information about locks you should create a query and look in the pg_locks view.

Total Indexes in Current Database

This counter returns the total number of indexes for a current database. For more information about locks, you should look in the pg_indexes view.

By default this counter looks in the postgres database. If you want to monitor another database, you should change the database name in the Connection String field: (…DataBase=postgres;…).

Table with the biggest number of Sequential Scans

This counter returns the name of the table and its number of sequential scans for the current database.

By default, this counter looks in the postgres database. If you want to monitor another database, you should change the database name in the Connection String field: (…DataBase=postgres;…).

Table with the biggest number of Index Scans

This counter returns the name of the table and its number of index scans for the current database.

By default, this counter looks in the postgres database. If you want to monitor another database, you should change the database name in the Connection String field: (…DataBase=postgres;…).

Table with the biggest number of Row Reads

This counter returns the name of the table and its number of row reads for the current database.

By default, this counter looks in the postgres database. If you want to monitor another database, you should change the database name in the Connection String field: (…DataBase=postgres;…).

Total Number of Triggers

This counter returns the total number of triggers for the current database.

By default this counter looks in the postgres database. If you want to monitor another database, you should change the database name in the Connection String field: (…DataBase=postgres;…).

Size of the Largest Table (MB)

This counter returns the name of the largest table and its size in MB for the current database.

By default this counter looks in the postgres database. If you want to monitor another database, you should change database name in the Connection String filed: (…DataBase=postgres;…).

Total Number of Tables in Current Database

This counter returns the total number of tables for the current database.

By default this counter looks in the postgres database. If you want to monitor another database, you should change the database name in the Connection String field: (…DataBase=postgres;…).