Connecting to a PostgresPro database using the pgpro_stats extension [*]

PostgresPro databases can use pg_stat_statements or pgpro_stats to collect query statistics.

If you are using the pg_stat_statements extension, please ignore this instruction.

Important

If you have the PostgresPro Standard version, download and install the pgpro-stats-std extension of the corresponding version from the PostgresPro repository.

If you have PostgresPro Enterprise, the extension is already included in the distribution.

Warning

The platform works with the pgpro_stats extension version 1.7 and above.

1. Edit postgresql.conf:

   shared_preload_libraries = 'pgpro_stats'
   pgpro_stats.track_planning = on
   pgpro_stats.plan_format = 'json'
   

2. Restart the monitored DBMS to apply the new parameters.

3. Create an extension:

   CREATE EXTENSION pgpro_stats;
   

4. Make sure there are no conflicts in the view. To do this, execute the command:

   select pgpro_stats_create_pg_stat_statements_compatible_views();
   

   Warning

   Remove unnecessary views, if after executing the command you receive the message:

   Please drop extension pg_stat_statements and remove it from
   the shared_preload_libraries.
   If the above is already done and the emulated pg_stat_statements
   and/or  pg_stat_statements_info views have been previousely created
   use: "drop view pg_stat_statements;" and "drop view
   pg_stat_statements_info;"commands.
   

   To remove views, execute the following commands:

   drop view pg_stat_statements;
   drop view pg_stat_statements_info;
   

5. Create a view:

   • for pgpro_stats version 1.7:

     CREATE OR REPLACE VIEW pg_store_plans
     AS
     SELECT userid,
     dbid,
     queryid,
     planid,
     plan,
     plans AS calls,
     total_plan_time::double precision AS total_time,
     rows,
     shared_blks_hit,
     shared_blks_read,
     shared_blks_dirtied,
     shared_blks_written,
     local_blks_hit,
     local_blks_read,
     local_blks_dirtied,
     local_blks_written,
     temp_blks_read,
     temp_blks_written,
     blk_read_time,
     blk_write_time,
     temp_blk_read_time,
     temp_blk_write_time,
     '2000-01-01 00:00:00+00'::timestamp with time zone AS first_call,
     now()::timestamp with time zone AS last_call
     FROM pgpro_stats_statements;
     

   • for pgpro_stats version 1.8:

     CREATE OR REPLACE VIEW pg_store_plans
     AS
     SELECT userid,
     dbid,
     queryid,
     planid,
     plan,
     plans AS calls,
     total_plan_time::double precision AS total_time,
     rows,
     shared_blks_hit,
     shared_blks_read,
     shared_blks_dirtied,
     shared_blks_written,
     local_blks_hit,
     local_blks_read,
     local_blks_dirtied,
     local_blks_written,
     temp_blks_read,
     temp_blks_written,
     shared_blk_read_time as blk_read_time,
     shared_blk_write_time as blk_write_time,
     temp_blk_read_time,
     temp_blk_write_time,
     '2000-01-01 00:00:00+00'::timestamp with time zone AS first_call,
     now()::timestamp with time zone AS last_call
     FROM pgpro_stats_statements;
     

6. Restart Platform agent:

   sudo systemctl restart pmaagent