pg_statsinfo 1.2

Build

First, build pg_statsinfo. Required PostgreSQL source or PostgreSQL RPM. please confirm whether the following opration is finished.

Build by using PostgreSQL source

There is a Postgresql source tree (already make)
dblink, pgcrypto, adminpack in /contrib are alread make && make install
In case of using PostgreSQL8.4, pg_stat_statements also be already make && make install

Build by using PostgreSQL RPM

postgresql-devel-*.rpma is already installed

Build by using PostgreSQL source

Unpacking pg_statsinfo-*.tar.gz and move pg_statsinfo-* directory into $PGSRC/contrib. ($PGSRC is top directory of Postgresql source tree.)

$ tar zxvf pg_statsinfp-*.tar.gz -C $PGSRC/contrib

DO "make && make install" in target_db and stats_info directory.

$ cd $PGSRC/contrib/pg_statsinfo-*/target_db
$ make && make install
$ cd $PGSRC/contrib/pg_statsinfo-*/stats_info
$ make && make install

Build by using PostgreSQL RPM

Unpacking pg_statsinfo-*.tar.gz.

$ tar zxvf pg_statsinfo-*.tar.gz -C /some/where/

DO "make && make install" with "USER_PGXS=1" in target_db and stats_info directory.

$ cd $PGSRC/contrib/pg_statsinfo-*/target_db
$ make USER_PGXS=1 && make USER_PGXS=1 install
$ cd $PGSRC/contrib/pg_statsinfo-*/stats_info
$ make USER_PGXS=1 && make USER_PGXS=1 install

Set path

Set $PATH to target_db/pg_get_logfillter and reporter/pg_make_report.pl. In addition, please put pg_make_report.pl and pg_report_config.pm to same directory. Following expample is a case we put pg_get_logfillter to $LOGFILTER and put reporter-files to $REPORT.

## In case of built with PostgreSQL source
$ cp $PGSRC/contrib/pg_statsinfo-*/target_db/pg_get_logfilterinfo $LOGFILTER
$ cp $PGSRC/contrib/pg_statsinfo-*/reporter/* $REPORT
$ export PATH=$LOGFILTER:$REPORT:$PATH

## In case of built with PostgreSQL RPM
$ cp /some/where/pg_statsinfo-*/target_db/pg_get_logfilterinfo $LOGFILTER
$ cp /some/where/pg_statsinfo-*/reporter/* $PGREPORT
$ export PATH=$LOGFILTER:$PGREPORT:$PATH

Create snapshot DB and set up

Create DB for storing snapshot and register some functions.

$ createdb stats

$ psql -f $PGHOME/share/contrib/dblink.sql -d stats
$ psql -f $PGHOME/share/contrib/pgcrypto.sql -d stats
$ psql -f $PGHOME/share/contrib/stats_info.sql -d stats

Set up for Target DB

Register adminpack, target_db modlues on Traget DB.In case of using ver1.1, pg_stat_statements also have to be installed.

$ psql -f $PGHOME/share/contrib/adminpack.sql -d target
$ psql -f $PGHOME/share/contrib/target_db.sql -d target
(If PostgreSQL8.4 is used, do following)
$ psql -f $PGHOME/share/contrib/pg_stat_statements.sql -d target

Set postgresql.conf

Edit following parameters in postgresql.conf or you can not get a part of information about checkpoint and autovacuum.

shared_preload_libraries = 'pg_stat_statements' # (required PG ver8.4 -)
log_destination = 'syslog, stderr' # (required if you want to get checkpoint & autovacuum info)
logging_collector = off # (required if you want to get checkpoint & autovacuum info)
log_checkpoints = on # (required if you want to get checkpoin info)
track_functions = all# (required PG ver8.4 -)
log_autovacuum_min_duration = 20s # (autovacuum info logged if ran for at least the specified value)

Set log filter

Set log filter and start (or restart) PostgreSQL as following.

$ pg_ctl -D $PGDATA start | pg_get_logfilter --postgres-datadir=$PGDATA &

Project Top page Previous page