OpsDash
Search…
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/Ubuntu, CentOS/RHEL, Generic.
2. Create a user on the PostgreSQL instance for OpsDash.
1
$ sudo -u postgres psql
2
psql (9.5.5)
3
Type "help" for help.
4
5
postgres=# create user opsdash superuser unencrypted password '[email protected]';
6
CREATE ROLE
7
postgres=# \q
8
$
Copied!
3. Edit the OpsDash Smart Agent configuration file at /etc/opsdash/agent.cfg and add a new service:
1
# choose a good, unique name for the service (here my-pg-1 is used)
2
service "my-pg-1" {
3
type = "postgresql"
4
host = "localhost"
5
user = "opsdash"
7
db = "postgres"
8
}
Copied!
4. Reload the OpsDash Smart Agent so that it picks up the new configuration:
1
# on Linux:
2
sudo service opsdash-agent reload
3
4
# on Windows:
5
net stop OpsDashSmartAgent
6
net start OpsDashSmartAgent
Copied!
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.
6. If you have a replicated PostgreSQL nodes, you can configure a service on each PostgreSQL node (e.g. "pg-prod-primary" on the master node and "pg-prod-standby" on a slave node). OpsDash will automatically figure out if the node it is talking to is a primary or standby, and set up dashboards accordingly.
SSL Mode
The OpsDash Smart Agent can connect to the PostgreSQL instance using SSL also. Use the sslmodekeyword in the configuration block, like this:
1
service "my-pg-1" {
2
type = "postgresql"
3
host = "localhost"
4
user = "opsdash"
6
db = "postgres"
7
8
# sslmode, optional, one of "disable" (default) or "require"
9
sslmode = "require"
10
}
Copied!
PostgreSQL 10
The "opsdash" database user can run with fewer privileges starting with PostgreSQL v10:
1
$ sudo -u postgres psql
2
psql (10.0)
3
Type "help" for help.
4
5
postgres=# create user opsdash password '[email protected]';
6
CREATE ROLE
7
postgres=# grant pg_read_all_stats to opsdash;
8
GRANT ROLE
9
postgres=# grant execute on function pg_stat_get_wal_senders to opsdash;
10
GRANT
11
postgres=# \q
Copied!
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.
Last modified 3yr ago
Copy link