O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Collecting regular statistics from pg_stat* views

This recipe describes how to collect the statistics needed to understand what is going on in the database system on a regular basis, so that they can be used for further optimizing the queries which are slow or which are becoming slow as the database changes.

The code to do is in file pg_statlogs.tar.gz

Getting ready

Find the package pg_statlogs.tar.gz.

Set up a directory as shown next for running the scripts:

mkdir /opt/pg_statlogs
cd /opt/pg_statlogs
tar xzvf pg_statlogs.tgz

Set up a schema for collecting the snapshots of statistics data and generating deltas from it as follows:

psql mydatabase <./pg_statlogs_prepare.sql

How to do it...

Run the following from command line:

psql mydatabase <./pg_statlogs_collect.sql ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required