Configuring PG_STAT_STATEMENTS in Azure PostgreSQL Flexible Server (4378160)

In order to set up Statement monitoring in the PostgreSQL agent monitoring Azure Database for PostgreSQL you'll need to first follow these steps to enable the pg_stat_statements extension for collecting query statistics.

A message similar to the following may appear in PostgreSQL if the extension has not been allow listed in Azure.

extension "pg_stat_statements" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL 

Enabling PG_STAT_STATEMENTS

Click To See Full Image.

1. Choose Server Parameters in the Azure PosgreSQL Flexible Server console
2. Filter by azure.extensions
3. Select PG_STAT_STATEMENTS
4. Save the changes

Note that this will reboot your database if pg_stat_statements has not been enabled before.

Load Libraries

Click To See Full Image.

1. Select your Azure Database for PostgreSQL flexible server instance.

2. From the resource menu, under Settings section, select Server parameters.

3. Include the libraries you wish to add in the value of shared_preload_libraries, and select Save.

4. Because shared_preload_librariesis a static server parameter, it requires a server restart so that the changes take effect.

Click To See Full Image.

Create Extensions

After an extension is allowlisted and, if the extension requires it, is also added to shared_load_libraries, it can be created or installed in each database on which it's to be used.

1. Connect to your database as a user with azure_pg_admin privileges (usually the credentials you created the database with), e.g. using psql.

2. Run the CREATE EXTENSION command to create or install a particular extension. This command loads the packaged objects into your database.
   CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA public;

3. Run the following query, this should return one row of query statistics information - if it gives an error you might not have restarted your database or changed the configuration correctly.
   SELECT * FROM pg_stat_statements LIMIT 1;