TutorialDBA - Support | Training | Consultant

What is pg_stat_activity? pg_stat_activity is a PostgreSQL system view that is a good first place to start when you want to find out what is going on with your PostgreSQL installation. Aside 1 If you need to make a connection to a PostgreSQL installation, a good first place to start is username postgres dbname postgres Any default PostgreSQL install will have both this user and database. Tool makers know this convention, and design tools that connect using these defaults. http://www.postgresql.org/docs/9.4/interactive/creating-cluster.html Aside 2 Here's how you list all the databases in a PostgreSQL cluster: mwood@mwod-ThinkPad-X220:~$ psql -U postgres -l List of databases ┌───────────┬──────────┬──────────┬─────────┬───────┬───────────────────────┐ │ Name │ Owner │ Encoding │ Collate │ Ctype │ Access privileges │ ├───────────┼──────────┼──────────┼─────────┼───────┼───────────────────────┤ │ postgres │ postgre...

The above methods are good, but lack a consolidated view.This is a module built within postgres itself, but disabled by default.We can enable this by doing create extension pg_stat_statementsOnce this is enabled, after a fair amount of queries are run, then we can fire a query such as below.Gives lot of details on how much time queries took and their average.The disadvantage with this approach is it takes some amount of performance, so it is not generally recommended in production systems The pg_stat_statements module provides a means for tracking execution statistics of all SQL statements executed by a server. The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is needed to add or remove the module. When pg_stat_statements is loaded, it tracks statistics across all databases of the server. To access and manipulate these statistics, the module provides ...