Step 1: Create Monitoring User

We recommend creating a separate monitoring user on your PostgreSQL database for pganalyze.

As a user with azure_pg_admin privileges, connect to the database you will be monitoring and run the following to create a new user (we've generated a random password for you, but you can replace it with one of your choosing):

CREATE USER pganalyze WITH PASSWORD '[could not generate password]' CONNECTION LIMIT 5;
GRANT pg_monitor TO pganalyze;

CREATE SCHEMA pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO pganalyze;
GRANT USAGE ON SCHEMA public TO pganalyze;

CREATE OR REPLACE FUNCTION pganalyze.get_stat_replication() RETURNS SETOF pg_stat_replication AS
$$
  /* pganalyze-collector */ SELECT * FROM pg_catalog.pg_stat_replication;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

Note that it is important you run these as the Postgres superuser in order to pass down the full access to statistics tables.

Verify connection

Let's make sure we can connect to the database with the new user:

PGPASSWORD=[could not generate password] psql -h mydb.postgres.database.azure.com -d mydatabase -U pganalyze@mydb

Write down the username and password of the monitoring user, we will use it in the last step of this tutorial.


Next we continue by installing the pganalyze collector:

Proceed to Step 2: Install the collector

Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →