'How to get the number of queries which are executed per hour/day in PostgreSQL

Is there a way to get the number of Select/Update/Delete/Insert/Commit/Rollback queries that are executed in the last one hour or one day?

One way of doing this is by enabling the transaction logs and using Shell/Python tools to search for specific queries like this.

cat postgresql-2022-02-25_105258.log | grep -i 'statement: select' | wc -l 28888

The above command would fetch the number of select queries which ran and it is somehow helpful, but the problem with this approach is we cannot afford to restart a production server to enable the configuration setting (logging_collector = on)

Please do share if you are familiar with any other approach.

Many Thanks, Suresh.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source