Skip to content
Percona Documentation

Get free database assistance or contact our experts for personalized support.

Get help from Percona

Set up pg_stat_monitor

After you installed pg_stat_monitor, you must set it up to use it.

1. Load the module

Load pg_stat_monitor at the start time by adding it to the shared_preload_libraries configuration parameter. This is because pg_stat_monitor requires additional shared memory.

  1. Connect to psql and modify the shared_preload_libraries parameter using the ALTER SYSTEM command. 

    ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_monitor';

    NOTE: If you’ve added other modules to the shared_preload_libraries parameter (for example, pg_stat_statements), list all of them separated by commas for the ALTER SYSTEM command.

    pg_stat_monitor must follow pg_stat_statements. For example, ALTER SYSTEM SET shared_preload_libraries = 'foo, pg_stat_statements, pg_stat_monitor'.

    Warning

    It makes sense to disable the application name tracking because it may cause perfomance degradation proportional to the number of connections.

    ALTER SYSTEM SET pg_stat_monitor.pgsm_track_application_names = 'no';

  2. Start or restart the postgresql instance to apply the changes.

    $ sudo systemctl restart postgresql.service

    $ sudo systemctl restart postgresql-XXX

    Replace the XXX with the PostgreSQL version you are using.

After you have added pg_stat_monitor to the shared_preload_libraries, it starts collecting statistics data for all existing databases. To access this data, you need to create the view on every database that you wish to monitor.

2. Create the extension view

Create the extension view with the user that has the privileges of a superuser or a database owner. Connect to psql as a superuser for a database and run the CREATE EXTENSION command:

CREATE EXTENSION pg_stat_monitor;

After the setup is complete, you can see the stats collected by pg_stat_monitor.

By default, pg_stat_monitor is created for the postgres database. To access the statistics from other databases, you need to create the extension view for every database.

Note

When you create a new database newdb, pg_stat_monitor captures the statistics metrics, yet you cannot see them because the pg_stat_monitor view is not accessible for it. You can see the metrics for the newdb database either when you query it from the existing database mydb or after you explicitly create the pg_stat_monitor view for the newdb database.

To reduce this manual work, see the How to automatically make the pg_stat_monitor view accessible for every newly created database guide.

Next steps