SelectStar

PostgreSQL

Prerequisites

Before attempting to monitor your PostgreSQL database with SelectStar, ensure you are running PostgreSQL 9.0 or greater. Ensure you have completed setting up your PostgreSQL database for Monitoring for RDS and non-RDS

Connection Information

The following information is required by SelectStar in order to monitor to your PostgreSQL databases:

Field
API Field
Description

Database

database

Comma delimited list of database(s) in your instance to monitor

Host

host

Hostname or IP address of the system on which your database is running

Port

port

Port on which your database is listening [default: 5432]

SSL Mode

ssl_mode

Use SSL connections to your database. One of: require, disable [default], verify-ca, verify-full

Save Query Text

show_query_text

Decides if normalized query text or query IDs should be returned [default: true]

Credentials

Field
API Field
Description

Username

username

PostgreSQL user

Password

password

PostgreSQL user's password

1a. Configuring a PostgreSQL database for monitoring

To configure a PostgreSQL database you'll need to make the following configuration changes to the postgresql.conf file located on the server instance. After making the change the server will require a reboot. If you are running your database in Amazon RDS you will need to perform the these steps instead.

Query Tracking

To track queries, you'll need to edit the following policy settings:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Table Statistics

To enable additional table statistics around index usage, you'll need to edit an additional policy setting

track_counts = 1

Verify pg_stat_statements configuration

Connect to the database and run the following command to ensure configuration was successful.

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

1b. Configuring an Amazon RDS database for monitoring

When setting up an RDS database you'll need to perform the following steps in the AWS console, by either changing your databases policy settings or creating a new policy settings group and assigning it to your database. Changes will require a reboot if a new policy was assigned.

Query Tracking

To track queries, you'll need to edit the following policy settings:

Setting
Value

shared_preload_libraries

pg_stat_statements

pg_stat_statements.track

all

track_activity_query_size

2048

Table Statistics

To enable additional table statistics around index usage, you'll need to edit an additional policy setting

Setting
Value

track_counts

1

Verify pg_stat_statements configuration

Connect to your database as an RDS superuser, and run the following command to ensure configuration was successful.

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

2. Creating a Monitoring User

Setting up a ReadOnly Monitoring User

You can create a monitoring user for your database by running the following script:

CREATE SCHEMA bluemedora;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE OR REPLACE FUNCTION bluemedora.pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$
SELECT * FROM public.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.explain_this (
      l_query text,
      out explain json
    )
RETURNS SETOF json AS
$$
BEGIN
  RETURN QUERY EXECUTE 'explain (format json) ' || l_query;
END;
$$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY DEFINER
COST 100 ROWS 1000;

CREATE USER selectstaruser WITH PASSWORD 'tmppassword';
GRANT SELECT ON pg_database TO selectstaruser;
GRANT SELECT ON pg_stat_bgwriter TO selectstaruser;
GRANT SELECT ON pg_stat_database TO selectstaruser;
GRANT SELECT ON pg_stat_user_indexes TO selectstaruser;
GRANT SELECT ON pg_stat_user_tables TO selectstaruser;
GRANT SELECT ON pg_statio_all_sequences TO selectstaruser;
GRANT SELECT ON pg_statio_user_indexes TO selectstaruser;
GRANT SELECT ON pg_statio_user_tables TO selectstaruser;
GRANT SELECT ON pg_tables TO selectstaruser;
GRANT SELECT ON pg_tablespace TO selectstaruser;
GRANT SELECT ON pg_user TO selectstaruser;
GRANT SELECT ON pg_stat_replication TO selectstaruser;
GRANT SELECT ON pg_stat_database_conflicts TO selectstaruser;
GRANT SELECT ON pg_trigger TO selectstaruser;
GRANT SELECT ON pg_stat_activity TO selectstaruser;
GRANT SELECT ON pg_stat_statements TO selectstaruser;
GRANT USAGE ON SCHEMA bluemedora TO selectstaruser;
GRANT EXECUTE ON FUNCTION public.explain_this(l_query text, out explain text) TO selectstaruser;

Note

PostgreSQL databases previous to 9.2 may need to edit the create user script.

Once complete, you can ensure that user has the EXPLAIN privilege:

SELECT exists( SELECT 1 FROM information_schema.role_routine_grants WHERE routine_name='explain_this' AND privilege_type='EXECUTE' AND grantee='explainer');

This query should return a single row with a single column of "exists" with a value of "true".

Note: If a user has permissions to read from your tables and views, they do not require this special EXPLAIN permission.

Troubleshooting: Why don't my queries show up?

If you are not seeing queries in SelectStar, the first thing you should do is ensure you performed the database setup steps required for your database above. Second ensure you've created a monitoring user. Lastly here are some steps that might help:

After making the query configuration changes, you must restart your database in order for them to be correctly applied. Please see the PostgreSQL docs for an explanation of why this restart is needed.

You also need to create the extension in each database you are monitoring:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements

Although the extension pulls from all databases, it is database-bound and must be created for each database you would like to monitor.

You can check if it is installed in the database by calling:

SELECT *
FROM pg_available_extensions
WHERE name = 'pg_stat_statements' AND installed_version IS NOT NULL

To verify that your user has sufficient permissions to monitor queries, ensure that one of the following commands returns query text.

SELECT query FROM pg_stat_statements;
SELECT query FROM bluemedora.pg_stat_statements();

Troubleshooting: Where are my Tablespace metrics?

Tablespace data currently is not returned for PostgreSQL read-only users. Assigning a user or role the super user attribute will correct this limitation.