Before adding a PostgreSQL instance to SQL Monitor, you'll need to:

• Update some PostgreSQL instance settings to configure logging,
• Install some important extensions that are shipped with PostgreSQL,
• Create a foreign data wrapper that will give SQL Monitor access to the PostgreSQL log,
• Create and give permissions to a user that SQL Monitor will use to access the instance, and
• Perform some host-specific setup.

SQL Monitor can also sample PostgreSQL query plans with some further setup.

Important PostgreSQL concepts

In PostgreSQL, connections are made to specific databases within an instance, instead of generally to the instance. As such, it's useful to configure the Base Monitor to log in to a specific database when adding the instance to SQL Monitor. If unconfigured, the Base Monitor user will follow the default PostgreSQL behaviour of logging in to the database matching its username; e.g. if logging in as sqlmonitor, then the Base Monitor will attempt to log in to a sqlmonitor database. Naturally, the Base Monitor's login database must exist, and it must have permission to log in to it.

Some PostgreSQL objects queried by the Base Monitor exist within specific schemas, but are used by the Base Monitor in an unqualified way. - e.g. as pg_stat_statements rather than public.pg_stat_statements. This allows the objects to be installed into different schemas as needed for other purposes without impacting SQL Monitor. As a result, the Base Monitor user must have access to those schemas, and they must be present in its search path.

Update postgresql.conf

SQL Monitor requires certain PostgreSQL settings to have specific values:

• The pg_stat_statements extension must be included in the shared_preload_libraries comma-separated string.
• The logging collector must be enabled, and log_destination must contain 'csvlog'. SQL Monitor records log entries at WARNING level and above.
• We recommend setting `track_io_timing` to 'on' to give more a more detailed view of queries' IO performance.

• For PostgreSQL on Amazon RDS these settings can be set in Configuration tab  by editing the DB instance parameter group instead of updating the postgresql.conf  file.

shared_preload_libraries = 'pg_stat_statements'
log_destination = 'csvlog'
logging_collector = on
track_io_timing = on

You will need to restart the PostgreSQL instance for configuration changes to take effect.

Configure Base Monitor PostgreSQL user permissions

The account used to monitor your PostgreSQL instance must be a member of the pg_monitor  role. This is a standard PostgreSQL role that grants a set of monitoring-related permissions.

To monitor autovacuum information, it must currently either be a superuser, or have GRANT SELECT  permission to all tables that should be monitored for autovacuum status. (This monitoring is optional.)

• Creating the Base Monitor user, and adding the pg_monitor role, for all host types:

  CREATE USER sqlmonitor WITH PASSWORD 'somepassword';
  GRANT pg_monitor TO sqlmonitor;
  GRANT ALL ON SCHEMA public TO sqlmonitor; /* PostgreSQL v15 and higher only */

• Optionally creating a maintenance database for the Base Monitor user to log in to (needed if the user won't be granted access to another login database, such as postgres):

  CREATE DATABASE sqlmonitor;
  GRANT ALL PRIVILEGES ON DATABASE sqlmonitor TO sqlmonitor;

• Enabling optional autovacuum monitoring, for all host types:

  GRANT pg_read_all_data TO sqlmonitor; /* option 1 - PostgreSQL v14 and higher only */
  GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO sqlmonitor; /* option 2 */
  GRANT SELECT on myschema.mytable to sqlmonitor; /* option 3 */
  ALTER USER sqlmonitor WITH SUPERUSER; /* option 4 */

Create the pg_stat_statements extension

The pg_stat_statements extension must be installed on the database the Base Monitor user will log in to. pg_stat_statements is used to provide information on queries, their performance and frequency.

CREATE EXTENSION pg_stat_statements;

PostgreSQL running on a Linux machine: log file collection

To access PostgreSQL log files on Linux machines, SQL Monitor requires additional configuration:

• The file_fdw extension must be installed, and a file server named sqlmonitor_file_server must be created. 

• The Base Monitor's PostgreSQL user must be granted additional permissions.

To set up the extension and file server, as well as grant the required permissions, run the following on the login database you intend to use for the Base Monitor's PostgreSQL user. In our example this would be sqlmonitor.

CREATE EXTENSION file_fdw;
CREATE SERVER sqlmonitor_file_server FOREIGN DATA WRAPPER file_fdw;
/* replace 'sqlmonitor' in this code block with the Base Monitor PostgreSQL user */
GRANT pg_read_server_files TO sqlmonitor; /* use 'ALTER USER sqlmonitor WITH SUPERUSER;' for PostgreSQL v10 */
GRANT EXECUTE ON FUNCTION pg_catalog.pg_current_logfile(text) TO sqlmonitor;
GRANT USAGE ON FOREIGN SERVER sqlmonitor_file_server TO sqlmonitor;

PostgreSQL running in RDS (including Amazon Aurora PostgreSQL)

To access PostgreSQL log files in RDS, SQL Monitor requires additional configuration:

• The log_fdw extension must be installed, and a file server named sqlmonitor_file_server must be created. 

• The Base Monitor's PostgreSQL user must be granted additional permissions.

To set up the extension and file server, as well as grant the required permissions, run the following on the login database you intend to use for the Base Monitor's PostgreSQL user. In our example this would be sqlmonitor.

CREATE EXTENSION log_fdw; /* may already be installed */
CREATE SERVER sqlmonitor_file_server FOREIGN DATA WRAPPER log_fdw;

/* replace 'sqlmonitor' in the following code block with the SQL Monitor PostgreSQL monitoring user */
GRANT EXECUTE ON FUNCTION list_postgres_log_files() TO sqlmonitor;
GRANT EXECUTE ON FUNCTION create_foreign_table_for_log_file(text, text, text) TO sqlmonitor;
GRANT USAGE ON FOREIGN SERVER sqlmonitor_file_server TO sqlmonitor;

Additional steps:

1. Establish a server-level firewall rule that allows communication between the Base Monitor and the Amazon RDS PostgreSQL instance (see Summary of firewall requirements).
2. Add them using the Adding PostgreSQL link in the web interface (Configuration > Monitored Servers). You'll need the name of the PostgreSQL instance with the fully qualified address (which for Amazon RDS will contain the string 'amazonaws').
3. Enter PostgreSQL server credentials, and your Amazon AWS API access key and secret key to monitor the underlying host.
4. Ensure that the monitored instance or cluster has enhanced monitoring enabled: this is used to collect data on memory usage, data storage usage and running processes within the system's operating system.