pg_statsinfo 1.2

Install by using RPM

First, please confirm whether the following opration is finished.

postgresql-contrib-*.rpm is already installed

Install RPM packeage by root user.

# rpm -ivh pg_statsinfo-target_db-*.rpm
# rpm -ivh pg_statsinfo-stats_info-*.rpm
# rpm -ivh pg_statsinfo-reporter-*.rpm

Set $PATH to 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 reporter-files to $PGREPORT.

$ cp /usr/share/pgsql/contrib/pgstatsinfo/pg_* $PGREPORT
$ export PATH=$PGREPORT:$PATH

Create snapshot DB and set up

Create DB for storing snapshot and register some functions.

$ createdb stats

$ psql -f /usr/share/pgsql/contrib/dblink.sql -d stats
$ psql -f /usr/share/pgsql/contrib/pgcrypto.sql -d stats
$ psql -f /usr/share/pgsql/contrib/pgstatsinfo/stats_info.sql -d stats

Set up for Target DB

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

$ psql -f /usr/share/pgsql/contrib/adminpack.sql -d target
$ psql -f /usr/share/pgsql/contrib/pgstatsinfo/target_db.sql -d target
(If PostgreSQL8.4 is used, do following)
$ psql -f /usr/share/pgsql/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_logfilterinfo --postgres-datadir=$PGDATA &