After you installed
pg_stat_monitor, you must set it up to use it.
1. Load the module¶
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.
psqland modify the
shared_preload_librariesparameter 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_librariesparameter (for example,
pg_stat_statements), list all of them separated by commas for the
pg_stat_statements. For example,
ALTER SYSTEM SET shared_preload_libraries = 'foo, pg_stat_statements, pg_stat_monitor'.
Start or restart the
postgresqlinstance to apply the changes.
$ sudo systemctl restart postgresql.service
$ sudo systemctl restart postgresql-XXX
XXXwith 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 is created for the
postgres database. To access the statistics from other databases, you need to create the extension view for every database.
When you create a new database
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
To reduce this manual work, see the How to automatically make the
pg_stat_monitor view accessible for every newly created database guide.
Created: October 5, 2023