PostgreSQL 8.3 changed the way the Query and Index Statistics are configured from previous versions of postgreSQL including 8.2 and below. Things are technically easier now as the stats collecting is turned on by default and there are less commands to enter into the configuration to get all of the stats you will need. I had to look into this problem as I wanted to install some cacti postgres templates and the configuration stated to add the following lines to the postgresql.conf configuration file.
postgresql.conf – Query/Index Statistics Collector – Example Configurations:
- stats_start_collector = on
- stats_command_string = on
- stats_block_level = on
- stats_row_level = on
- stats_reset_on_server_start = off
Not knowing any better I added the lines and attempted to reload the config which then stopped the database on our test server. I finally came across documentation, thanks to ziz, that explains how things have changed. In the entire changelog for PostgreSQL 8.3 is described in detail.
Query and Index Stats Collector: PostgreSQL 8.2 and lower versus PostgreSQL 8.3
- stats_start_collector: This is always on so it is not necessary to configure in PostgreSQL 8.3
- stats_command_string: This is now called track_activities.
- stats_block_level: This is now combined with stats_row_level and renamed to track_counts
- stats_row_level: This is now combined with stats_block_level and renamed to track_counts
- stats_reset_on_server_start: This is no longer necessary and is not a configuration option anymore
So now the only additions you will need to make to collect all of the stats you need will be track_counts and track_activities.
Example postgresql.conf Configuration Additions:
- track_activities = on
- track_counts = on
Test PostgreSQL Stat Collection Via PSQL CLI:
- select * from pg_stat_all_tables;