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 / alloydbsuperuser 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 / alloydbsuperuser 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;
If you enable the optional reset mode (usually not required), you will also need this helper method:
CREATE OR REPLACE FUNCTION pganalyze.reset_stat_statements() RETURNS SETOF void AS
$$
/* pganalyze-collector */ SELECT * FROM public.pg_stat_statements_reset();
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
Then, connect to each database that you plan to monitor on this server as a superuser (or equivalent) and run the following to enable the collection of additional column statistics:
CREATE SCHEMA IF NOT EXISTS pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO pganalyze;
CREATE OR REPLACE FUNCTION pganalyze.get_column_stats() RETURNS SETOF pg_stats AS
$$
/* pganalyze-collector */ SELECT schemaname, tablename, attname, inherited, null_frac, avg_width,
n_distinct, NULL::anyarray, most_common_freqs, NULL::anyarray, correlation, NULL::anyarray,
most_common_elem_freqs, elem_count_histogram
FROM pg_catalog.pg_stats;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
Note: We never collect actual table data through this method (see the NULL
values in the function), but we do collect statistics about the distribution of values in your tables. You can skip creating the get_column_stats
helper function if the database contains highly sensitive information and statistics about it should not be collected. This will impact the accuracy of Index Advisor recommendations.
Note that it is important you run these as a user with cloudsqlsuperuser / alloydbsuperuser 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 collectorCouldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →