Monitor PostgreSQL metrics with pgwatch2¶
pgwatch2 is an open source monitoring solution for PostgreSQL, created by CYBERTEC and can be used to monitor instances of Aiven for PostgreSQL collecting key PostgreSQL metrics and also gathering data from a wide range of PostgreSQL extensions.
Aiven for PostgreSQL supports the most popular extensions, but, due to security implications, can’t support all the available extensions. Check List of available extensions and Install or update an extension for details on the supported PostgreSQL extensions on the Aiven platform.
Prepare an Aiven for PostgreSQL instance for pgwatch2¶
The following steps need to be executed on the Aiven for PostgreSQL instance to be monitored with pgwatch2:
Create a user for pgwatch2 (with a sensible password):
CREATE USER pgwatch2 WITH PASSWORD 'password';
Limit the number of connections from pgwatch2 (optional, but recommended in the pgwatch2 documentation):
ALTER ROLE pgwatch2 CONNECTION LIMIT 3;
Allow pgwatch2 to read database statistics:
GRANT pg_read_all_stats TO pgwatch2;
If you want to collect data gathered from the
pg_stat_statementsextension, it needs to be enabled:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
docker run --rm -p 3000:3000 -p 8080:8080 \ -e PW2_ADHOC_CONN_STR='postgres://pgwatch2:password@HOST:PORT/defaultdb?sslmode=require' \ -e PW2_ADHOC_CONFIG='rds' --name pw2 cybertec/pgwatch2-postgres
This runs pgwatch2 with the container image provided by CYBERTEC.
PW2_ADHOC_CONN_STR is set to the connection string of the PostgreSQL instance to be monitored, copied from the Aiven web console replacing the username/password have been replaced by the ones specifically created for pgwatch2. Please consult the pgwatch2 documentation to decide on the best way to set up pgwatch2 in your environment.
pgwatch2 contains several dashboards that rely on extensions not available in Aiven for PostgreSQL, so it is to be expected that some dashboards are either empty or display error symbols.