PostgreSQL

Overview

PostgreSQL is the world's most advanced database system, with a global community of thousands of users and contributors and dozens of companies and organizations.

Setup

The postgres monitoring is accomplished by bridge plugin which is included with the SolarWinds Snap Agent by default. Follow the directions below to enable it for an agent instance. The bridge plugin utilize Telegraf PostgreSQL plugin.

Prerequisites

You'll need to have a PostgreSQL server accessible. Take note of the host address, username, database name, and password. This will be needed later in the configuration step.

Make sure a role is created for the PostgreSQL user you add to the configuration file. If you don't the logs will report that PostgreSQL is "Unable to log in as <user>". I.e. if PostgreSQL is installed on a Ubuntu server you can connect to psql as user postgres and create a role.

sudo su - postgres
psql
# CREATE ROLE username;

Configuration

The agent provides an example task file to help you get started quickly, but requires you to provide the correct settings for your PostgreSQL installation. To enable the task:

Make a copy of the postgres example task file task-bridge-postgresql.yaml.example, renaming it to task-bridge-postgresql.yaml:

On Windows, using Explorer or PowerShell:

copy "C:\ProgramData\SolarWinds\Snap\tasks-autoload.d\task-bridge-postgresql.yaml.example" "C:\ProgramData\SolarWinds\Snap\tasks-autoload.d\task-bridge-postgresql.yaml"

On Linux using command line:

sudo cp -p /opt/SolarWinds/Snap/etc/tasks-autoload.d/task-bridge-postgresql.yaml.example /opt/SolarWinds/Snap/etc/tasks-autoload.d/task-bridge-postgresql.yaml

Edit the task file with settings specific to your PostgreSQL install:

If you wish to also collect logs for this service, uncomment the last section in the example task file. For more information on collecting logs, see the logs collector docs.

---
version: 2

schedule:
  type: cron
  interval: "0 * * * * *"

plugins:
  - plugin_name: bridge

    config:
      postgresql:
        ## specify address via a url matching:
        ##   postgres://[pqgotest[:password]]@localhost[/dbname]\
        ##       ?sslmode=[disable|verify-ca|verify-full]
        ## or a simple string:
        ##   host=localhost user=pqotest password=... sslmode=... dbname=app_production
        ##
        ## All connection parameters are optional. Without the dbname parameter,
        ## the driver will default to a database with the same name as the user.
        ## This dbname is just for instantiating a connection with the server and doesn't
        ## restrict the databases we are trying to grab metrics for.
        # address: "host=localhost user=pquser sslmode=disable dbname=someDB"
        address: "postgres://pquser@localhost/someDB"

        ## A custom name for the database that will be used as the "server" tag in the
        ## measurement output. If not specified, a default one generated from
        ## the connection address is used.
        # outputaddress: "db01"

        ## connection configuration.
        ## maxlifetime - specify the maximum lifetime of a connection.
        ## default is forever (0s)
        # max_lifetime: "0s"

        ## A  list of databases to explicitly ignore.  If not specified, metrics for all
        ## databases are gathered.  Do NOT use with the 'databases' option.
        # ignored_databases: 
        #   - postgres
        #   - template0
        #   - template1

        ## A list of databases to pull metrics about. If not specified, metrics for all
        ## databases are gathered.  Do NOT use with the 'ignored_databases' option.
        # databases: 
        #   - app_production
        #   - testing

    publish:
      - plugin_name: publisher-appoptics

## If you want to gather logs for this integration, uncomment the following section.
#  - plugin_name: log-files
#    config:
#      file_paths:
#        - /path/to/postgres.log

#    publish:
#      - plugin_name: loggly-http-bulk

If running the database locally on a cloud instance you may need to replace localhost with 127.0.0.1. Verify the host adddress by running sudo netstat -plunt | grep postgres in the console.

Restart the agent after saving your configuration changes:

On Windows command line:
```
net stop swisnapd
net start swisnapd
```
On Linux command line:
```
sudo service swisnapd restart
```
Enable the PostgreSQL integration in AppOptics

On the Integrations Page you will see PostgreSQL integration available if the previous steps were successful. It may take a couple minutes before the PostgreSQL integration is identified. Select the PostgreSQL integration to open the configuration menu in the UI, and enable it. If you do not see it, see Troubleshooting Linux.

Testing Integration

To check if and what metrics can be collected with given configuration, run bridge plugin in debug mode:

On Windows command line:

"C:\Program Files\SolarWinds\Snap\bin\snap-plugin-collector-bridge.exe" --debug-mode --plugin-config  "{\"postgresql\": {\"address\": \"postgres://pguser@localhost/someDB\"}}"

On Linux command line:

/opt/SolarWinds/Snap/bin/snap-plugin-collector-bridge --debug-mode --plugin-config "{\"postgresql\": {\"address\": \"postgres://pguser@localhost/someDB\"}}"

Metrics and Tags

The table below outlines the default set of metrics collected by the postgres integration.

Namespace	Description
postgresql.blk_read_time	Total time the statement spent reading blocks, in milliseconds
postgresql.blk_write_time	Total time the statement spent writing blocks, in milliseconds
postgresql.blks_hit	Total number of shared block cache hits by the statement
postgresql.blks_read	Total number of shared blocks read by the statement
postgresql.buffers_alloc	Number of buffers allocated
postgresql.buffers_backend	Number of buffers written directly by a backend
postgresql.buffers_backend_fsync	Number of times a backend had to execute its own fsync call
postgresql.buffers_checkpoint	Number of buffers written during checkpoints
postgresql.buffers_clean	Number of buffers written by the background writer
postgresql.checkpoint_sync_time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk, in milliseconds
postgresql.checkpoint_write_time	Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds
postgresql.checkpoints_req	Number of requested checkpoints that have been performed
postgresql.checkpoints_timed	Number of scheduled checkpoints that have been performed
postgresql.conflicts	Number of queries canceled due to conflicts with recovery in this database.
postgresql.datid	OID of database in which the statement was executed
postgresql.deadlocks	Number of deadlocks detected in this database
postgresql.maxwritten_clean	Number of times the background writer stopped a cleaning scan because it had written too many buffers
postgresql.numbackends	Number of backends currently connected to this database.
postgresql.temp_bytes	Total amount of data written to temporary files by queries in this database.
postgresql.temp_files	Number of temporary files created by queries in this database.
postgresql.tup_deleted	Number of rows deleted by queries in this database
postgresql.tup_fetched	Number of rows fetched by queries in this database
postgresql.tup_inserted	Number of rows inserted by queries in this database
postgresql.tup_returned	Number of rows returned by queries in this database
postgresql.tup_updated	Number of rows updated by queries in this database
postgresql.xact_commit	Number of transactions in this database that have been committed
postgresql.xact_rollback	Number of transactions in this database that have been rolled back

Tag Name	Description
db	Name of the database
hostname	Name of the host. Instead of using this tag we recommend using the `@host` alias.
server	Name of the server

Search SolarWinds Support