Skip to content
logo
pg_stat_monitor Documentation
Set up
Initializing search
    percona/pgsm-docs
    percona/pgsm-docs
    • Home
    • Comparison with pg_stat_statements
      • Release notes index
      • None
      • Version 2.0.1 (2023-05-22)
      • Version 2.0.0 (2023-03-20)
      • Version 1.x release notes
      • Install
      • Set up
        • 1. Load the module
        • 2. Create the extension view
        • Next steps
        • Useful links
    • Upgrade
      • Features
      • Views
      • Functions
    • Integration with Percona Monitoring and Management
      • Auto-create pg_stat_monitor for new databases
      • pg_stat_monitor view reference
      • Configuration
    • Uninstall
    • Contributing guide
    • Copyright and licensing information
    • Trademark policy
    • 1. Load the module
    • 2. Create the extension view
    • Next steps
    • Useful links

    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'.

    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¶

    • Use pg_stat_monitor.

    Useful links¶

    • Configuration
    • pg_stat_monitor view reference

    Last update: October 5, 2023
    Created: October 5, 2023

    Contact Us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.

    Back to top
    Previous Install
    Next Upgrade
    Percona LLC, © 2023
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better. Read more about Percona Cookie Policy.