Postgres

Postgres 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 plugin is included with the AppOptics host agent by default, please follow the directions below to enable it for a host agent instance.

Prerequisites

You’ll need to have a Postgres 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 Postgres user you add to the configuration file. If you don’t the logs will report that Postgres is “Unable to log in as <user>”. If Postgres 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 host agent provides an example configuration file to help you get started quickly. It defines the plugin and task file to be loaded by the agent, but requires you to provide the correct settings for your Postgres installation. To enable the plugin:

  1. Make a copy of the postgres example configuration file /opt/appoptics/etc/plugins.d/postgresql.yaml.example, renaming it to /opt/appoptics/etc/plugins.d/postgresql.yaml:
$ sudo cp /opt/appoptics/etc/plugins.d/postgresql.yaml.example /opt/appoptics/etc/plugins.d/postgresql.yaml
  1. Update the /opt/appoptics/etc/plugins.d/postgresql.yaml configuration file with settings specific to your Postgres install, for example:
#in /opt/appoptics/etc/plugins.d/postgresql.yaml:
address: "host=localhost user=my_username sslmode=disable dbname=my_database password=my_password"

Note

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.

  1. Restart the agent after saving your configuration changes:
$ sudo service appoptics-snapteld restart
  1. Enable the Postgres plugin

On the Integrations Page you will see the Postgres available if the previous steps were successful. If you do not see the plugin, see Troubleshooting.

Select the Postgres plugin to open the configuration menu in the UI, and enable the plugin.

Metrics and Tags

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

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

Metric Tags

The table below outlines the default set of tags provided for each metric.

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

Here is a helpful guide on installing and configuring a Postgres server on Ubuntu. Take a look at this guide on granting permissions if you have any issues managing roles.