PostgreSQL: Monitoring PostgreSQL with OpsDash

Quick Start

1. Install the OpsDash Smart Agent on the PostgreSQL server that you wish to monitor. If you need instructions for how to do this, here they are:  Debian/UbuntuCentOS/RHELGeneric.

2. Create a user on the PostgreSQL instance for OpsDash.

$ sudo -u postgres psql
psql (9.5.5)
Type "help" for help.

postgres=# create user opsdash superuser unencrypted password 'p@ssw0rd';
CREATE ROLE
postgres=# \q
$

3. Edit the OpsDash Smart Agent configuration file at  /etc/opsdash/agent.cfg and add a new service:

# choose a good, unique name for the service (here my-pg-1 is used)
service "my-pg-1" {
    type = "postgresql"
    host = "localhost"
    user = "opsdash"
    pass = "p@ssw0rd"
    db = "postgres"
}

4. Reload the OpsDash Smart Agent so that it picks up the new configuration:

sudo service opsdash-agent reload

5. You should see the service my-pg-1 appear under  https://app.opsdash.com/sources within a minute or so. Clicking on it should take you to the dashboard at https://app.opsdash.com/source/my-pg-1. If it does not, check the agent log file at /var/log/opsdash/agent.log.

SSL Mode

The OpsDash Smart Agent can connect to the PostgreSQL instance using SSL also. Use the sslmode keyword in the configuration block, like this:

service "my-pg-1" {
    type = "postgresql"
    host = "localhost"
    user = "opsdash"
    pass = "p@ssw0rd"
    db = "postgres"

    # sslmode, optional, one of "disable" (default) or "require"
    sslmode = "require"
}

PostgreSQL 10

The "opsdash" database user can run with fewer privileges starting with PostgreSQL v10:

$ sudo -u postgres psql
psql (10.0)
Type "help" for help.

postgres=# create user opsdash password 'p@ssw0rd';
CREATE ROLE
postgres=# grant pg_read_all_stats to opsdash;
GRANT ROLE
postgres=# grant execute on function pg_stat_get_wal_senders to opsdash;
GRANT
postgres=# \q

The first statement creates a normal user called "opsdash", and the second grants it the " pg_read_all_stats" privilege. The third statement is required if you want to monitor replication.

Notes

  • PostgreSQL versions 9.2, 9.3, 9.4, 9.5, 9.6 and 10 are supported.
  • You can install the OpsDash Smart Agent on any server that can connect to the PostgreSQL instance -- adjust "localhost" and "127.0.0.1" accordingly in the above instructions.
  • The option db should be any valid database name.
  • You can also specify a port (port = 3306) in the service configuration block if needed.
  • The OpsDash Smart Agent will also collect and report metrics of the server itself, where it is installed. This will appear under the "Servers" section on the Sources page.