（二）PostgreSQL常用的配置文件(2)

PostgreSQL常用的配置文件(2)

在 PostgreSQL 中，postgresql.conf 文件是数据库实例的主要配置文件，其中包含了许多参数，用于调整和控制 PostgreSQL 的行为和性能。这些参数可以根据它们的功能和影响范围分类。以下是一些主要类别及其描述：

1 FILE LOCATIONS（文件位置）

用于指定一些关键的文件和目录路径。如data_directory、hba_file等

2 CONNECTIONS AND AUTHENTICATION（连接和身份验证）

该部分控制与数据库连接和认证相关的各种参数设置。这些参数对于数据库的安全性、可访问性和性能都至关重要。

• Connection Settings
• TCP settings
• Authentication
• SSL

部分参数：

listen_addresses

• 描述: 控制 PostgreSQL 服务器监听哪些 IP 地址。可以指定为一个列表，用逗号分隔，或者使用 ‘*’ 代表监听所有可用的接口。
• 示例: listen_addresses = 'localhost'（只监听来自本地机器的连接请求）
• 示例: listen_addresses = '*'（监听所有的网络接口）

port

• 描述: 设置 PostgreSQL 服务器监听的端口号。默认情况下，PostgreSQL 使用端口 5432。
• 示例: port = 5432

max_connections

• 描述: 设置 PostgreSQL 能够接受的最大并发连接数。这个数字包括超级用户保留的连接，因此实际可用的最大连接数可能会略微低于这个设置。
• 示例: max_connections = 100

superuser_reserved_connections

• 描述: 为超级用户预留的连接数。这确保了即使数据库达到 max_connections 限制时，超级用户（如数据库管理员）依然能够连接到数据库进行维护或故障排除。
• 示例: superuser_reserved_connections = 3

authentication_timeout

• 描述: 设置客户端认证超时的时间，单位是秒。如果在这个时间内，客户端没有完成认证过程，服务器将终止连接。
• 示例: authentication_timeout = 60

password_encryption

• 描述: 指定用户密码的加密方式。在较新版本的 PostgreSQL 中，可能支持如 scram-sha-256 这样的更安全的加密方式。
• 示例: password_encryption = scram-sha-256

ssl

• 描述: 是否启用 SSL 加密。为了使 SSL 生效，可能还需要配置其他与 SSL 相关的参数，如 ssl_cert_file 和 ssl_key_file。
• 示例: ssl = on

3 RESOURCE USAGE (except WAL)（资源使用（WAL除外））

该部分包含了一系列的参数，这些参数用于控制数据库系统资源的使用，如内存和进程等。这些设置对于数据库的性能和稳定性非常重要。

• Memory
• Disk
• Kernel Resources
• Cost-Based Vacuum Delay
• Background Writer
• Asynchronous Behavior

部分参数

shared_buffers

• 描述: 设置用于缓存数据的共享内存的大小。这是 PostgreSQL 用于缓存表、索引等数据的主要内存区域。
• 示例: shared_buffers = 4GB

temp_buffers

• 描述: 指定每个数据库会话可以用于临时表的内存最大值。这些内存只在一个特定的数据库会话中使用，并且在会话结束时释放。
• 示例: temp_buffers = 8MB

work_mem

• 描述: 设置排序操作（如 ORDER BY、DISTINCT）或哈希表（如用于 JOIN 或 UNION 操作）可用的内存量，对于每个操作过程。
• 示例: work_mem = 64MB

maintenance_work_mem

• 描述: 指定用于维护数据库的任务（如 VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY 等）的内存最大值。
• 示例: maintenance_work_mem = 512MB

autovacuum_work_mem

• 描述: 设置自动 VACUUM 操作可以使用的内存量。如果设置为 -1，则表示使用 maintenance_work_mem 设置的值。
• 示例: autovacuum_work_mem = -1

max_stack_depth

• 描述: 设置 PostgreSQL 服务器进程的最大堆栈深度。这个设置应小于操作系统允许的最大堆栈大小。
• 示例: max_stack_depth = 2MB

max_files_per_process

• 描述: 控制单个 PostgreSQL 进程可以打开的最大文件数。这个设置影响到数据库能够有效管理的文件描述符数量。
• 示例: max_files_per_process = 1000

effective_io_concurrency

• 描述: 表示 PostgreSQL 预计的并发 I/O 操作的数量。这个参数帮助数据库优化并行 I/O 操作，特别是在使用支持并发 I/O 的存储系统时。
• 示例: effective_io_concurrency = 200

max_worker_processes

• 描述: 设置 PostgreSQL 可以启动的最大背景工作进程数量。这个设置影响到并行查询操作和后台进程的最大数目。
• 示例: max_worker_processes = 8

max_parallel_workers_per_gather

• 描述: 设置单个查询操作中可以启动的并行工作进程的最大数量。这会影响到执行查询时的并行度。
• 示例: max_parallel_workers_per_gather = 4

4 WRITE-AHEAD LOG（预写式日志）

该部分负责控制与 Write-Ahead Logging (WAL) 相关的设置。WAL 是 PostgreSQL 保证数据完整性和恢复能力的关键机制。通过先将数据变更写入 WAL 文件，然后异步写入数据文件的方式，PostgreSQL 能够在发生故障时恢复数据。

• Settings
• Checkpoints
• Prefetching during recovery
• Archiving
• Archive Recovery
• Recovery Target

部分参数

wal_level

• 描述: 定义 WAL 日志记录的详细程度，可以设置为 minimal、replica 或 logical。更高的级别增加了日志的详细程度，但可能会影响性能。对于大多数复制配置，通常需要设置为 replica 或更高。
• 示例: wal_level = replica

fsync

• 描述: 控制 PostgreSQL 是否使用 fsync() 调用来强制将 WAL 记录写入磁盘。关闭此设置可能会提升性能，但是会在系统崩溃时损失数据安全性。
• 示例: fsync = on

synchronous_commit

• 描述: 控制事务提交时的行为。当设置为 on 时，系统会等待将 WAL 记录物理写入磁盘后才确认事务提交，以保证数据的持久性。设置为 off 可以提高性能，但牺牲了事务的持久性。
• 示例: synchronous_commit = on

wal_buffers

• 描述: 设置 WAL 缓冲区的大小。这是 WAL 数据在被写入磁盘之前保存在内存中的区域。
• 示例: wal_buffers = -1 （设置为 -1 表示自动配置）

wal_writer_delay

• 描述: 控制 WAL 写入器进程写入磁盘的延迟时间。较短的时间可以提高数据持久性，但可能会增加 I/O 负载。
• 示例: wal_writer_delay = 200ms

checkpoint_timeout

• 描述: 控制两次自动 WAL 检查点之间的最大时间间隔。检查点是数据库定期将 WAL 中的数据写入磁盘的数据文件的过程。
• 示例: checkpoint_timeout = 5min

max_wal_size、min_wal_size

• 描述: 控制 WAL 文件大小的阈值，以触发新的检查点。max_wal_size 定义了 WAL 占用的最大空间，而 min_wal_size 则是 WAL 文件被回收前保持的最小空间。
• 示例:
  • max_wal_size = 1GB
  • min_wal_size = 80MB

archive_mode、archive_command

• 描述: 当 archive_mode 设置为 on 时，允许 PostgreSQL 将 WAL 文件归档到指定的位置，通常用于备份和灾难恢复中。archive_command 定义了用于归档 WAL 文件的具体命令。
• 示例:
  • archive_mode = on
  • archive_command = 'cp %p /path_to_archive/%f'

5 REPLICATION（复制）

该部分包含了一系列设置，用于控制主从复制或逻辑复制的行为。这些设置对于配置高可用性、读写分离和数据分发架构非常重要。

• Sending Servers
• Primary Server
• Standby Servers
• Subscribers

部分参数

max_wal_senders

• 描述: 允许的最大 WAL 发送进程数目。这些进程用于在主服务器上处理备用服务器的复制请求。
• 示例: max_wal_senders = 10

wal_keep_size

• 描述: 设置在主服务器上保留的、用于复制的最小 WAL 文件总大小。这确保了备用服务器在断线一段时间后能够重新连接并继续复制而不需要完整的基础备份。
• 示例: wal_keep_size = 128MB

max_replication_slots

• 描述: PostgreSQL 服务器上可以配置的最大复制槽位数量。每个槽位用于一条复制流。逻辑复制需要使用复制槽位。
• 示例: max_replication_slots = 10

hot_standby

• 描述: 控制备用服务器是否允许在接收复制数据的同时，对读取操作进行响应。开启这个选项能够在备用服务器上执行只读查询。
• 示例: hot_standby = on

synchronous_standby_names

• 描述: 指定哪些备用服务器为同步备用服务器。这对于配置同步复制是必需的。格式可以是具体的备用服务器名字或者优先级和数量的组合。
• 示例: synchronous_standby_names = 'first 2 (*)'

primary_conninfo

• 描述: 在备用服务器的 recovery.conf 或 postgresql.conf（针对 PostgreSQL 12 及以上版本）中设置，指定连接到主服务器的信息，如主服务器的地址、端口、用户和密码等。
• 示例: primary_conninfo = 'host=master_host port=5432 user=replicator password=secret'

primary_slot_name

• 描述: 在启用逻辑复制的备用服务器上设置，指示备用服务器连接到主服务器时使用的复制槽位。
• 示例: primary_slot_name = 'replication_slot'

6 QUERY TUNING（查询调优）

该部分包含了影响查询执行计划和性能的多个参数。正确调整这些参数可以显著提高数据库的性能和响应时间。

• Planner Method Configuration
• Planner Cost Constants
• Genetic Query Optimizer
• Other Planner Options

部分参数

seq_page_cost

• 描述: 估计顺序扫描一页所需的成本。默认值通常设为1.0。调低此值可能使优化器更倾向于采用顺序扫描。
• 示例: seq_page_cost = 1.0

random_page_cost

• 描述: 估计随机访问一页的成本。这个值通常高于 seq_page_cost，因为随机访问比顺序访问更慢。在 SSD 硬盘上，可以考虑降低这个值，因为 SSD 的随机读取性能较好。
• 示例: random_page_cost = 4.0

cpu_tuple_cost

• 描述: 估计处理一个元组（即数据库中的一行）的成本。默认值较低，增加这个值可以使优化器更偏向于使用索引。
• 示例: cpu_tuple_cost = 0.01

cpu_index_tuple_cost

• 描述: 估计处理一个通过索引检索的元组的成本。
• 示例: cpu_index_tuple_cost = 0.005

cpu_operator_cost

• 描述: 估计执行运算符或函数的成本，默认值非常低。
• 示例: cpu_operator_cost = 0.0025

effective_cache_size

• 描述: 估计数据库可用的缓存大小，包括 PostgreSQL 的 shared_buffers 和操作系统的文件系统缓存。
• 示例: effective_cache_size = 4GB

min_parallel_table_scan_size

• 描述: 顺序扫描的最小大小，才能考虑启用并行扫描。
• 示例: min_parallel_table_scan_size = 8MB

min_parallel_index_scan_size

• 描述: 索引扫描的最小大小，才能考虑启用并行扫描。
• 示例: min_parallel_index_scan_size = 512kB

default_statistics_target

• 描述: 设置为 ANALYZE 收集统计信息时，目标样本大小的默认值。更大的值可能改善优化器的选择，但会增加 ANALYZE 的运行时间。
• 示例: default_statistics_target = 100

7 REPORTING AND LOGGING（报告和日志记录）

该部分涉及到数据库的日志记录和报告功能，这些功能是数据库管理、监控和故障排除的重要工具。合理配置这些参数有助于跟踪数据库活动、性能瓶颈和潜在的安全问题。

• Where to Log
• When to Log
• What to Log

部分参数

log_destination

• 描述: 指定日志输出的目的地。可能的值包括 ‘stderr’、‘csvlog’ 以及 ‘syslog’（在 Unix-like 系统上）和 ‘eventlog’（在 Windows 上）。
• 示例: log_destination = 'stderr'

logging_collector

• 描述: 开启这个选项，PostgreSQL 会启动一个单独的进程来收集日志信息，允许日志信息被重定向到指定的日志目录中。
• 示例: logging_collector = on

log_directory

• 描述: 指定收集到的日志文件存放的目录。这个设置只有在 logging_collector 被启用时才有效。
• 示例: log_directory = 'pg_log'

log_filename

• 描述: 设置日志文件的命名方式。你可以使用一些预定义的变量，比如时间来帮你区分不同时间段的日志文件。
• 示例: log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age

• 描述: 指定日志文件的最大存活时间，超过这个时间就会创建一个新的日志文件。
• 示例: log_rotation_age = '1d' # 每天轮换一次

log_rotation_size

• 描述: 触发日志轮换的文件大小。当日志文件达到这个大小时，将会创建一个新的日志文件。
• 示例: log_rotation_size = '100MB'

log_min_messages

• 描述: 设置被记录的消息的最低级别。可用的级别有 DEBUG5、DEBUG4、DEBUG3、DEBUG2、DEBUG1、INFO、NOTICE、WARNING、ERROR、LOG、FATAL 和 PANIC。
• 示例: log_min_messages = WARNING

log_min_error_statement

• 描述: 设置记录 SQL 语句的最低错误级别。当一个语句产生的错误达到或超过这个级别时，这个语句将被记录。
• 示例: log_min_error_statement = ERROR

log_line_prefix

• 描述: 在每条日志行前添加的格式化字符串。这个选项允许你包含一些有用的信息，比如时间戳、SQL 语句的执行标识等。
• 示例: log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '

log_statement

• 描述: 设置需要记录的所有 SQL 语句的级别。可以是 ‘none’、‘ddl’、‘mod’、‘all’。
• 示例: log_statement = 'all' # 记录所有 SQL 语句

log_duration

• 描述: 是否记录 SQL 语句的执行时间。这个设置通常用于性能分析。
• 示例: log_duration = on

log_connections 和 log_disconnections

• 描述: 这两个参数用于记录客户端连接和断开连接的信息。
• 示例:
  • log_connections = on
  • log_disconnections = on

default_statistics_target

• 描述: 设置默认的统计目标，这个值决定了 ANALYZE 命令收集统计信息时使用的样本大小。较大的值会增加收集统计信息的时间，但可能会导致更精确的查询计划。
• 示例: default_statistics_target = 100

8 STATISTICS（统计）

该部分包括多个参数，这些参数对数据库的性能调整和问题解析至关重要。通过收集和分析统计信息，PostgreSQL 可以优化查询计划，提高查询效率。

• Cumulative Query and Index Statistics
• Monitoring

部分参数

track_activities

• 描述: 指示 PostgreSQL 跟踪每个会话的当前活动，以及活动的查询。这对于监控和分析数据库性能具有重要作用。
• 示例: track_activities = on

track_counts

• 描述: 开启数据库对表和索引的访问频率统计。这对优化器（查询计划选择）非常重要，因为这些信息被用来估计查询执行的成本。
• 示例: track_counts = on

track_functions

• 描述: 控制是否跟踪用户定义函数的执行统计信息。可选值为 none（不收集）、pl（仅收集过程语言的函数）、all（收集所有函数）。
• 示例: track_functions = pl

track_io_timing

• 描述: 开启或关闭对数据库 I/O 操作的计时统计。这可以提供精确的 I/O 时间数据，有助于深入分析性能问题，但可能会轻微影响性能。
• 示例: track_io_timing = on

9 AUTOVACUUM（自动清空）

该部分参数可以帮助数据库管理员精细地控制 autovacuum 进程的行为。调整这些设置有助于优化数据库性能、减少事务 ID 包裹（wraparound）的风险，并确保查询计划的有效性。

部分参数

autovacuum

• 描述: 启用或禁用 autovacuum 守护进程。建议保持启用以自动维护数据库的健康状态。
• 示例: autovacuum = on

log_autovacuum_min_duration

• 描述: 设置日志记录 autovacuum 操作的最短时间阈值。只有运行时间超过这个值的 autovacuum 操作才会被记录到日志中。如果设置为 -1，则不记录任何 autovacuum 操作。
• 示例: log_autovacuum_min_duration = 0 # 记录所有autovacuum操作

autovacuum_max_workers

• 描述: 设置可以同时运行的 autovacuum 工作进程的最大数量。
• 示例: autovacuum_max_workers = 3

autovacuum_naptime

• 描述: 设置 autovacuum 工作进程在检查是否有表需要 VACUUM 或 ANALYZE 操作之间的休息时间。
• 示例: autovacuum_naptime = 1min

autovacuum_vacuum_threshold

• 描述: 设置一个表在触发 autovacuum VACUUM 操作之前，由于更新或删除操作而变得“脏”的元组（行）数量的阈值。
• 示例: autovacuum_vacuum_threshold = 50

autovacuum_analyze_threshold

• 描述: 设置一个表在触发 autovacuum ANALYZE 操作之前，由于插入、更新或删除操作而变化的元组数量阈值。
• 示例: autovacuum_analyze_threshold = 50

autovacuum_vacuum_scale_factor

• 描述: 设置一个表的总元组数量的一部分，超过这部分的“脏”元组会触发 autovacuum VACUUM 操作。
• 示例: autovacuum_vacuum_scale_factor = 0.2

autovacuum_analyze_scale_factor

• 描述: 设置一个表的总元组数量的一部分，超过这部分变化的元组会触发 autovacuum ANALYZE 操作。
• 示例: autovacuum_analyze_scale_factor = 0.1

autovacuum_freeze_max_age

• 描述: 设置在 VACUUM 强制对表进行全表扫描以阻止事务 ID 包裹之前，一个表的事务 ID 年龄可以达到的最大值。
• 示例: autovacuum_freeze_max_age = 200000000

autovacuum_multixact_freeze_max_age

• 描述: 类似 autovacuum_freeze_max_age，但适用于多事务 ID。
• 示例: autovacuum_multixact_freeze_max_age = 400000000

10 CLIENT CONNECTION DEFAULTS（客户端连接默认值）

该部分包含了一系列与客户端连接设置相关的参数。这些参数主要影响新的数据库连接的默认行为和性能特性，包括连接超时、字符集、日期/时间格式等。

• Statement Behavior
• Locale and Formatting
• Shared Library Preloading
• Other Defaults

11 LOCK MANAGEMENT（锁管理）

该部分专门用于配置与数据库锁机制相关的参数。数据库锁是并发控制的重要机制，用于管理多个事务同时访问相同数据的情况。合理的锁管理配置可以帮助提高并发性能，避免死锁，并确保数据的一致性和完整性。

部分参数

max_locks_per_transaction

• 描述: 此参数配置了每个事务最大可以持有的锁数量。这包括对象锁和行锁。这个设置对于处理包含大量锁请求的复杂事务至关重要。需要注意的是，增加此值会增加每个事务占用的内存量。
• 示例: max_locks_per_transaction = 64

deadlock_timeout

• 描述: 指定 PostgreSQL 在检测潜在死锁情况之前等待的时间长度。如果两个或更多事务相互持有对方需要的锁，就会发生死锁。该参数设置的是在启动死锁检测机制之前要等待的时间。
• 示例: deadlock_timeout = 1s

max_pred_locks_per_transaction

• 描述: (如果使用了基于谓词的锁定系统，例如 Serializable 隔离级别) 此参数用于控制每个事务允许的最大谓词锁数。与 max_locks_per_transaction 类似，增加此值会影响内存使用。
• 示例: max_pred_locks_per_transaction = 64

12 VERSION AND PLATFORM COMPATIBILITY（版本和平台兼容性）

该部分包含了一组参数，这些参数用于控制与旧版本 PostgreSQL 或特定平台兼容性相关的行为。通过适当配置这些参数，管理员可以确保在升级 PostgreSQL 版本或迁移数据库到新平台时，应用程序能够平稳过渡，最小化兼容性问题。

• Previous PostgreSQL Versions
• Other Platforms and Clients

部分参数

array_nulls

• 描述: 控制对 SQL 标准中的 NULL 数组元素的处理。在较旧版本的 PostgreSQL 中，默认为打开（on），以兼容那些版本的行为。
• 示例: array_nulls = on

backslash_quote

• 描述: 设置反斜杠引用的行为，对于字符串字面量中的单引号，能够控制反斜杠作为转义字符的能力。可选的值包括 safe_encoding、on、off，其中 safe_encoding 防止字符编码安全问题。
• 示例: backslash_quote = safe_encoding

escape_string_warning

• 描述: 当字符串常量包含反斜杠时，如果 standard_conforming_strings 开启，则可能会产生警告，这个参数可以控制是否显示这些警告。
• 示例: escape_string_warning = on

standard_conforming_strings

• 描述: 控制字符串字面量的处理方式，特别是反斜线的解释。开启此选项（on）意味着反斜线在字符串中不作为转义字符，与 SQL 标准一致。
• 示例: standard_conforming_strings = on

synchronize_seqscans

• 描述: 控制顺序扫描是否同步进行。禁用此项（设置为 off）可能会在某些场景下提高性能，但默认情况下保持启用以确保更一致的性能。
• 示例: synchronize_seqscans = on

13 ERROR HANDLING（错误处理）

14 CONFIG FILE INCLUDES（包括的配置文件）

15 CUSTOMIZED OPTIONS（客户自定义）