PostgreSQL provides detailed monitoring information using extensions that are not typically enabled and configured by default. Therefore, before connecting Redgate Monitor to your PostgreSQL instance, there are two areas that need to be configured first, regardless of whether you self-host your database or use a cloud provider.

This page lists concrete steps to get a PostgreSQL instance ready for monitoring. The setting values shown are a good general starting point. However, some suggested values make tradeoffs between data richness and performance overhead, so may not be the correct balance in all cases. We recommend you read the additional guidance on configuration choices to further understand what each setting does and if it makes sense to modify some of them for your specific environment.

Configure extensions, logging, and other settings (steps 1-3)

If you are unsure how to change the following configuration settings, see the instructions for each platform.

Step 1: Enable extensions and logging

Change the following configuration settings either directly in the postgresql.conf file, or via your cloud service configuration, to enable essential extensions and logging:

shared_preload_libraries = 'pg_stat_statements,auto_explain'
logging_collector = on
log_destination = 'csvlog'

The logging collector is enabled by default in parameter groups for Amazon RDS and Aurora Postgres

On Cloud SQL for PostgreSQL you do not edit shared_preload_libraries, logging_collector or log_destination directly. Instead:

pg_stat_statements is preloaded by default, so no change is needed to load it (you still create the extension in Step 5, and tune it with pg_stat_statements.track in Step 2).
auto_explain is enabled by setting the cloudsql.enable_auto_explain database flag to on. The auto_explain.* settings in Step 2 then take effect.
Cloud SQL streams server logs to Google Cloud Logging automatically, so the logging-collector / CSV-log file mechanism does not apply. Redgate Monitor reads these logs through the Cloud Logging API – see Step 5.

Step 2: Tune data collection

Change the following configuration settings either directly in the postgresql.conf file, or via your cloud service configuration, to tune what data is captured, adapting them as needed based on the guidance.

Pay special attention to the autoexplain.log_min_duration and autoexplain.sample_rate values, which are important for getting the most value from query execution monitoring.

# Essential
auto_explain.log_format = json
auto_explain.log_level = LOG # This setting may not be configurable depending on the cloud provider or PG version; it will default to `LOG` in those cases

# Throttle which explain plans are recorded
auto_explain.log_min_duration = 2000
auto_explain.sample_rate = 1.0

# Configure explain plan detail level -- some settings may not be configurable depending on the cloud provider or PG version
auto_explain.log_verbose = true
auto_explain.log_nested_statements = true
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_timing = true
auto_explain.log_triggers = true
auto_explain.log_wal = true
auto_explain.log_settings = true

# Capture I/O performance
track_io_timing = true

# Ensure the pg_stat_statements extension captures data
pg_stat_statements.track = top

On Cloud SQL for PostgreSQL, the auto_explain.* settings above only take effect once cloudsql.enable_auto_explain is set to on (see Step 1). Leave the instance's log_line_prefix at its Cloud SQL default – Redgate Monitor parses the default format, so it should not be customised.

Step 3: Restart to apply changes

Restart the PostgreSQL instance for those changes to take effect:

This is the only time a restart is required, but in some environments, particularly those without High Availability configured, an appropriate maintenance window may be needed. Note that while changes made in Step 1 can only be applied by restarting the PostgreSQL instance, changes in Step 2 can be applied without a restart by executing pg_reload_conf(), providing a way to iteratively improve those settings without additional downtime.

Connect to the host over an SSH terminal and run:

sudo systemctl restart postgresql

In the AWS console, navigate to the RDS service, ensuring you are viewing the correct AWS region.
In the left-hand navigation, select the Database menu item to display a list of instances.
Select the instance you wish to restart.
Click the Actions menu item, and choose Reboot.

Note that for Amazon Aurora, each instance in the cluster must be rebooted.

More guidance on rebooting approaches for Amazon Aurora is available here.

In the Azure portal, navigate to the list of Flexible Servers for PostgreSQL.
Select the instance you wish to restart.
In the actions bar at the top of the overview page, click "Restart".

On Cloud SQL, applying or changing the database flags from Step 1 restarts the instance automatically, so a separate manual restart is usually not required. To restart the instance explicitly, use either of the methods below.

In the Google Cloud console, navigate to the list of Cloud SQL instances, ensuring you have the correct project selected.
Select the instance you wish to restart.
In the actions bar at the top of the overview page, click "Restart", and confirm.

Alternatively, restart the instance with the gcloud CLI, replacing the INSTANCE_NAME and PROJECT_ID parameters:

gcloud sql instances restart INSTANCE_NAME --project=PROJECT_ID

Grant access and permissions (steps 4-6)

Step 4: Create monitoring database and role

As a superuser, connect to your PostgreSQL instance and run the following queries to create a utility database alongside a login for monitoring with basic permissions applied.

--Create the utility database
CREATE DATABASE redgatemonitor;

--Create the user with basic monitoring permissions
CREATE USER redgatemonitor WITH PASSWORD 'Y0uRp@s$w0rD';
GRANT pg_monitor TO redgatemonitor;
GRANT ALL PRIVILEGES ON DATABASE redgatemonitor TO redgatemonitor;

Managed platforms such as Amazon RDS, Azure Flexible Server and Google Cloud SQL do not provide a true superuser. Connect instead as the default administrative user created with the instance (for example postgres on Cloud SQL); it has sufficient privileges to run the statements above.

Step 5: Configure and grant access to richer monitoring data

As a superuser, connect specifically to the "redgatemonitor" utility database you created above, and run the following script to provide access to query and log data via extensions, being careful to choose the right platform:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE SERVER sqlmonitor_file_server FOREIGN DATA WRAPPER file_fdw;
GRANT pg_read_server_files TO redgatemonitor;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_current_logfile(text) TO redgatemonitor;
GRANT USAGE ON FOREIGN SERVER sqlmonitor_file_server TO redgatemonitor;
GRANT ALL PRIVILEGES ON SCHEMA public TO redgatemonitor;

If you add your Aurora PostgreSQL cluster with Automatically monitor cluster instances switched on and host monitoring enabled with AWS API credentials, Redgate Monitor reads each instance's log files through the RDS API instead of the log_fdw extension. In that case, only the following is required:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
GRANT ALL PRIVILEGES ON SCHEMA public TO redgatemonitor;

The full script below, including log_fdw, is required for standard Amazon RDS instances and for Aurora clusters monitored without auto-discovery.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS log_fdw;
CREATE SERVER sqlmonitor_file_server FOREIGN DATA WRAPPER log_fdw;
GRANT EXECUTE ON FUNCTION list_postgres_log_files() TO redgatemonitor;
GRANT EXECUTE ON FUNCTION create_foreign_table_for_log_file(text, text, text) TO redgatemonitor;
GRANT USAGE ON FOREIGN SERVER sqlmonitor_file_server TO redgatemonitor;
GRANT ALL PRIVILEGES ON SCHEMA public TO redgatemonitor;

Before running the SQL below to create the pg_stat_statements extension, you will need to add the extension to the server's allow list by following Microsoft's documentation.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The following additional steps will enable monitoring of error logs and query plans, but will also require host monitoring to be enabled for the Azure Flexible Server.

In the Azure portal, enable server logs for your PostgreSQL Flexible Server instance.
1. Using the Azure portal, under the Monitoring section of your instance page in the Azure portal, select Server logs and mark the Capture logs for download checkbox. Then press Save.
2. Alternatively, you can run the following Azure CLI command, replacing the resource_group and server parameters.
```
az postgres flexible-server parameter set \
  --resource-group <resource_group> \
  --server-name <server> \
  --name logfiles.download_enable \
  --value on
```
On the left hand navigation, select the Settings > Server parameters page.
Change the value of the log_line_prefix parameter to: %c<||>%l<||>%n<||>%u<||>%d<||>%p<||>%i<||>%v<||>%x<||>%e<||>%a<||>%b<||>%q<||>

In the "redgatemonitor" utility database, create the pg_stat_statements extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Cloud SQL for PostgreSQL does not expose its log files over SQL, so the file_fdw / log_fdw approach used for other platforms does not apply. Instead, Cloud SQL streams error logs and query execution plans to Google Cloud Logging, and Redgate Monitor reads them through the Cloud Logging and Cloud Monitoring APIs. This requires host monitoring to be enabled for the Cloud SQL instance.

To enable host monitoring, provide Redgate Monitor with a Google Cloud service account key (JSON). Grant the service account the following IAM roles on the project that contains the Cloud SQL instance:

roles/cloudsql.viewer – to discover the Cloud SQL instance and its metadata.
roles/monitoring.viewer – to read the instance's host and database metrics.
roles/logging.viewer – to read the PostgreSQL error logs and query execution plans.

You can grant these roles with the gcloud CLI, replacing PROJECT_ID and the service account email:

for ROLE in roles/cloudsql.viewer roles/monitoring.viewer roles/logging.viewer; do
  gcloud projects add-iam-policy-binding PROJECT_ID \
    --member="serviceAccount:redgatemonitor@PROJECT_ID.iam.gserviceaccount.com" \
    --role="$ROLE"
done

Step 6 (optional): Provide access to vacuum health data

Vacuum health information can only be collected for tables to which Redgate Monitor has read access. There are two ways to provide this access, depending on which version of PostgreSQL you are using.

This step can be skipped, but at the expense of collecting information about vacuum health.

As a superuser, connect to any database and run:

GRANT pg_read_all_data TO redgatemonitor;

As a superuser, connect in turn to each database which you want Redgate Monitor to capture vacuum health data from. Then run the following for any Schemas or Tables in that database for which you want Redgate Monitor to collect vacuum health data:

Either (collect data for all tables within schema)

--Grant permissions for an entire schema within the database:
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO redgatemonitor;

Or (collect data for only specific tables)

--Or alternatively selectively grant permissions for an individual table:
GRANT SELECT on myschema.mytable to redgatemonitor;

Redgate Monitor 14

Preparing PostgreSQL for monitoring