Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to view Postgresql configuration files". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

If you want to view some options in the configuration file, you can log in to psql and use the command to view

Show option name

Show all; # View the values of all database parameters

Main options:

The default value of the option indicates whether the maximum number of concurrent connections allowed by max_connections100 is optimized because during the test, 100 connections are enough for fsyncon to force data synchronization to be updated to disk because of the high IO pressure on the system, in order to better test the impact of other configurations Changing the parameter to offshared_buffers24MB to determine how much memory can be used by PostgreSQL for caching data (recommended memory 1max 4) is in the case of high IO pressure, increasing this value can reduce IOwork_mem1MB so that internal sorting and some complex queries are completed in this buffer helps to improve the speed of operations such as sorting, and reduces the maximum memory that the IOeffective_cache_size128MB optimizer assumes that a query can use. It has nothing to do with shared_buffers (recommended memory 1Universe 2). The optimizer prefers to use index scanning rather than sequential scanning maintenance_work_mem16MB. The memory defined here is only used by more resource-consuming commands such as VACUUM to increase this value. The size of the wal_buffer768kB log cache that can speed up the execution of commands can reduce IO, if you encounter more concurrent short transactions. You should use checkpoint_segments3 with commit_delay to set the maximum number of wal log (the size of a log is 16m). The default 48m cache is a serious bottleneck. Basically, setting it to more than 10 checkpoint_completion_target0.5 means that the completion time of the checkpoint to be completed within N% of the two checkpoint intervals can reduce the average write cost commit_delay.

The interval between log writes to wal log and wal_buffer writes to disk after the transaction is committed. Need to cooperate with commit_sibling to be able to write multiple transactions at a time, reduce IO, improve performance commit_siblings5 set the number of concurrent transactions that trigger commit_delay, and configure yes according to the number of concurrent transactions

Reduce IO and improve performanc

The number of database connections reserved by superuser_reserved_connections3 for superusers, which must be less than max_connections. Is to reduce IO and improve performance

I. connection configuration and security authentication

1. Connect Connection Settings

Listen_addresses (string)

This parameter can only be set when the database is started. It specifies the TCP/IP address that the database uses to listen for client connections. The default value is *, which means that after startup, the database will listen for user requests on all IP addresses on the machine on which the data is running (if the machine has only one network card, only one IP address, and the machine with multiple network cards has multiple IP addresses). It can be written as the name of the machine or as an IP address, with different values separated by commas, for example, 'server01',' 140.87.171.49,140.87.171.21'. If set to localhost, it means that the database can only accept local client connection requests, not remote client connection requests.

Port (integer)

This parameter can only be set when the database is started. It specifies the TCP port to which the database listener client connects. The default value is 5432.

Max_connections (integer)

This parameter can only be set when the database is started. It determines the maximum number of client connections that the database can establish at the same time. The default value is 100. Each connection consumes 400 bytes of shared memory. Note: Increasing max_connections costs ~ 400bytes of shared memory per connection slot, plus lock space (see max_locks_per_transaction).

Superuser_reserved_connections (integer)

This parameter can only be set when the database is started. It represents the number of database connections reserved for superusers. Its value must be less than max_connections. The maximum number of concurrent connections that the average user can make in the database is 3 by default for max_connections.

Unix_socket_group (string)

This parameter can only be set when the database is started. Set the operating system user group in which Unix-domain socket belongs. The default value is an empty string, using the group of the operating system user who started the database as the user group of the Unix-domain socket.

Unix_socket_permissions (integer)

This parameter can only be set when the database is started. It sets access to Unix-domain socket in the same format as the operating system's file access. The default value is 0770, which means that any operating system user can access Unix-domain socket. Can be set to 0770 (all Unix-domain socket file owners are in a group that contains users who can access it) and 0700 (only the owner of the Unix-domain socket file can access it). For Unix-domain socket, only write permissions make sense, and read and execute permissions are meaningless.

# unix_socket_directories ='/ tmp' # comma-separated list of directories

# (change requires restart)

# bonjour = off # advertise server via Bonjour

# (change requires restart)

# bonjour_name =''# defaults to the computer name

# (change requires restart)

2. Security certification Security and Authentication

Authentication_timeout (integer)

This parameter can only be set in the postgresql.conf file, it specifies a length of time within which the client authentication operation must be completed, otherwise the client connection request will be rejected. It can prevent some clients from taking up database connections for a long time when they authenticate. The unit is seconds, and the default value is 60.

Ssl (boolean)

This parameter can only be set when the database is started. Determines whether the database accepts SSL connections. The default value is off.

Ssl_ciphers (string)

Specifies the SSL encryption algorithm that can be used. Check the operating system's user manual on openssl for a complete list of encryption algorithms (you can also get it by executing the command openssl ciphers-v).

# ssl_prefer_server_ciphers = on # (change requires restart)

# ssl_ecdh_curve = 'prime256v1' # (change requires restart)

# ssl_renegotiation_limit = 0 # amount of data between renegotiations

# ssl_cert_file = 'server.crt' # (change requires restart)

# ssl_key_file = 'server.key' # (change requires restart)

# ssl_ca_file =''# (change requires restart)

# ssl_crl_file =''# (change requires restart)

# password_encryption = on

# db_user_namespace = off

# GSSAPI using Kerberos

# krb_server_keyfile =''

# krb_caseins_users = off

#-TCP Keepalives-

Tcp_keepalives_idle (integer)

This parameter can be set at any time. The default value is 0, which means to use the default value of the operating system. It sets the TCP_KEEPIDLE property of the TCP socket. This parameter has no effect on database connections established through Unix-domain socket. That is, the TCP heartbeat packet is sent intermittently, and the house connection is interrupted by network equipment.

Tcp_keepalives_interval (integer)

This parameter can be set at any time. The default value is 0, which means to use the default value of the operating system. It sets the TCP_KEEPINTVL property of the TCP socket. This parameter has no effect on database connections established through Unix-domain socket.

Tcp_keepalives_count (integer)

This parameter can be set at any time. The default value is 0, which means to use the default value of the operating system. It sets the TCP_KEEPCNT property of the TCP socket. This parameter has no effect on database connections established through Unix-domain socket.

II. File configuration FILE LOCATIONS

# The default values of these variables are driven from the-D command-line

# option or PGDATA environment variable, represented here as ConfigDir.

# data_directory = 'ConfigDir' # use data in another directory

# (change requires restart)

# hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file

# (change requires restart)

# ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file

# (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.

# external_pid_file =''# write an extra PID file

# (change requires restart)

3. Memory Memory

Shared_buffers (integer)

This parameter can only be set when the database is started. It represents the number of blocks in the data buffer, and the size of each block is 8KB. The data buffer is located in the shared memory of the database, and the larger the better, it cannot be less than 128KB. The default value is 128MB.

Temp_buffers (integer)

This parameter can be set at any time. The default value is 8MB. It determines the number of blocks in the data buffer where the temporary table is stored, and the size of each block is 8KB. Temporary table buffers are stored in the private memory of each database process, not in the shared memory of the database. Minimum 800KB

Max_prepared_transactions (integer)

This parameter can only be set when the database is started. It determines the maximum number of transactions that can be in the prepared state at the same time (see the PREPARE TRANSACTION command). If its value is set to 0. The database will turn off the feature of the prepared transaction. Its value should usually be as large as the value of max_connections. Each transaction consumes 600 bytes (b) shared memory.

Work_mem (integer)

This parameter can be set at any time. It determines the sort operation of the database and the size of the memory buffer used by the hash table. If the memory specified by work_mem is exhausted, the database will use disk files to complete the operation, which will be much slower. ORDER BY, DISTINCT, and merge connections use sort operations. Hash tables are used in Hash joins, hash aggregate functions, and using hash tables to process subqueries in IN predicates. The unit is KB and the default is 4MB.

Maintenance_work_mem (integer)

This parameter can be set at any time. It determines the amount of memory space used by database maintenance operations. The maintenance operations of the database include VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY and other operations. If the value of maintenance_work_mem is large, you can usually shorten the time required for the VACUUM database and the recovery of the database from the dump file. The maintenance_work_mem is stored in the private memory of each database process, not in the shared memory of the database. The unit is KB and the default is 64MB.

Max_stack_depth (integer)

This parameter can be set at any time, but only the database superuser can modify it. It determines the maximum amount of space occupied by the STACK of a database process at runtime. When the database process is running, it automatically checks whether its STACK size exceeds max_stack_depth, and if it does, it automatically terminates the current transaction. This value should be smaller than the upper limit of the process STACK set by the operating system 1MB. Use the operating system command "ulimit-s" to get the maximum STACK of the process set by the operating system. The unit is KB and the default is 2MB.

# huge_pages = try # on, off, or try (change requires restart)

# use large pages as much as possible, need the support of the operating system, and configure vm.nr_hugepages*2MB greater than shared_buffers.

Dynamic_shared_memory_type = posix

# the default is the first option

# supported by the operating system:

# posix

# sysv

# windows

# mmap

# use none to disable dynamic shared memory

# autovacuum_work_mem =-1

# min 1MB, or-1 to use maintenance_work_mem

Resources (free space mapping)

Free Space Map

All available space information in the database is stored in a structure called free space map (FSM), which records the amount of free space for each data block in the data file. Blocks that are not recorded in FSM will not be used by the system even if space is available. If the system needs new physical storage space, it will first look in FSM, and if none of the data pages in FSM have enough free space, the system will automatically expand the data file. Therefore, if the FSM is too small, it will cause the system to expand the data files frequently, wasting physical storage space. After the command VACUUM VERBOSE finishes, it prompts whether the current FSM setting meets the needs, and if the parameter value of FSM is too small, it prompts you to increase the parameter.

FSM is stored in the shared memory of the database. Due to the limitation of physical memory, FSM can not track the free space information of all data blocks of all data files in the database, but can only track the free space information of some data blocks.

Max_fsm_relations (integer)

This parameter can only be set when the database is started. The default value is 1000. It determines the upper limit of the number of tables and indexes tracked by FSM. Each table and index takes up 7 bytes of storage space in FSM.

Max_fsm_pages (integer)

This parameter can only be set when the database is started. It determines the upper limit of the number of blocks tracked in the FSM. When creating a database cluster, initdb determines its value based on the size of physical memory. Each data block takes up 6 bytes of storage space in the fsm. Its size cannot be less than 16 * max_fsm_relations. The default value is 20000.

5. Kernel resource Kernel Resource Usage

Max_files_per_process (integer)

This parameter can only be set when the database is started. He sets the number of files that each database process can open. The default value is 1000.

Shared_preload_libraries (string)

This parameter can only be set when the database is started. It sets the operating system shared library files to be loaded by the database at startup. If there are multiple library files, the names are separated by commas. If the database does not find one of the library files specified by shared_preload_libraries at startup, the database will not start. The default is an empty string.

6. Garbage resource recycling Cost-Based Vacuum Delay

VACUUM and ANALYZE commands are executed because they consume a lot of CPU and IO resources and take a long time to execute at one time, which interferes with the system executing SQL commands issued by the application. To solve this problem, after the VACUUM and ANALYZE commands are executed for a period of time, the system temporarily terminates their operation, and then continues to execute the two commands after a period of time. This feature is turned off by default. This feature can be turned on by setting the parameter vacuum_cost_delay to a positive integer that is not zero.

Users usually only need to set the parameters vacuum_cost_delay and vacuum_cost_limit, and use the default values for other parameters. During the execution of VACUUM and ANALYZE commands, the system calculates the resources consumed by their execution, and the number of resources is expressed as a positive integer. If the number of resources exceeds vacuum_cost_limit, the process executing the command will go to sleep, and the length of sleep is vacuum_cost_delay. The higher the value of vacuum_cost_limit, the less the number of times you sleep during the execution of VACUUM and ANALYZE commands, whereas the lower the value of vacuum_cost_limit, the more times you sleep during the execution of VACUUM and ANALYZE commands.

Vacuum_cost_delay (integer)

This parameter can be set at any time. The default value is 0. It determines the sleep time of the processes that execute the VACUUM and ANALYZE commands. The unit is milliseconds. Its value is preferably an integer of 10, and if it is not an integer of 10, the system will automatically set it to an integer larger than that value and the nearest value is a multiple of 10. If the value is 0 and the ANALYZE command will not actively go to sleep during execution, it will be executed until the end.

Vacuum_cost_page_hit (integer)

This parameter can be set at any time. The default value is 1. Lock the buffer pool, find the shared hash table, and scan the content of the page overhead (credits).

Vacuum_cost_page_miss (integer)

This parameter can be set at any time. The default value is 10. Represents the overhead of locking the buffer pool, finding shared hash tables, reading required blocks from disk, and scanning its contents.

Vacuum_cost_page_dirty (integer)

This parameter can be set at any time. The default value is 20. If you clean up and modify the estimated cost of a previously clean block. It requires an extra overhead of flushing dirty blocks onto the disk again.

Vacuum_cost_limit (integer)

This parameter can be set at any time. The default value is 200. The accumulated overhead that causes the cleanup process to sleep.

7. Write the database process Background Writer in the background

The background database writing process is responsible for writing the modified data blocks (also called dirty data blocks) in the data buffer back to the database physical file.

Bgwriter_delay (integer)

This parameter can only be set in the file postgresql.conf. It determines the sleep time of the database writing process in the background. Each time the background database writing process completes the task of writing data to a physical file, it sleeps at the time specified by bgwriter_delay. The value of bgwriter_delay should be a multiple of 10, and if the value set by the user is not a multiple of 10, the database will automatically set the value of the parameter to the value that is closer to the user-specified value and a multiple of 10 than the user-specified value. The unit is millisecond, and the default value is 200. That is, the back-end write process repeats the action every millisecond.

Bgwriter_lru_maxpages (integer)

This parameter can only be set in the file postgresql.conf. The default value is 100. Each time the background database writing process writes dirty data blocks, the number of dirty data blocks written to external files cannot exceed the value specified by bgwriter_lru_maxpages. For example, if its value is 500, the background database writing process cannot write more than 500 data pages to the physical file at a time, if more than 500, the process will go to sleep and perform the task of writing the physical file the next time it wakes up. If its value is set to 0, the background write database process will not write any physical files (but will also perform a checkpoint).

That is, the maximum number of dirty pages to be written in a cycle.

Bgwriter_lru_multiplier (floating point)

This parameter can only be set in the file postgresql.conf. The default value is 2.0. It determines the number of dirty data blocks written to the external file (not more than the value specified by bgwriter_lru_maxpages) each time the background write database process writes to the physical file. The default value is generally used, and there is no need to modify this parameter. The higher the value of this parameter, the more dirty data blocks will be written each time by the background database writing process.

-Asynchronous Behavior

# effective_io_concurrency = 1 # 1-1000; 0 disables prefetching

# max_worker_processes = 8

If you want to use worker process, the maximum number of worker processes that can be allowed for fork

8. Transaction log prewriting

Full_page_writes (boolean)

This parameter can only be set in the postgresql.conf file. The default value is on. Turning on this parameter can improve the reliability of the database and reduce the probability of data loss, but it will generate too many transaction logs and reduce the performance of the database. That is, the server writes the entire page to wal when it writes the page for the first time after checkpoint.

Wal_buffers (integer)

This parameter can only be set when the database is started. The default value is 8. It specifies the number of blocks contained in the transaction log buffer, each of which is 8KB, so the default transaction log buffer size is 8*8=64KB. The transaction log buffer is in the shared memory of the database. That is, the number of disk page buffers placed in shared memory for wal data, with a minimum of 32kb indicating a share buffer-based setting.

Wal_writer_delay (integer)

This parameter can only be set in the postgresql.conf file. It determines the sleep time of the process of writing transaction logs. Each time the WAL process completes the task of writing the transaction log, it sleeps at the time specified by wal_writer_delay, then wakes up and continues to write the newly generated transaction log from the buffer to the WAL file. The unit is milliseconds (millisecond), and the default value is 200. That is, how often does the write operation occur.

Commit_delay (integer)

This parameter can be set at any time. It sets the sleep time of the transaction after issuing the commit command, and only after sleeping at the time specified by commit_delay, the transaction log generated by the transaction will be written to the transaction log file, and the transaction can be really committed. Increasing this parameter increases the waiting time for users, but allows multiple transactions to be committed at the same time, improving the performance of the system. If the load in the database is high and most transactions are update-type transactions, consider increasing the value of this parameter. The following parameter commit_siblings affects whether commit_delay is valid or not. The default value is 0, in microseconds (microsecond). 0 means no delay. That is, the time delay between writing a record to the WAL buffer and flushing the buffer to disk.

Commit_siblings (integer)

This parameter can be set at any time. The value of this parameter determines whether the parameter commit_delay is valid. Assuming that the value of commit_siblings is 5, if a transaction issues a commit request, at this point, if the number of transactions being executed in the database is greater than or equal to 5, then the transaction will sleep for the time specified by commit_delay. If the number of transactions being executed in the database is less than 5, the transaction will be committed directly. The default value is 5. That is, the minimum number of concurrent transactions opened in commit_delay time (1-1000)

# wal_level = minimal prewritten log mode

# minimal, archive, hot_standby, or logical

# (change requires restart)

# fsync = on sets the synchronization method

# turns forced synchronization on or off

# synchronous_commit = on if the IOPS of the disk is general, it is recommended to use asynchronous commit to improve performance, but when database crash or operating system crash, the transaction log generated by 2*wal_writer_delay time period may be lost at most (in wal buffer)

# synchronization level

# off, local, remote_write, or on

# wal_sync_method = the method used by fsync to force updates of wal data to disk. If fsync is off, then this setting is irrelevant and invalid.

# the default is the first option

# supported by the operating system:

# open_datasync (open the WAL file with open () of the O_DSYNC option)

# fdatasync (default on Linux) calls fdatasync () every time it is submitted

# fsync calls fsync every time it is submitted

# fsync_writethrough calls fsync () every time it is submitted, forcing any disk write buffer to be written out

# open_sync opens the WAL file with open () of the O_DSYNC option

# wal_log_hints = off

# also do full page writes of non-critical updates (change requires restart)

9.

10. Checkpoint Checkpoints

Checkpoint_segments (integer) in logfile segments, min 1, 16MB each

This parameter can only be set in the postgresql.conf file. The default value is 3. It affects when the system initiates a checkpoint operation. If the number of transaction log files generated by the system exceeds the checkpoint_segments value after the last checkpoint operation ends, the system automatically initiates a checkpoint operation. Increasing this parameter increases the time it takes to recover after a database crash. That is, the maximum size of the segment occurs once checkpoint, which is equal to the size of shared_buffers divided by a single wal segment.

Checkpoint_timeout (integer) range 30s-1h

This parameter can only be set in the postgresql.conf file. The unit is seconds, and the default value is 300. It affects when the system initiates a checkpoint operation. If the current time minus the end of the last checkpoint operation exceeds the checkpoint_timeout value, the system automatically initiates a checkpoint operation. Increasing this parameter increases the time it takes to recover after a database crash. That is, how long does it take for checkpoint to occur?

Checkpoint_completion_target (floating point)

This parameter controls the execution time of the checkpoint operation. The legal value is between 0 and 1, and the default value is 0.5. Do not easily change the value of this parameter, just use the default value. This parameter can only be set in the postgresql.conf file.

# checkpoint_warning = 30s

11. Disk Disk

# temp_file_limit =-1 # limits per-session temp file space

# in kB, or-1 for no limit

Temporary file space (kb) for each session, with-1 for unlimited

12. Archiving mode Archiving

Archive_mode (boolean)

This parameter can only be set when the database is started. The default value is off. It determines whether the database turns on archive mode.

Archive_dir (string)

This parameter can only be set when the database is started. The default value is an empty string. It sets the directory where the archived transaction log files are stored.

Archive_timeout (integer)

This parameter can only be set in the postgresql.conf file. The default value is 0. The unit is seconds. If the value of archive_timeout is not 0 and the current time minus the time the database last switched the transaction log file is greater than the value of archive_timeout, the database will perform a transaction log file switch. In general, the database will not switch to the next transaction log file until one transaction log file is full. Setting this parameter allows the database to switch to the next transaction log file if one transaction log file is not full.

13. Optimizer parameter QUERY TUNING

1. Access method parameter Planner Method Configuration

The following parameters control whether the query optimizer uses specific access methods. Unless you know a lot about optimizers, you can generally use their default values.

Enable_bitmapscan (boolean)

Turns on or off the planner's use of the bitmap scan plan type. The default value is on.

Enable_hashagg (boolean)

Turns on or off the use of hash aggregation planning types by query planner. The default value is on.

Enable_hashjoin (boolean)

Turns on or off the use of hash join planning types by query planner. The default value is on.

Enable_indexscan (boolean)

Turns on or off the use of the index scan planning type by query planner. The default value is on.

Enable_mergejoin (boolean)

Turns on or off the use of the merge connection planning type by query planner. The default value is on.

Enable_nestloop (boolean)

Turns on or off the use of the nested loop connection planning type by query planner. The default value is on.

Enable_seqscan (boolean)

Turns on or off the use of sequential scan planning types by the query planner. The default value is on.

Enable_sort (boolean)

Turn query planner on or off using explicit sorting steps. The default value is on.

Enable_tidscan (boolean)

Turns on or off the use of TID scan planning types by query planner. The default value is on.

2. Optimizer cost constant Planner Cost Constants

The optimizer uses a positive floating-point number to represent the execution cost of different query plans, and each basic database operation is assigned to a determined cost constant. The optimizer calculates the execution cost of each query plan based on the execution cost of each basic operation. Do not easily change the values of the following parameters, just use their default values.

Seq_page_cost (floating point)

Sets the execution cost of sequentially reading a data block from a data file. The default value is 1.0.

Random_page_cost (floating point)

Sets the execution cost of randomly reading a data block from a data file. The default value is 4.0.

Cpu_tuple_cost (floating point)

Sets the execution cost for processing each row of data. The default value is 0.01.

Cpu_index_tuple_cost (floating point)

Sets the execution cost of processing each index item during the scan of the index. The default value is 0.005.

Cpu_operator_cost (floating point)

Sets the execution cost of processing each operator or function. The default value is 0.0025.

Effective_cache_size (integer)

Sets the size of the data buffer that can be used by a single query. The default value is 128MB.

3. Query optimization Genetic Query Optimizer

The following parameters control the genetic algorithm used by the optimizer. Unless you have a special knowledge of genetic algorithms, in general, you can use their default values.

Geqo (boolean)

Turns the genetic optimizer on or off. The default value is on.

Geqo_threshold (integer)

Determine the type of query that uses the genetic optimizer. The default value is 12. If the number of tables referenced in the FROM clause exceeds the value of geqo_threshold, the genetic optimizer is used. Use the exhaustive optimizer for simple queries.

Geqo_effort (integer)

Control the genetic optimizer to make a tradeoff between the time it takes to generate the query plan and the effectiveness of the query plan. Valid values range from 1 to 10. The default value is 5. The higher the value, the longer it takes the optimizer to select a query plan, and the more likely it is to find an optimal query plan. The system usually does not directly use the value of geqo_effort, but uses its value to calculate the default values of the parameters geqo_pool_size and geqo_generations.

Geqo_pool_size (integer)

Controls the pool (pool) size of the genetic optimizer. The default value is 0. Pool size is the number of individuals in a genetic population. At least 2, typically between 10 and 1000. If the value of the parameter is 0, a default value is automatically selected based on the value of geqo_effort and the number of tables referenced in the query.

Geqo_generations (integer)

Controls the size of the genetic optimizer generation (generation). The default value is 0. Generation is the number of iterations of genetic algorithm. At least 1, the typical range of values is the same as that of the pool. If the value of the parameter is 0, a default value is automatically selected based on the value of geqo_pool_size.

Geqo_selection_bias (floating point)

Controls the size of the generation selection deviation (selection bias) of the genetic optimizer. The default value is 2. Values range from 1.50 to 2.00.

4. Other optimizer parameters Other Planner Options

Default_statistics_target (integer)

Sets the default target value for collecting optimizer statistics. The higher its value, the longer the execution time of the ANALYZE operation, the more rows of data are scanned, and the more accurate the optimizer statistics are. You can also use the command ALTER TABLE... ALTER COLUMN... SET STATISTICS sets a separate statistics target value for each column of the table. This value works the same as the parameter default_statistics_target, which only affects the statistics collection process of the related columns. The default value is 10.

Constraint_exclusion (boolean)

If the value of this parameter is on, the query optimizer uses the constraints on the table to optimize the query. If its value is off, the query optimizer does not use constraints on the table to optimize the query. The default value is off.

# cursor_tuple_fraction = 0.1 # range 0.0-1.0

# from_collapse_limit = 8

# join_collapse_limit = 8 # 1 disables collapsing of explicit

# JOIN clauses

14. Configuration parameters of database operation log

1 、 Where to Log

Log_destination = 'stderr' # Valid values are combinations of

# stderr, csvlog, syslog, and eventlog

# depending on platform. Csvlog

# requires logging_collector to be on.

# This is used when logging to stderr:

Logging_collector = on # Enable capturing of stderr and csvlog

# into log files. Required to be on for

# csvlogs.

# (change requires restart)

# These are only used if logging_collector is on:

Log_directory (string)

This parameter can only be set in the postgresql.conf file. It determines the directory where the database running log files are stored. The default value is pg_log. It can be an absolute path or a relative path (relative to the path where the database file is located).

Log_filename (string)

It determines the name of the database running log file. The default value is postgresql-%Y-%m-%d_%H%M%S.log. Its value can contain strings such as% Y,% m,% d,% H,% M, and% S, representing year, month, day, hour, minute, and second, respectively. If no time information is specified in the value of the parameter (no% Y,% m,% d,% H,% M, and% S appear), the file name is automatically added to the end of the log_ filename value. For example, if the value of log_filename is server_log, the name of the log file created at Sun Aug 29 19:02:33 2004 MST will be server_log.1093827753. 1093827753 is the internal representation of Sun Aug 29 19:02:33 2004 MST in the database. This parameter can only be set in the postgresql.conf file.

Log_rotation_age (integer)

It determines when to create a new database log file. The unit is in minutes. The default value is 0. If the current time minus the last time to create a database run log exceeds the value of log_rotation_age, the database will automatically create a new run log file. If its value is 0, this parameter will have no effect. This parameter can only be set in the postgresql.conf file.

Log_rotation_size (integer)

This parameter can only be set in the postgresql.conf file. It determines when to create a new database log file. The unit is KB. The default value is 10240. If a log file writes more data than the log_rotation_size value, the database creates a new log file. If its value is set to 0, this parameter will have no effect.

Log_truncate_on_rotation (boolean)

When the system creates a new database running log file, if a file with the same name is found, the system overwrites the file with the same name when the value of log_truncate_on_rotation is on. When the value of log_truncate_on_rotation is off, the system reuses the file with the same name, adding new log information at the end of it. It is also important to note that the log file with the same name is overwritten only if the system creates a new log file because the parameter log_rotation_age works. Because the database is restarted or a new log file is created because the parameter log_rotation_size works, the log file with the same name is not overwritten, but new log information is added at the end of the log file with the same name. This parameter can only be set in the postgresql.conf file. The default value is off.

For example, if you set this parameter to on, set log_rotation_age to 60, and set log_filename to postgresql-%H.log at the same time, there will be only 24 log files in the system, which will be constantly reused. At most, the log information of the last 24 hours will be available in the system at any one time.

# These are relevant when logging to syslog:

# syslog_facility = 'LOCAL0'

# syslog_ident = 'postgres'

# This is only relevant when logging to eventlog (win32):

# event_source = 'PostgreSQL

2 、 When to Log

Client_min_messages (string)

Controls the level of messages sent to the client. The legal values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, ERROR, FATAL, and PANIC, and each level contains information from all levels that follow it. The lower the level, the less messages are sent to the client. The default value is NOTICE. This parameter can be set at any time.

Log_min_messages (string)

Controls the level of messages written to the database log file. The legal values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC, and each level contains information from all levels that follow it. The lower the level, the fewer messages are recorded in the database run log. The default value is NOTICE. Only superusers can modify this parameter. Only superusers can set this parameter.

Log_error_verbosity (string)

Controls the level of detail of each log message. The legal values are TERSE, DEFAULT, and VERBOSE (each value provides more detailed information than the previous values). Only superusers can modify this parameter. The default value is DEFAULT.

Log_min_error_statement (string)

Controls whether SQL statements that cause errors in the database are recorded in the log. The legal values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC, and each level contains all levels that follow it. The default value is ERROR. Only superusers can modify this parameter.

Message severity level

Severity level

Usage

DEBUG1..DEBUG5

Report detailed debugging information.

INFO

Report the information that the user may need.

NOTICE

Report information that is useful to users.

WARNING

Report a warning message.

ERROR

An error message is reported.

LOG

Report information that is useful to database administrators, such as checkpoint operation statistics.

FATAL

Reports an error message that caused the current session to be terminated.

PANIC

Report an error message that caused the entire database to be shut down.

3 、 What to Log

Debug_print_parse (boolean)

Debug_print_rewritten (boolean)

Debug_print_plan (boolean)

Debug_pretty_print (boolean)

These parameters control whether the database outputs debugging information at run time. The default value for these parameters is off. These parameters can be set by any user.

Log_checkpoints (boolean)

Control whether and record the operation information of the checkpoint. The default value is off. This parameter can only be set in the postgresql.conf file. The database must be restarted to take effect.

Log_connections (boolean)

Controls whether and records client connection request information. The default value is off. This parameter can only be set in the postgresql.conf file. The database must be restarted to take effect.

Log_disconnections (boolean)

Controls whether client end connection information is recorded. The default value is off. This parameter can only be set in the postgresql.conf file.

Log_duration (boolean)

Controls whether the execution time of each completed SQL statement is recorded. Only superusers can modify this parameter. The default value is off. For clients that use the extension protocol to communicate with the database, the execution times of Parse, Bind, and Execute are recorded.

Log_hostname (boolean)

Controls whether and records the hostname of the client. The default value is off. If set to on, the performance of the database may be affected because it may take some time to resolve the hostname. This parameter can only be set in the postgresql.conf file. This parameter can only be set in the postgresql.conf file.

Log_line_prefix (string)

Controls the prefix format of each log message. The default value is an empty string. Its format is similar to the format string of the printf function in the c language. This parameter can only be set in the postgresql.conf file.

Log_line_prefix ='

< %m >

'# special values:

Escape sequence

#% a = application name

#% u = user name user name

#% d = database name database name

#% r = remote host and port client machine name or IP address, as well as client port

#% h = remote host client machine name or IP address

#% p = process ID process ID

#% t = time of timestamp without milliseconds with microseconds

#% m = timestamp with milliseconds time without microseconds

#% I = command tag command label: the type of command currently executed by the session

#% e = SQL state

#% c = session ID session ID

#% l = session line number log number for each session, starting with 1

#% s = session start timestamp process startup time

#% v = virtual transaction ID virtual transaction ID (backendID/localXID)

#% x = transaction ID (0 if none) transaction ID (0 means no transaction ID is assigned)

#% Q = stop here in non-session does not produce any output If the current process is a backend process, ignore the escape sequence and continue processing the subsequent escape sequence. If the current process is not a backend process, ignore this escape sequence and all escape sequences that follow it.

# processes

#%%'% 'character%

Log_lock_waits (boolean)

If a session waits for a type of lock longer than the value of deadlock_timeout, this parameter determines whether this information is logged in the database log. The default value is off. Only superusers can modify this parameter.

Log_statement (string)

Controls the execution information of which SQL statements are logged. Valid values are none, ddl, mod, and all. The default value is none. Ddl includes all data definition statements, such as CREATE, ALTER, and DROP statements. Mod includes all ddl statements and statements that update data, such as INSERT, UPDATE, DELETE, TRUNCATE, COPY FROM, PREPARE, and EXECUTE. All includes all statements. Only superusers can modify this parameter.

Log_temp_files (integer)

Controls whether deletion information for temporary files is recorded. The unit is KB. 0 means to record the deletion information of all temporary files. A positive integer means that only temporary files whose size is larger than the value of log_temp_files are recorded. -1 indicates that no temporary file deletion information is recorded. The default value is-1. This parameter can be set at any time.

Log_timezone (string)

Sets the time zone that the database log file uses when writing the log file. The default value is unknown, and awareness is the time zone of the operating system. This parameter can only be set in the postgresql.conf file

15. Database operation statistics related parameters RUNTIME STATISTICS

The following parameters control whether specific database operation statistics are collected:

-Query/Index Statistics Collector

Track_activities (boolean)

Whether to collect statistics on the currently executing commands for each session, including the time when the command began to execute. The default value is on. Only superusers can modify this parameter.

Track_counts (boolean)

Whether to collect statistics for database activity. The default value is on. Only superusers can modify this parameter.

# track_io_timing = off

# track_functions = none # none, pl, all

# track_activity_query_size = 1024 # (change requires restart)

# update_process_title = on

# stats_temp_directory = 'pg_stat_tmp'

-Statistical monitoring Statistics Monitoring--

Log_statement_stats (boolean)

Log_parser_stats (boolean)

Log_planner_stats (boolean)

Log_executor_stats (boolean)

These parameters determine whether to record statistics on the execution of each SQL statement in the database's run log. If the value of log_statement_stats is on, the value of the other three parameters must be off. The default value for all of these parameters is off. Log_statement_stats reports statistics for the entire statement, log_parser_stats records statistics for the database parser, log_planner_stats reports statistics for the database query optimizer, and log_executor_stats reports statistics for the database executor. Only superusers can modify these parameters.

16. Automatic garbage collection related parameters AUTOVACUUM PARAMETERS

The following parameters control the behavior of automatic garbage collection:

Autovacuum (boolean)

The control is the automatic garbage collection function that can open the database. The default value is on. If autovacuum is set to on, the parameter track_counts (see Chapter 10.9) must also be set to on for automatic garbage collection to work properly. Note that even if this parameter is set to off, the database will automatically start a garbage collection operation if transactional ID rollback is about to occur. This parameter can only be set in the file postgresql.conf.

Log_autovacuum_min_duration (integer)

The unit is milliseconds. If its value is 0, all garbage collection operations will be recorded in the database run log, if its value is-1, all garbage collection operations will not be recorded in the database run log. If its value is set to 250ms, as long as the execution time of VACUUM and ANALYZE commands issued by automatic garbage collection exceeds 250ms, information about VACUUM and ANALYZE commands will be recorded in the database run log. The default value is-1. This parameter can only be set in postgresql.conf.

Autovacuum_max_workers (integer)

Sets the maximum number of automatic garbage collection processes that can run at the same time. The default value is 3. This parameter can only be set in the file postgresql.conf.

Autovacuum_naptime (integer)

Sets the sleep time of the automatic garbage collection control process. The unit is seconds, and the default value is 60. This parameter can only be set in the file postgresql.conf.

Autovacuum_vacuum_threshold (integer)

Sets the threshold at which garbage collection operations are triggered. The default value is 50. This parameter can only be set in the file postgresql.conf. Garbage collection operations are performed on a table only if the number of records deleted or updated exceeds the value of autovacuum_vacuum_threshold.

Autovacuum_analyze_threshold (integer)

Sets the threshold that triggers the ANALYZE operation. The default value is 50. This parameter can only be set in the file postgresql.conf. ANALYZE operations are performed on a table only if the number of records deleted, inserted, or updated exceeds the value of autovacuum_analyze_threshold.

Autovacuum_vacuum_scale_factor (floating point)

This parameter is related to when a table is garbage collected. The default value is 0.2. This parameter can only be set in the file postgresql.conf.

Autovacuum_analyze_scale_factor (floating point)

This parameter is related to when to ANALYZE a table. The default value is 0.1. This parameter can only be set in the file postgresql.conf.

Autovacuum_freeze_max_age = 200000000

# maximum XID age before forced vacuum # (change requires restart)

Specifies the maximum age of transactions on the table, which defaults to 200 million. Reaching this threshold will trigger the autovacuum process, thus avoiding wraparound. The transaction age on the table can be queried through pg_class.relfrozenxid

Autovacuum_multixact_freeze_max_age = 400000000

# maximum multixact age

# before forced vacuum

# (change requires restart)

Autovacuum_vacuum_cost_delay = 20ms

# default vacuum cost delay for

# autovacuum, in milliseconds

#-1 means use vacuum_cost_delay

When the autovacuum process is about to execute, the vacuum execution cost is evaluated. If the value of autovacuum_vacuum_cost_limit is exceeded, the delay is delayed, and the time value of the delay is the changed value.

Autovacuum_vacuum_cost_limit =-1

# default vacuum cost limit for

# autovacuum,-1 means use

# vacuum_cost_limit

This value is the evaluation threshold of the autovacuum process. The default value is-1. The table uses the vacuum_cost_ threshold value. If the cost evaluated during the autovacuum process exceeds autovacuum_vacuum_cost_limit, the autovacuum process will hibernate.

Lock Management LOCK MANAGEMENT

Deadlock_timeout (integer)

Sets the deadlock timeout detection time. The unit is microseconds, and the default value is 1000. Deadlock detection is an operation that consumes a lot of CPU resources. The value of this parameter cannot be too small. In the case of heavy database load, you should increase the value of this parameter.

Max_locks_per_transaction (integer)

This parameter controls the average number of object locks available for each transaction. The default value is 64. The maximum number of max_locks_per_transaction * (max_connections + max_prepared_transactions) object locks can be saved in the shared lock table created by the database after startup. The number of object locks that a single transaction can acquire at the same time can exceed the value of max_locks_per_transaction, as long as there is space left in the shared lock table. Each lock takes up 270 bytes of shared memory

# lock table slots.

# max_pred_locks_per_transaction = 64 # min 10

# (change requires restart)

Client connection Management CLIENT CONNECTION DEFAULTS

#-Statement Behavior-

# search_path ='"$user", public' # schema names

# default_tablespace =''# a tablespace name,''uses the default

# temp_tablespaces =''# a list of tablespace names,''uses

# only default tablespace

# check_function_bodies = on

# default_transaction_isolation = 'read committed'

# default_transaction_read_only = off

# default_transaction_deferrable = off

# session_replication_role = 'origin'

# statement_timeout = 0 # in milliseconds, 0 is disabled

# lock_timeout = 0 # in milliseconds, 0 is disabled

# vacuum_freeze_min_age = 50000000

# vacuum_freeze_table_age = 150000000

# vacuum_multixact_freeze_min_age = 5000000

# vacuum_multixact_freeze_table_age = 150000000

# bytea_output = 'hex' # hex, escape

# xmlbinary = 'base64'

# xmloption = 'content'

# gin_fuzzy_search_limit = 0

#-Locale and Formatting-

Datestyle = 'iso, mdy'

# intervalstyle = 'postgres'

Timezone = 'PRC'

# timezone_abbreviations = 'Default' # Select the set of available timezone

# abbreviations. Currently, there are

# Default

# Australia (historical usage)

# India

# You can create your own file in

# share/timezonesets/.

# extra_float_digits = 0 # min-15, max 3

# client_encoding = sql_ascii # actually, defaults to database

# encoding

# These settings are initialized by initdb, but they can be changed.

Lc_messages = 'en_US.UTF-8' # locale for system error message

# strings

Lc_monetary = 'en_US.UTF-8' # locale for monetary formatting

Lc_numeric = 'en_US.UTF-8' # locale for number formatting

Lc_time = 'en_US.UTF-8' # locale for time formatting

# default configuration for text search

Default_text_search_config = 'pg_catalog.english'

#-Other Defaults-

# dynamic_library_path ='$libdir'

# local_preload_libraries =''

# session_preload_libraries =''

XIX. Error handling ERROR HANDLING

# exit_on_error = off # terminate session on any error?

# restart_after_crash = on # reinitialize after backend crash?

Configuration files include CONFIG FILE INCLUDES

A configuration file can also contain other configuration files, which can be achieved using the include directive.

For example, there is a line like this in the postgresql.conf file:

Include 'my.confg'

In this way, the configuration information in the my.confg file will also be read into the database.

The configuration file specified by the include directive can also include other configuration files with the include directive. If the file name specified in the include directive is not an absolute path, the database looks for the file in the same directory as the postgresql.conf file.

# include_dir = 'conf.d' # include files ending in' .conf 'from

# directory 'conf.d'

# include_if_exists = 'exists.conf' # include file only if it exists

# include = 'special.conf' # include file

21, version\ platform compatible with VERSION/PLATFORM COMPATIBILITY

#-Previous PostgreSQL Versions-

# array_nulls = on

# backslash_quote = safe_encoding # on, off, or safe_encoding

# default_with_oids = off

# escape_string_warning = on

# lo_compat_privileges = off

# quote_all_identifiers = off

# sql_inheritance = on

# standard_conforming_strings = on

# synchronize_seqscans = on

#-Other Platforms and Clients-

# transform_null_equals = off

22, copy REPLICATION

#-Sending Server (s)-

# Set these on the master and on any standby that will send replication data.

# max_wal_senders = 0 # max number of walsender processes

# (change requires restart)

# wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables

# wal_sender_timeout = 60s # in milliseconds; 0 disables

# max_replication_slots = 0 # max number of replication slots

# (change requires restart)

#-Master Server-

# These settings are ignored on a standby server.

# synchronous_standby_names =''# standby servers that provide sync rep

# comma-separated list of application_name

# from standby (s);'*'= all

# vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed

#-Standby Servers-

# These settings are ignored on a master server.

# hot_standby = off # "on" allows queries during recovery

# (change requires restart)

# max_standby_archive_delay = 30s # max delay before canceling queries

# when reading WAL from archive

#-1 allows indefinite delay

# max_standby_streaming_delay = 30s # max delay before canceling queries

# when reading streaming WAL

#-1 allows indefinite delay

# wal_receiver_status_interval = 10s # send replies at least this often

# 0 disables

# hot_standby_feedback = off # send info from standby to prevent

# query conflicts

# wal_receiver_timeout = 60s # time that receiver waits for

# communication from master

# in milliseconds; 0 disables

This is the end of "how to View Postgresql configuration File". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.