PostgreSQL Logger Process

The logger process is a background process that captures log messages sent to stderr and redirects them into log files.
The process works when logging_collector parameter is enabled.

Set the following parameters in postgresql.conf file and restart the cluster to enable logging.

log_destination = ‘stderr’
logging_collector = on
log_directory = ‘log’
log_filename = ‘alert_postgresql.log’

There are few more parameters for logging can be found in postgresql.conf file under the following sub sections.

Please go thru the the file to understand more.

• What to log
• When to log
• where to log

PostgreSQL: stats collector

The stats collector process collects statistics about the database. It’s an optional process with the default value as on.

The process keeps track of access to tables and indexes in both disk-block and individual row-terms.

It also keeps track of record counts for tables, and tracks the vacuum and analyze actions.

The data collected is stored in a set of tables and we can access this via a number of views provided.

The views start with pg_stat.

\d pg_stat

Hitting the Tab key twice will list all the views, as shown in the following command:

postgres=# \d pg_stat
pg_stat_activity
pg_stat_all_indexes
pg_stat_all_tables
pg_stat_archiver
pg_stat_bgwriter
pg_stat_database
pg_stat_database_conflicts
pg_stat_gssapi
pg_statio_all_indexes
pg_statio_all_sequences
pg_statio_all_tables
postgres=# \d pg_stat

The stats collector collects the information based on the below parameters set inpostgresql.conf file

1. track_activities: This parameter enables monitoring of the current command being executed by any server process.
2. track_counts: This parameter controls whether statistics are collected about table and index accesses.
3. track_functions: This parameter enables tracking of usage of user-defined functions.
4. track_io_timing: This parameter enables monitoring of block read and write times.

The statistics collector transmits the collected information to other PostgreSQL processes through temporary files. These files are stored in the directory named by the stats_temp_directory parameter, pg_stat_tmp by default.

For better performance, stats_temp_directory can be pointed at a RAM-based file system, decreasing physical I/O requirements.

When the server shuts down cleanly, a permanent copy of the statistics data is stored in the pg_stat sub directory, so that statistics can be retained across server restarts. When recovery is performed at server start (e.g. after immediate shutdown, server crash, and point-in-time recovery), all statistics counters are reset.

PostgreSQL Autovacuum launcher

Autovacuum launcher is an optional process and it is enabled by default in PostgreSQL. This process automates the execution of vacuum and analyzes commands based on a few parameters.

If autovacuum is set, then it will wake up every autovacuum_naptime seconds, and decide whether to run VACUUM, ANALYZE, or both.

But what is a vacuum?

You can read in detail about a vacuum in PostgreSQL

The following are the few relevant parameters that can be set in postgresql.conf file for autovacuum.(refer postgresql.conf file for more details)

autovacuum = on
log_autovacuum_min_duration = -1
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
autovacuum_multixact_freeze_max_age =
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = -1