Check out our latest eBook: "Efficient Search in Rails with Postgres". We've updated our Terms of Service and Privacy Policy, effective Jan 30, 2021.

Step 1: Create Monitoring User

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

Enabling pg_stat_statements

Connect to your database with cloudsqlsuperuser privileges (i.e. the initial user that was created on instance creation), e.g. using psql.

Run the following SQL commands to enable the pg_stat_statements extension, and make sure it was installed correctly:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT * FROM pg_stat_statements LIMIT 1;

Create monitoring user

Connect to the database you will be monitoring, again as a user with cloudsqlsuperuser privileges. Then run the following to create a monitoring 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 a user with cloudsqlsuperuser privileges 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 1.2.3.4 -d mydatabase -U pganalyze

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 →