PostgreSQL Background and foreground Process Details

PostgreSQL Background and foreground Process Details

Following are some of the important Process of PostgreSQL/Postgres Plus Advanced Server.

vibhore@ubuntu:~/edb/edb-postgres$ ps -eaf|grep postgre
1002      1080     1  0 06:07 ?        00:00:00 /opt/PostgresPlus/8.4AS/bin/edb-postgres -D /opt/PostgresPlus/8.4AS/data (Mandatory)
1002      1101  1080  0 06:07 ?        00:00:00 postgres: logger process   (Mandatory)                                              
1002      1103  1080  0 06:07 ?        00:00:01 postgres: writer process                                               (Mandatory)
1002      1104  1080  0 06:07 ?        00:00:01 postgres: wal writer process (Mandatory)                                            
1002      1105  1080  0 06:07 ?        00:00:00 postgres: autovacuum launcher process  (Optional if autovacuum is on)                                 
1002      1106  1080  0 06:07 ?        00:00:00 postgres: stats collector process      (Mandatory)

Above is an snapshot of the processes which exist in PostgreSQL/Postgres Plus Advanced Server.
Postmaster: is a superior process among others its the process which keep monitor the other process and also spawns new process if user request comes to it. It is the process which is responsible for Host Based Authentication and User Based Authentication. This process also checks the consistency of database and accordingly does the recovery of PostgreSQL Instance.


BG writer process: This is a BG (Back Ground) Writer process. This process writes dirty shared buffer to Disk on following events:
1. When Checkpoint occurs.
2. When Checkpoint timeout happens
3. When WAL Writter finishes the number segments mention in Checkpoint_segments.
4. When there is no space left in shared buffer for new Blocks.

Parameters which can help in controlling the activity of BG Writter activities if given below:
1. bgwriter_delay (integer): This parameter specifies delay between activity rounds for the background writer. In each round the writer issues writes for some number of dirty buffers (controllable by the following parameters). It then sleeps for bgwriter_delay milliseconds, and repeats.
2. bgwriter_lru_maxpages (integer): This parameter specifies max number of buffers will be written to disk in each round. Setting this to zero disables background writing (except for checkpoint activity).
3. bgwriter_lru_multiplier (floating point): This parameter use in estimating of the number of buffers that will be needed during the next round. Formula which use is given below:
(average recent need) * bgwriter_lru_multiplier
Dirty buffers are written until there are ((average recent need) * bgwriter_lru_multiplier) many clean, reusable buffers available.

wal writer process: This process writes all the transaction from WAL Buffer to WAL files in pg_xlog. Parameters which can control the WAL Writter is given below. It is similar to log writer in Oracle (which flush redo log buffer to redo logfile). Parameter which can be use to control the behavior of WAL Writer can be found in following link:
http://www.postgresql.org/docs/8.3/static/runtime-config-wal.html

stats collector process:
The collector can count accesses to tables and indexes in both disk-block and individual-row terms. It also tracks the total number of rows in each table, and information about vacuum and analyze actions for each table. It can also count calls to user-defined functions and the total time spent in each one.
Information of paramter which can be use to control its activity can be find in following link:
http://www.postgresql.org/docs/8.3/static/runtime-config-statistics.html

Logger Process: This process is responsible for logging the details of activity from the startup to shutdown of the PostgreSQL Instance. Logging behavior of this process can be control by following catagories of parameters:
Category: Where to Log: Parameters:
1. log_destination (string): This parameter tells logger process about methods for logging server messages, including stderr, csvlog and syslog.
2. logging_collector (boolean): This parameter allows messages sent to stderr, and CSV-format log output, to be captured and redirected into log files.
3. log_directory (string): This parameter specifies directory of logfile.
4. log_filename (string): When logging_collector is enabled, this parameter sets the file names of the created log files. The value is treated as a strftime pattern, so %-escapes can be used to specify time-varying file names. 
5. log_rotation_age (integer): This parameter determines the maximum lifetime of an individual log file.After this many minutes have elapsed, a new log file will be created. Set to zero to disable time-based creation of new log files. This parameter can only be set in the postgresql.conf file or on the server command line.
6. log_rotation_size (integer): this parameter determines the maximum size of an individual log file. After this many kilobytes have been emitted into a log file, a new log file will be created.
7. log_truncate_on_rotation (boolean): This parameter will cause PostgreSQL to truncate (overwrite), rather than append to, any existing log file of the same name. However, truncation will occur only when a new file is being opened due to time-based rotation, not during server startup or size-based rotation.
8. syslog_facility (enum): This parameter determines the syslog "facility" to be used. You can choose from LOCAL0, LOCAL1, LOCAL2, LOCAL3, LOCAL4, LOCAL5, LOCAL6, LOCAL7; the default is LOCAL0. See also the documentation of your system's syslog daemon. 
9. syslog_ident (string): This parameter determines the program name used to identify PostgreSQL messages in syslog logs. The default is postgres.
10. silent_mode (boolean): This parameter is set, the server will automatically run in background and disassociate from the controlling terminal. This parameter can only be set at server start.

Category: When to Log: Parameters: 
Following are parameters which controls the message level:
1. client_min_messages (enum)
2. log_min_messages (enum)
3. log_error_verbosity (enum)
4. log_min_error_statement (enum)
5. log_min_duration_statement (integer)
Detail about the above parameter values can be found in following link:
http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS

Category: What to Log: Parameters:
1. Debug Parameters:
debug_print_parse (boolean)
debug_print_rewritten (boolean)
debug_print_plan (boolean) 
These parameters enable various debugging output to be emitted. When set, they print the resulting parse tree, the query rewriter output, or the execution plan for each executed query.
2. debug_pretty_print (boolean): This indents the messages produced by debug_print_parse, debug_print_rewritten, or debug_print_plan. This results in more readable but much longer output than the "compact" format used when it is off.
3. log_checkpoints (boolean): This checkpoints to be logged in the server log. Some statistics about each checkpoint are included in the log messages, including the number of buffers written and the time spent writing them.
4. log_connections (boolean): This parameter causes each attempted connection to the server to be logged, as well as successful completion of client authentication.
5. log_disconnections (boolean): This outputs a line in the server log similar to log_connections but at session termination, and includes the duration of the session
6. log_duration (boolean): This parameter causes the duration of every completed statement to be logged.
7. log_lock_waits (boolean): It controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock.
8. log_statement (enum): It controls which SQL statements are logged. Valid values are none, ddl, mod, and all. ddl logs all data definition statements, such as CREATE, ALTER, and DROP statements. mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type.
9. log_temp_files (integer): It controls logging of use of temporary files. Temporary files can be created for sorts, hashes, and temporary query results. A log entry is made for each temporary file when it is deleted. 
10. log_timezone (string): It sets the time zone used for timestamps written in the log.Unlike timezone, this value is cluster-wide, so that all sessions will report timestamps consistently.

autovacuum process:
Autovacuum is a process, which performs following activity:
1. To recover or reuse disk space occupied by updated or deleted rows.
2. To update data statistics used by the PostgreSQL query planner.
3. To protect against loss of very old data due to transaction ID wraparound.