A.Many Ways to set postgresql parameters
•Some parameters can be changed per session using the SETcommand.
•Some parameters can be changed at the user level using ALTER USER.
•Some parameters can be changed at the database level using ALTER DATABASE.
•The SHOW command can be used to see settings.
•The pg_settings and pg_file_settings catalog table lists settings information.
B.PostgreSQL Parameters(The following are most common parameters in postgresql.conf)
1.Connection Settings
| Parameter | Default Value | Description |
| listen_addresses | localhost | Specifies the addresses on which the server is to listen for connections. Use * for all. |
| port | 5432 | The port the server listens on. |
| max_connections | 100 | Maximum number of concurrent connections the server can support. |
| superuser_reserved_connections | 3 | Number of connection slots reserved for superusers. |
| unix_socket_directory | /tmp | Directory to be used for UNIX socket connections to the server |
| unix_socket_permissions | 0777 | access permissions of the Unix-domain socket. |
2.Security and Authentication Settings
| Parameter | Default Value | Description |
| authentication_timeout | 1 minute | Maximum time to complete client authentication, in seconds. |
| ssl | off | Enables SSL connections. |
| ssl_ca_file | Specifies the name of the file containing the SSL server certificate authority (CA). | |
| ssl_cert_file | Specifies the name of the file containing the SSL server certificate. | |
| ssl_key_file | Specifies the name of the file containing the SSL server private key. | |
| ssl_ciphers | List of SSL ciphers that may be used for secure connections. |
3.Memory Settings
| Parameter | Default Value | Description |
| shared_buffers | 128 MB | Size of PostgreSQL shared buffer pool for a cluster. |
| temp_buffers | 8 MB | Amount of memory used by each backend for caching temporary table data. |
| work_mem | 4 MB | Amount of memory used for each sort or hash operation before switching to temporary disk files. |
| maintenance_work_mem | 64 MB | Amount of memory used for each index build or VACUUM. |
| temp_file_limit | 1 | amount of disk space that a session can use for temporary files. A transaction attempting to exceed this limit will be cancelled. Default is unlimited. |
4.Query Planner Settings
| Parameter | Default Value | Description |
| random_page_cost | 4.0 | Estimated cost of a random page fetch, in abstract cost units. May need to be reduced to account for caching effects. |
| seq_page_cost | 1.0 | Estimated cost of a sequential page fetch, in abstract cost units. May need to be reduced to account for caching effects. Must always set random_page_cost >= seq_page_cost |
| effective_cache_size | 4 GB | Used to estimate the cost of an index scan. Rule of thumb is 75% of system memory. |
5.Write Ahead Log Settings
| Parameter | Default value | Description |
| wal_level | minimum | Determines how much information is written to the WAL. Change this to enable replication. Other values are archive, logical and hot_standby. |
| fsync | on | Turn this off to make your database much faster –and silently cause arbitrary corruption in case of a system crash. |
| wal_buffer | -1, autotune) | The amount of memory used in shared memory for WAL data. The default setting of -1 selects a size equal to 1/32nd (about 3%) of shared_buffers. |
| min_wal_size | 80 MB | The WAL size to start recycling the WAL files. |
| max_wal_size | 1 GB | The WAL size to start checkpoint.Controls the number of WAL Segments(16MB each) after which checkpoint is forced. |
| checkpoint_timeout | 5 minutes | Maximum time between checkpoints. |
| wal_compression | off | The WAL of Full Page write will be compressed and written. |
6.Where To Log
log_destination-Valid values are combinations of stderr, csvlog, syslog, and eventlog, depending on platform.
logging_collector-Enables advanced logging features. csvlogrequires logging_collector.
‒log_directory-Directory where log files are written. Requires logging_collector.
‒log_filename-Format of log file name (e.g. postgresql-%Y-%M-%d.log). Allows regular log rotation. Requires logging_collector.
‒log_file_mode(default 0600) -On Unix systems this parameter sets the permissions for log files when logging_collectoris enabled.
‒log_rotation_age-Automatically rotate logs after this much time. Requires logging_collector.
‒log_rotation_size-Automatically rotate logs when they get this big. Requires logging_collector.
7.When To Log
| Parameter | Default value | Description |
| client_min_messages | NOTICE | Messages of this severity level or above are sent to the client |
| log_min_messages | WARNING | Messages of this severity level or above are sent to the server. |
| log_min_error_statement | ERROR | When a message of this severity or higher is written to the server log, the statement that caused it is logged along with it. |
| log_min_duration_statement | default -1, disabled | When a statement runs for at least this long, it is written to the server log, with its duration. |
8.What to log
| Parameter | Default value | Description |
| log_connections | off | Log successful connections to the server log. |
| log_disconnections | off | Log some information each time a session disconnects, including the duration of the session. |
| log_error_verbosity | default | Can also select “terse” or “verbose”. |
| log_duration | off | Log duration of each statement. |
| log_line_prefix | Additional details to log with each line. | |
| log_statement | none | Legal values are none, ddl, mod (DDL and all other data-modifying statements), or all. |
| log_temp_files | -1 | Log temporary files of this size or larger, in kilobytes. |
| log_checkpoints | off | Causes checkpoints and restartpoints to be logged in the server log. |
9.Background Writer Settings
| Parameter | Default value | Description |
| bgwriter_delay | 200 ms | Specifies time between activity rounds for the background writer. |
| bgwriter_lru_maxpages | 100 | Maximum number of pages that the background writer may clean per activity round. |
| bgwriter_lru_multiplier | 2.0 | Multiplier on buffers scanned per round. By default, if system thinks 10 pages will be needed, it cleans 10 * bgwriter_lru_multiplierof 2.0 = 20. |
10.Statement Behavior
| Parameter | Default value | Description |
| search_path | This parameter specifies the order in which schemas are searched. The default value for this parameter is”$user”, public. | |
| default_tablespace | Name of the tablespace in which to objects are created by default. | |
| temp_tablespaces | Tablespaces name(s) in which temporary objects are created. | |
| statement_timeout | Postgres will abort any statement that takes over the specified number of milliseconds A value of zero (the default) turns this off. |
11.Vacuum Cost Settings
| Parameter | Default value | Description |
| vacuum_cost_delay | 0 ms | The length of time, in milliseconds, that the process will wait when the cost limit is exceeded. |
| vacuum_cost_page_hit | 1 | The estimated cost of vacuuming a buffer found in the PostgreSQL buffer pool. |
| vacuum_cost_page_miss | 10 | The estimated cost of vacuuming a buffer that must be read into the buffer pool. |
| vacuum_cost_page_dirty | 20 | The estimated cost charged when vacuum modifies a buffer that was previously clean. |
| vacuum_cost_limit | 200 | The accumulated cost that will cause the vacuuming process to sleep. |
12.Autovacuum Settings
| Parameter | Default value | Description |
| autovacuum | on | Controls whether the autovacuum launcher runs, and starts worker processes to vacuum and analyze tables. |
| log_autovacuum_min_duration | -1 | Autovacuum tasks running longer than this duration are logged. Can now be specified per table. |
| autovacuum_max_workers | 3 | Maximum number of autovacuum worker processes which may be running at one time. |
| autovacuum_work_mem | default -1, to use maintenance_work_mem | Maximum amount of memory used by each autovacuum worker. |