postgres 文件结构和记忆（更新中）

Posted on 2024-09-08 09:35 大势趋007 阅读(6) 评论(0) 编辑收藏举报

[postgres@pg1 data]$ ll
total 8256
drwx------. 8 postgres postgres      84 Jan  2  2024 base
-rw-------. 1 postgres postgres      33 Sep  8 08:51 current_logfiles
drwx------. 2 postgres postgres    4096 Sep  8 08:52 global
-rw-------. 1 postgres postgres 6226485 Jan  2  2024 logfile
drwx------. 2 postgres postgres       6 Jan  2  2024 pg_commit_ts
drwx------. 2 postgres postgres       6 Jan  2  2024 pg_dynshmem
-rw-------. 1 postgres postgres    4896 Jan  2  2024 pg_hba.conf
-rw-------. 1 postgres postgres    1636 Jan  2  2024 pg_ident.conf
drwx------. 2 postgres postgres     150 Sep  8 08:51 pg_log
drwx------. 4 postgres postgres      68 Sep  8 09:11 pg_logical
drwx------. 4 postgres postgres      36 Jan  2  2024 pg_multixact
drwx------. 2 postgres postgres       6 Jan  2  2024 pg_notify
drwx------. 2 postgres postgres       6 Jan  2  2024 pg_replslot
drwx------. 2 postgres postgres       6 Jan  2  2024 pg_serial
drwx------. 2 postgres postgres       6 Jan  2  2024 pg_snapshots
drwx------. 2 postgres postgres       6 Sep  8 08:51 pg_stat
drwx------. 2 postgres postgres     134 Sep  8 09:14 pg_stat_tmp
drwx------. 2 postgres postgres      18 Jan  4  2024 pg_subtrans
drwx------. 2 postgres postgres       6 Jan  2  2024 pg_tblspc
drwx------. 2 postgres postgres       6 Jan  2  2024 pg_twophase
-rw-------. 1 postgres postgres       3 Jan  2  2024 PG_VERSION
drwx------. 3 postgres postgres     188 Sep  7 13:31 pg_wal
drwx------. 2 postgres postgres      30 Jan  3  2024 pg_xact
-rw-------. 1 postgres postgres      88 Jan  2  2024 postgresql.auto.conf
-rw-------. 1 postgres postgres   27928 Jan  4  2024 postgresql.conf
-rw-------. 1 postgres postgres      24 Sep  8 08:51 postmaster.opts
-rw-------. 1 postgres postgres      73 Sep  8 08:51 postmaster.pid
[postgres@pg1 data]$ pwd
/opt/pgsql/data
[postgres@pg1 data]$

[postgres@pg1 data]$ more pg_hba.conf  连接配置文件，类似oracle 监听
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file.  A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local         DATABASE  USER  METHOD  [OPTIONS]
# host          DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl     DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostgssenc    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnogssenc  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
#

[postgres@pg1 data]$ more postgresql.conf  参数配置文件，类似oracle pfile
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "pg_ctl reload", or execute
# "SELECT pg_reload_conf()".  Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time

[postgres@pg1 data]$ more postmaster.pid  类似mysql 进程文件
4546
/opt/pgsql/data
1725756698
5432
/tmp
*
 69307088    196610
ready   
[postgres@pg1 data]$ ps -ef|grep postgres
root      4490  4438  0 08:51 pts/0    00:00:00 su - postgres
postgres  4491  4490  0 08:51 pts/0    00:00:00 -bash
postgres  4546     1  0 08:51 ?        00:00:00 /opt/pgsql/bin/postgres
postgres  4547  4546  0 08:51 ?        00:00:00 postgres: logger 
postgres  4549  4546  0 08:51 ?        00:00:00 postgres: checkpointer 
postgres  4550  4546  0 08:51 ?        00:00:00 postgres: background writer
postgres  4551  4546  0 08:51 ?        00:00:00 postgres: walwriter 
postgres  4552  4546  0 08:51 ?        00:00:00 postgres: autovacuum launcher
postgres  4553  4546  0 08:51 ?        00:00:00 postgres: stats collector
postgres  4554  4546  0 08:51 ?        00:00:00 postgres: powa idle
postgres  4555  4546  0 08:51 ?        00:00:00 postgres: logical replication launcher
postgres  4774  4546  0 09:01 ?        00:00:00 postgres: test02 postgres 192.168.56.1(50744) idle
postgres  4775  4546  0 09:01 ?        00:00:00 postgres: test02 postgres 192.168.56.1(50745) idle
postgres  5069  4491  0 09:18 pts/0    00:00:00 ps -ef
postgres  5070  4491  0 09:18 pts/0    00:00:00 grep --color=auto postgres
[postgres@pg1 data]$

快速进阶记忆
连接设置

listen_addresses：指定服务器在哪些 TCP/IP 地址上监听客户端连接，默认值是localhost，只允许本地连接。
max_connections：决定数据库的最大并发连接数，默认值通常是 100 个连接，如果内核设置不支持（initdb时决定），可能会比这个数少。
superuser_reserved_connections：为超级用户保留的连接数，默认是 3，不能小于 max_connections。

内存设置
shared_buffers：数据库服务器将使用的共享内存，默认通常是 128M，如果内核设置不支持（initdb时决定），那么可以会更少。这个设置必须至少为 128 千字节。
shared_buffers 推荐值是系统内存的 25%。因为PostgreSQL同样依赖操作系统的高速缓冲区，将shared_buffers设置为超过系统内存40%，可能会造成更高的负载。
max_prepared_transactions：设置可以同时处于“prepared”状态的事务的最大数目，把这个参数设置为零（这是默认设置）将禁用预备事务特性。
若要使用预备事务，max_prepared_transactions 至少设置为同 max_connections 一样大。
work_mem：单个查询操作(例如排序或哈希表)可使用的最大内存，默认值是 4MB，ORDER BY、DISTINCT和归并连接都要用到排序操作，哈希连接、基于哈希的聚集以及基于哈希的IN子查询处理中都要用到哈希表。
maintenance_work_mem：维护性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）中使用的最大的内存，其默认值是64M，
更大的设置可以改进清理和恢复数据库转储的性能。

autovacuum_work_mem：每个自动清理工作者进程能使用的最大内存量，其默认值为 -1，表示转而使用 maintenance_work_mem 的值。
建议单独分配，因为 maintenance_work_mem 分配的资源建索引等操作也会使用。

dynamic_shared_memory_type：服务器使用的内存管理方式。可能的值是

posix（用于使用 shm_open分配的 POSIX 共享内存）、
sysv （用于通过shmget分配的 System V 共享内存）、 windows（用于 Windows 共享内存）、和mmap
（使用存储在数据目录中的内存映射文件模拟共享内存）。并非所有平台上都支持所有值，平台上第一个支持的选项就是其默认值。
在任何平台上mmap选项都不是默认值，通常不鼓励使用它，因为操作系统会反复地把修改过的页面写回到磁盘上，
从而增加了系统的I/O负载。不过当 pg_dynshmem目录被存储在一个 RAM 盘时或者没有其他共享内存功能可用时，它还是有用的。

effective_cache_size：规划器对一个单一查询可用的有效磁盘缓冲区的预估大小，更高的数值会使得索引扫描更可能被使用，更低的数值会使得顺序扫描更可能被使用。
这个参数对PostgreSQL分配的共享内存尺寸没有影响，它也不会保留内核磁盘缓冲，它只用于估计的目的，默认值是 4GB，推荐值是系统内存的 50%或更大。

IO设置
bgwriter_delay：后台写入器活动轮次之间的延迟。在每个轮次中，写入器都会为一定数量的脏缓冲区发出写操作，之后它就休眠 bgwriter_delay 的时长，
然后重复动作。当缓冲池中没有脏缓冲区时，不管 bgwriter_delay，它都会进入更长的休眠，默认值是 200ms。

bgwriter_lru_maxpages：在每个轮次中，不超过这么多个缓冲区将被后台写入器写出，把这个参数设置为零可禁用后台写出，默认值是 100 个缓冲区。
bgwriter_lru_multiplier：最近所需缓冲区的平均值乘以 bgwriter_lru_multiplier可以估算下一轮次中将会需要的缓冲区数目。
脏缓冲区将被写出直到有很多干净可重用的缓冲区（然而，每一轮次中写出的缓冲区数不超过 bgwriter_lru_maxpages）。因此，设置为 1.0 表示一种“刚刚好的”策略，这种策略会写出正好符合预测值的数目的缓冲区，更大大的值可以为需求高峰提供某种缓冲，默认值是 2.0。

effective_io_concurrency：设置了该值，磁盘的预读会失效，顺序扫描和索引扫描都可以利用磁盘预读，建议关闭，默认值为 1。
max_worker_processes：系统能够支持的后台进程的最大数量，默认值为 8。在更改这个值时，
考虑也对 max_parallel_workers、max_parallel_maintenance_workers、max_parallel_workers_per_gather 进行调整。

max_parallel_workers：设置系统支持的最大并行数量，默认值为8，要注意将这个值设置得大于 max_worker_processes 将不会产生效果，因为并行工作者进程都是从 max_worker_processes 所建立的工作者进程池中取出来的。

max_parallel_maintenance_workers：单一工具性命令能够启动的最大并行数。当前支持使用并行工作者的工具性命令是 CREATE INDEX，
并且只有在构建B-树索引时才能并行，并且 VACUUM 没有 FULL选项。并行工作者从由 max_worker_processes 创建的进程池中取出，数量由max_parallel_workers控制，默认值为2。

max_parallel_workers_per_gather：设置允许的最大并行查询数，并行查询可能消耗比非并行查询更多的资源，把这个值设置为0将会禁用并行查询执行，默认值是2。

wal_compression：当这个参数为on时，如果 full_page_writes 为打开（默认值打开）或者处于基础备份期间，PostgreSQL服务器会压缩写入到 WAL 中的完整页面镜像。压缩页面镜像将在 WAL 重放时被解压。默认值为off。只有超级用户可以更改这个设置，打开这个参数可以减小 WAL 所占的空间且无需承受不可恢复的数据损坏风险，但是代价是需要额外的 CPU 开销以便在 WAL 记录期间进行压缩以及在 WAL 重放时解压。
wal_writer_delay：指定 WAL 写入器刷写 WAL 的频繁程度，以时间为单位。在刷写WAL之后，写入器将根据 wal_writer_delay所给出的时间长度进行睡眠，除非被一个异步提交的事务提前唤醒。如果最近的刷写发生在 wal_writer_delay 之前，并且小于 wal_writer_flush_after WAL的值产生之后，那么WAL只会被写入操作系统，而不会被刷写到磁盘，默认值是 200ms。

commit_delay：在一次 WAL 刷写被发起之前，commit_delay 增加一个时间延迟。如果系统负载足够高，使得在一个给定间隔内有额外的事务准备好提交，那么通过允许更多事务通过一个单次 WAL 刷写来提交能够提高组提交的吞吐量。但是，它也把每次 WAL 刷写的潜伏期增加到了最多 commit_delay。因为如果没有其他事务准备好提交，就会浪费一次延迟，只有在当一次刷写将要被发起时有至少 commit_siblings(默认是5个)个其他活动事务时，才会执行一次延迟。另外，如果fsync被禁用，则将不会执行任何延迟。
checkpoint_timeout：自动 WAL 检查点之间的最长时间，合理的范围在 30 秒到 1 天之间，默认是 5min。增加这个参数的值会增加崩溃恢复所需的时间。
max_wal_size：在自动 WAL 检查点之间允许 WAL 增长到的最大尺寸。这是一个软限制，在特殊的情况下 WAL 尺寸可能会超过max_wal_size，例如在重度负荷下、archive_command失败或者高的 wal_keep_size设置，默认为 1 GB。增加这个参数可能导致崩溃恢复所需的时间。
min_wal_size：只要 WAL 磁盘用量保持在这个设置之下，在检查点时旧的 WAL 文件总是被回收以便未来使用，而不是直接被删除。这可以被用来确保有足够的 WAL 空间被保留来应付 WAL 使用的高峰，例如运行大型的批处理任务。如果指定值时没有单位，则以兆字节为单位，默认是 80 MB。
archive_mode：当启用archive_mode时，可以通过设置 archive_command命令将完成的 WAL 段发送到归档存储。除用于禁用的off之外，还有两种模式：on 和 always。在普通操作期间，这两种模式之间没有区别，但是当设置为 always 时，WAL 归档器在归档恢复或者后备模式下也会被启用。在 always 模式下，所有从归档恢复的或者用流复制传来的文件将被（再次）归档。当wal_level被设置为minimal时，archive_mode不能被启用。
max_replication_slots：服务器可以支持的复制槽最大数量，默认值为10。将它设置为一个比当前已有复制槽要少的值会阻碍服务器启动。此外，要允许使用复制槽，wal_level必须被设置为 replica或更高。

synchronous_commit 参数说明
有 on、off、local、remote_write、remote_apply 5个选项。

单实例环境：
on：当数据库提交事务时，wal先写入 wal buffer 再写入 wal 日志文件，设置成on表示提交事务时需等待本地wal写入wal日志后才向客户端返回成功。
on 为默认设置，数据库非常安全，但性能有所损耗。
local：local含义和on类似，表示提交事务时需要等待本地wal写入后才向客户端返回成功。
off：当数据库提交事务时不需要等待本地 wal buffer 写入 wal 日志，随即向客户端返回成功，
设置成 off会给数据库带来一点风险：数据库宕机时最新提交的少量事务可能丢失，数据库重启后会认为这些事务异常终止，
会rollback。适用对数据库准确性要求不高同时追求数据库性能的的场景。

流复制环境：
on：表示流复制主库提交事务时，需等待备库接收主库发送的wal日志流并写入wal文件，之后才向客户端返回成功，简单的说on表示本地wal已落盘，备库的wal也已落盘，有两份持久化的wal，但备库此时还没有完成重做。这个选项带来的事务响应时间较高。
remote_write：表示流复制主库提交事务时，需等待备库接收主库发送的wal日志流并写入备节点操作系统缓存中，之后向客户端返回成功，这种情况下备库出现异常关闭时不会有已传送的wal日志丢失风险，但备库操作系统异常宕机就有已传送的wal丢失风险了，此时wal可能还没有完全写入备节点wal文件中，简单的说 remote_write 表示本地wal已落盘，备库的wal还在备库操作系统缓存中，也就是说只有一份持久化的wal。这个选项带来的事务响应时间较低。
remote_apply：表示流复制主库提交事务时，需等待备库接收主库发送的wal流并写入wal文件，同时备库已经完成重做，之后才向客户端返回成功，简单的说remote_apply 表示本地wal已落盘，备库wal已落盘并且已经完成重做，这个设置保证了拥有两份持久化的wal，同时备库也已经完成了重做。这个选项带来的事务响应时间最高。

日志设置
log_destination：有三种输出方法，stderr,csvlog,syslog；在windows上还支持eventlog。默认是stderr，如果使用csvlog的话，
logging_collector必须开启。也可以同时使用csvlog和stderr，会记录两种格式的日志。
logging_collector：日志收集器，它是一个捕捉被发送到stderr的日志消息的后台进程，并且它会将这些消息重定向到日志文件中，默认是OFF。
log_directory：日志保存路径，当logging_collector被启用时，这个参数决定日志文件将被在哪个目录下创建。
log_filename：日志名格式，默认是postgresql-%Y-%m-%d_%H%M%S.log。
log_file_mode：默认的权限是0600，表示只有服务器拥有者才能读取或写入日志文件。其他常用的设置是0640，它允许拥有者的组成员读取文件。不过要注意你需要修改log_directory为将文件存储在集簇数据目录之外的某个位置，才能利用这个设置。在任何情况下，让日志文件变成任何人都可读是不明智的，因为日志文件中可能包含敏感数据。
log_truncate_on_rotation：当logging_collector被启用时，这个参数将导致 PostgreSQL截断（覆盖而不是追加）任何已有的同名日志文件。不过，截断只在一个新文件由于基于时间的轮转被打开时发生，在基于尺寸的轮转时不会发生。如果被关闭，在所有情况下以前存在的文件将被追加。
log_rotation_age：当logging_collector被启用时，这个参数决定使用一个单个日志文件的最大时间量，之后将创立一个新的日志文件，默认为24小时。将这个参数设置为零将禁用基于时间的新日志文件创建。
log_rotation_size：当logging_collector被启用时，这个参数决定一个个体日志文件的最大尺寸。当这些数据量被发送到一个日志文件后，将创建一个新的日志文件，默认值是10M。设置为零时将禁用基于大小创建新的日志文件。
log_min_duration_statement：记录慢 sql 的时间阀值，超过这个时间的 sql 将会被记录到日志中，默认值为 -1，不记录慢sql。
log_checkpoints：控制检查点和重启点是否被记录在服务器日志中，一些统计信息也被包括在日志消息中，包括写入缓冲区的数据和写它们所花的时间，默认值是关闭。
log_connections：控制连接信息是否被记录，尝试对服务器的连接被记录，客户端认证的成功完成也会被记录，在会话中它不能被更改，默认为off。
log_disconnections：控制会话终止是否被记录。日志输出提供的信息类似于 log_connections，不过还外加会话的持续时间，在会话中它不能被更改，默认为off。
log_error_verbosity：有效值是TERSE、DEFAULT和VERBOSE。TERSE排除记录DETAIL、HINT、QUERY和CONTEXT错误信息。VERBOSE输出包括SQLSTATE错误码以及产生错误的源代码文件名、函数名和行号。默认是 DEFAULT。
log_line_prefix：设置日志中几率哪些内容，默认值是’%m [%p] '，记录时间戳和进程ID。
log_statement：控制哪些 SQL 语句被记录。有效值是 none (off)、ddl、mod和 all（所有语句），mod记录所有ddl语句，外加数据修改语句例如INSERT, UPDATE、DELETE、TRUNCATE, 和COPY FROM。如果PREPARE、EXECUTE和 EXPLAIN ANALYZE包含合适类型的命令，它们也会被记录。默认是 none。

autovacuum 设置
autovacuum：控制服务器是否运行自动清理启动器后台进程。默认为开启，不过要自动清理正常工作还需要启用 track_counts（默认启用）。
该参数只能在postgresql.conf文件或服务器命令行中设置，通过更改表存储参数可以为表禁用自动清理。注意即使该参数被禁用，系统也会在需要防止事务ID回卷时发起清理进程。
old_snapshot_threshold：设置可以使用查询快照的最小时间，以规避使用快照时出现“snapshot too old” 错误的风险，超过此阈值时间的数据将可以被清除，这可以有助于阻止长时间使用的快照造成的快照膨胀，默认值为 -1（禁用此功能）,实际上将快照的时限设置为无穷大。
log_autovacuum_min_duration：超过这个时间阀值的自动清理动作都会被日志记录，将该参数设置为0会记录所有的自动清理动作，默认值为 -1 （禁用对自动清理动作的记录）。此外，当该参数被设置为除-1外的任何值时，如果一个自动清理动作由于一个锁冲突或者被并发删除的关系而被跳过，将会为此记录一个消息。开启这个参数对于追踪自动清理活动非常有用，但是可以通过更改表的存储参数为个别表覆盖这个设置。
autovacuum_max_workers：设置能同时运行的自动清理进程（除了自动清理启动器之外）的最大数量，默认值为3
autovacuum_vacuum_scale_factor：触发 vacuum 自动清理操作的 dml 比例，默认值 0.2，当表上的 dml 操作占据表数据量的 20% 时触发 vacuum 自动清理操作，为防止数据量较小的表被频繁清理，与 autovacuum_vacuum_threshold（改参数默认值为 50，表中至少有 50 条数据发成 dml 操作时，才会触发 vacuum 自动清理）参数共同作用。
autovacuum_analyze_scale_factor：触发 vacuum 自动 analyze 操作的 dml 比例，默认值 0.1，当表上的 dml 操作占据表数据量的 10% 时触发 vacuum 自动 analyze 操作，为防止数据量较小的表被频繁 analyze，与 autovacuum_analyze_threshold（改参数默认值为 50，表中至少有 50 条数据发成 dml 操作时，才会触发 vacuum 自动 analyze）参数共同作用。
autovacuum_freeze_max_age：某表的pg_class.relfrozenxid的最大值，如果超出此值则重置xid，默认值为2亿，注意即便自动清理被禁用，系统也将发起自动清理进程来阻止回卷。
autovacuum_multixact_freeze_max_age：某表的pg_class.relminmxid最大值，如果超出此值则重置xid，默认值为4亿，注意即便自动清理被禁用，系统也将发起自动清理进程来阻止回卷。
autovacuum_vacuum_cost_delay：指定用于自动 VACUUM 操作中的代价延迟值，如果指定-1（默认值），则使用 vacuum_cost_delay 值（默认值 2ms）。