References > Template Reference > PostgreSQL

PostgreSQL

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

Works with the Orion Agent for Linux.

Prerequisites

On the SAM Server, 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 SAM Server:

hostallall192.168.3.198/32trust

where 192.168.3.198 is IP address of your SAM Server.

Configurations for Orion agent for Linux

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

  1. Login 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 dbuser with the password Password1 to the Linux-based computer. The next commands login as the database superuser to the ProgreSQL server and modify the created user.

    # adduser dbuser
    # passwd Password1
    # su - postgres
    $ psql -d template1 -U postgres

    At the prompt, create the dbuser account with the Password1 password:

    template1=# CREATE USER dbuser WITH PASSWORD 'Password1';

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

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

    Close the template:

    template1=# \q
  5. Test access to the database using the newly created account credentials.

    $ su - dbuser
    $ psql -d TestDatabase -U dbuser

Configure and assign a SAM template with the credentials.

  1. On the 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 checkboxes 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 'postgresuser'", change the authentication method to md5.

Credentials

Database user name and password.

Monitored Components

For details on monitors, see SAM Component Monitor Types.

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