体系结构

C/S结构

PostgreSQL 是典型的 C/S 模型的关系型数据库。

进程简介

PM（连接）进程

postmaster进程，提供监听、连接协议、验证功能，fork其他进程。监听哪个IP是受到postgresql.conf影响的。默认提供sockte和TCP/IP方式连接。

SP（会话）进程

Session Processors，会话进程。用户验证成功就会fork一个新的进程。

SysLogger（系统日志）进程

需要在Postgres.conf 中 logging_collection设置为on，此使主进程才会启动Syslogger辅助进程。

BgWriter（后台写）进程

把共享内存中的脏页写到磁盘上的进程。主要是为了提高插入、更新和删除数据的性能。

WALWrite（预写式日志）进程

Write Ahead Log (预写式日志)，在修改数据之前把修改操作记录到磁盘中，以便后面更新实时数据时不需要数据持久化到文件中。

PgArch（归档）进程

WAL日志会被循环使用，PgArch在归档前会把WAL日志备份出来。通过PITY (Pointin Time Recovery)技术，可以对数据库进行一次全量备份后，该技术将备份时间点之后的WAL日志通过归档进行备份，使用数据库的全量备份再加上后面产生的WAL日志，即可把数据库向前推到全量备份后的任意一个时间点。

AutoVacuum（系统自动清理）进程

在PostgreSQL数据库中，对表进行DELETE操作后，旧的数据并不会立即被删除，并且，在更新数据时，也并不会在旧的数据上做更新，而是新生成一行数据。

旧的数据只是被标识为删除状态，只有在没有并发的其他事务读到这些旧数据时，他们才会被清除。这个清除工作就由AutoVacuum进程完成。

Pgstat（统计收集）进程

做数据的统计收集工作。主要用于查询优化时的代价估算，包括一个表和索引进行了多少次的插入、更新、删除操作。磁盘块读写的次数、行的读次数。pg_statistic中存储了PgStat收集的各类信息。

CheckPoint（检查点）进程

checkpoint又名检查点，一般checkpoint会将某个时间点之前的脏数据全本刷新到磁盘，以实现数据的一致性与完整性。目前各个流行的关系型数据库都具备checkpoint功能，其主要目的是为了缩短崩溃恢复时间，以Oracle为例，在进行数据恢复时，会以最近的Checkpoint为参考点执行事务前滚。而在WAL机制的浅析中，也提过PostgreSQL在崩溃恢复时会以最近的Checkpoint为基础，不断应用之后的WAL日志。

control 文件

记录了pg服务器内部的信息，如最新检查点时间、服务器状态、WAL文件位置，总之和数据库可能损坏相关的信息这里都有维护，还包括LC_CTYPE/LC_COLLATE等信息，和oracle控制文件差不多的。如果该文件被删除，实例将无法启动。运行时，会在共享内存中维护一份pg_control的镜像。

关键文件介绍

日志文件种类

$PGDATA/log 运行日志（pg10之前为$PGDATA/pg_log）
PGDATA/pg_wal 重做日志（pg10之前为$PGDATA/pg_xlog）
PGDATA/pg_xact 事务提交日志（pg10之前为$PGDATA/pg_clog）
服务器日志，可以在启动的时候指定，比如pg_ctl start -l ./alert.log

运行日志参数

参数	简介
log_destination = 'csvlog'	日志输出到哪 stderr, csvlog, syslog, and eventlog四选一，默认stderr
logging_collector = on	开启关闭日志默认为off / 需要重启
log_directory = 'log'	该参数是配置日志的目录，可以是绝对目录，也可以是相对目录
log_file_mode = 0600	日志文件的权限，默认是600也不用更改
logfilename = 'postgresql-%Y-%m-%d%H%M%S.log'	该参数是配置log的名字，一般用这个就行了，不用修改
log_truncate_on_rotation = off	当日志文件轮转时，名字已存在时，该配置如果为off，新生成的日志将在文件尾部追加，如果为on，则会覆盖原来的日志。同时也需要注意该参数只是针对时间到期的切换，如果是因为大小或者系统重启发生切换时，并不会覆盖已有的文件。
log_rotation_age = 1d	单个日志文件的生命周期，默认1天，在日志文件大小没有达到log_rotation_size时，一天只生成一个日志文件
log_rotation_size = 10MB	单个日志文件的大小，如果时间没有超过log_rotation_age，一个日志文件最大只能到10M，否则将新生成一个日志文件。
log_min_messages=warning	控制PG 系统日志级别，log_min_error_statement 要大于等于 log_min_messages 时，SQL语句才会被记录（默认ERROR，足够）。默认是WARNING。
log_min_duration_statement = -1	-1 表示不可用记录任何慢查0 将记录所有SQL语句和它们的耗时大于0时比如N，单位是ms，表示系统会记录耗时大于等于N ms的SQL语句
log_checkpoints = on	记录发生检查点的日志
log_connections = off	记录客户端会话连接操作
log_disconnections = off	记录客户断开的日志
log_duration = off	记录每条SQL语句执行完成消耗的时间，将此配置设置为on,用于统计哪些SQL语句耗时较长。开启该参数会消耗IO性能，如果客户端使用扩展查询协议，则会记录解析、绑定、执行三个阶段的时间。
log_lock_waits = off	控制会话等待时间超过 deadlock_timeout而被锁时是否产生一个日志信息。在判断一个锁等待是否会影响性能时是有用的，缺省是off。
log_statement = 'ddl'	控制记录哪些SQL语句。none不记录，ddl 记录所有数据定义命令，比如CREATE,ALTER,和DROP 语句。mod 记录所有ddl语句,加上数据修改语句INSERT,UPDATE等,all记录所有执行的语句，将此配置设置为all可跟踪整个数据库执行的SQL语句。

CSV日志入库存储

-- 创建日志存储表
CREATE TABLE postgres_log
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text,
  PRIMARY KEY (session_id, session_line_num)
);

-- 将日志导入表中
copy postgres.postgres_log from '/data/pg_log/postgresql-202-04-14_000000.csv' with csv;

postgresql.conf

参数	简介
listen_addresses = '*'	监听客户端的地址，默认是本地连接，需要需改为'*' 或者 0.0.0.0
port = 5432	pg连接端口，默认5432
max_connections = 1000	最大连接数默认100
unix_socket_directories = '/tmp'	sockte配置路径，默认 /tmp
shared_buffers	数据缓存区核心参数，建议值服务器内存的 1/4 - 1/2
maintenance_work_mem	维护工作内存。用于VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等操作，建议 (服务器内存的 1/4)/autovacuum_max_workers
autovacuum_max_workers	设置能同时运行的最大的自动垃圾收集工作进程的数目。默认值是3。
max_worker_processes = 8	如果要使用 worker process,最多可以允许 fork多少个 worker进程
max_parallel_workers_per_gather	单条query 允许开启并行计算的worker数，默认2
wal_level = minimal	预写日志模式 11+默认是replica
wal_buffers	需重启/指定事务日志缓冲区中包含的数据块的个数，每个数据块的大小是8KB，所以默认的事务日志缓冲区的大小是8*8=64KB。
checkpoint_timeout	checkpoint时间间隔
max_wal_size	控制wal的数量
min_wal_size	控制wal的数量
archive_command	开启归档
autovacuum	开启自动vacuum

控制文件

pg_controldata可以查看控制文件的内容

[postgres@test01 ~]$ pg_controldata $PGDATA
pg_control version number:            1201                # PostgreSQL 版本号
Catalog version number:               201909212           # 目录版本号
Database system identifier:           7356493727464752915 # 数据库系统标识符（DBSID）
Database cluster state:               in production       # 数据库集群状态
pg_control last modified:             Thu 11 Apr 2024 05:01:01 PM CST  # pg_control 最后修改时间
Latest checkpoint location:           0/6084AC0           # 最新检查点位置
Latest checkpoint's REDO location:    0/6084A88           # 最新检查点的 REDO 位置
Latest checkpoint's REDO WAL file:    000000010000000000000006  # 最新检查点的 REDO WAL 文件
Latest checkpoint's TimeLineID:       1      # 最新检查点的时间线 ID
Latest checkpoint's PrevTimeLineID:   1      # 最新检查点的上一个时间线 ID
Latest checkpoint's full_page_writes: on     # 最新检查点的全页写开关状态
Latest checkpoint's NextXID:          0:521  # 最新检查点的 NextXID
Latest checkpoint's NextOID:          24577  # 最新检查点的 NextOID
Latest checkpoint's NextMultiXactId:  1      # 最新检查点的 NextMultiXactId
Latest checkpoint's NextMultiOffset:  0      # 最新检查点的 NextMultiOffset
Latest checkpoint's oldestXID:        480    # 最新检查点的最旧事务 ID（oldestXID）
Latest checkpoint's oldestXID's DB:   1      # 最新检查点的最旧事务 ID 所属数据库
Latest checkpoint's oldestActiveXID:  521    # 最新检查点的最旧活跃事务 ID（oldestActiveXID）
Latest checkpoint's oldestMultiXid:   1      # 最新检查点的最旧多版本事务 ID（oldestMultiXid）
Latest checkpoint's oldestMulti's DB: 1      # 最新检查点的最旧多版本事务所属数据库
Latest checkpoint's oldestCommitTsXid:0      # 最新检查点的最旧提交时间戳事务 ID（oldestCommitTsXid）
Latest checkpoint's newestCommitTsXid:0      # 最新检查点的最新提交时间戳事务 ID（newestCommitTsXid）
Time of latest checkpoint:            Thu 11 Apr 2024 05:01:01 PM CST  # 最新检查点时间
Fake LSN counter for unlogged rels:   0/3E8  # 未记录关系的虚拟 LSN 计数器
Minimum recovery ending location:     0/0    # 最小恢复结束位置
Min recovery ending loc's timeline:   0      # 最小恢复结束位置的时间线
Backup start location:                0/0    # 备份开始位置
Backup end location:                  0/0    # 备份结束位置
End-of-backup record required:        no     # 是否需要备份结束记录
wal_level setting:                    replica  # WAL 日志级别设置
wal_log_hints setting:                on     # WAL 日志提示设置
max_connections setting:              2000   # 最大连接数设置
max_worker_processes setting:         8      # 最大工作进程数设置
max_wal_senders setting:              10     # 最大 WAL 发送者数设置
max_prepared_xacts setting:           0      # 最大预处理事务数设置
max_locks_per_xact setting:           64     # 每个事务的最大锁数设置
track_commit_timestamp setting:       off    # 跟踪提交时间戳设置
Maximum data alignment:               8      # 最大数据对齐大小
Database block size:                  8192   # 数据库块大小
Blocks per segment of large relation: 131072 # 大型关系的每个段的块数
WAL block size:                       8192   # WAL 块大小
Bytes per WAL segment:                16777216  # 每个 WAL 段的字节数
Maximum length of identifiers:        64     # 标识符的最大长度
Maximum columns in an index:          32     # 索引中的最大列数
Maximum size of a TOAST chunk:        1996   # TOAST 块的最大大小
Size of a large-object chunk:         2048   # 大对象块的大小
Date/time type storage:               64-bit integers  # 日期/时间类型存储方式
Float4 argument passing:              by value  # Float4 参数传递方式
Float8 argument passing:              by value  # Float8 参数传递方式
Data page checksum version:           0         # 数据页校验版本
Mock authentication nonce:            b1482276658b9aa5b638bad162d3d4fea064cbabd6889303f5d179131ad66c93  # 模拟认证 nonce

数据文件

pg中,每个索引和表都是一个单独的文件,pg中叫做page。默认是每个大于1G的page会被分割 pg_class.relfilenode.1这样的文件。page的的大小在initdb的时候指定 (--with.segsize)。

page物理位置

page的物理位置在 $PGDATA/BASE/DATABASE_OID/PG_CLASS.RELFILENODE

# 查看t1 表存储在哪个物理文件
select relfilenode from pg_class where relname='t1';

# 查看t1表存储在哪个路径下的物理文件
select pg_relation_filepath('t1');

# 查看数据目录路径
show data_directory;

# 查找文件是否存在
ll /pgdata/12/data/base/16384/16385

需要注意的是，pg_class.relfilenode类似dbaobjects.data_objectid，truncate表之后relfilenode会变。对应的物理文件名字也会变。

Online WAL 日志（redo）

关于Online WAL日志

这个日志存在的目的是为了保证崩溃后的安全，如果系统崩溃，可以“重放“从最后一次检查点以来的日志项来恢复数据库的一致性。

但是也存在日志膨胀的问题

设置Online WAL日志

pg提供如下參数控制wal日志的大小

max_wal_size = 1GB
min_wal_size = 80MB

max_wal_size (integerl)

在自动 WAL 检查点之间允许WAL 增长到的最大尺寸。这是一个软限制，在特殊的情况下 WAL 尺寸可能会超过max_waLsize，例如在重度负荷下、archive_command失败或者高的 wal_keep_segments设置。如果指定值时没有单位，则以兆字节为单位。默认为 1GB。增加这个参数可能导致崩溃恢复所需的时间。这个参数只能在postgresql.conf 或者服务器命令行中设置。

min_wal_size (integer)

只要 WAL 磁盘用是保持在这个设置之下，在检查点时旧的 WAL 文件总是被回收以便未来使用，而不是直接被删除。这可以被用来确保有足够的 WAL 空间被保留来应付 WAL 使用的高峰，例如运行大型的批处理任务。如果指定估时没有单位，则以兆字节为单位。默认是80MB。这个参数只能在postgresql。conf 或者服务器命令行中设置。

wal 位置

wal在$PGDATA/pg_wal 下。10之前在pg_xlog

[postgres@pg-01 pg_wal]$ cd /pgdata/12/data/
[postgres@pg-01 data]$ cd pg_wal/
[postgres@pg-01 pg_wal]$ ll
total 16384
-rw-------. 1 postgres postgres 16777216 Apr 22 22:16 000000010000000000000001
drwx------. 2 postgres postgres        6 Apr 20 21:01 archive_status

wal 命名格式

文件名称为16进制的24个字符组成，每8个字符一组，每组的意义如下：

[postgres@pg-01 pg_wal]$ ll
-rw-------. 1 postgres postgres 16777216 Apr 22 22:16 000000010000000000000001
00000001 00000000 00000001
时间线     逻辑ID    物理ID

查看 wal 时间

postgres=# select pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 000000010000000000000001
(1 row)

postgres=# select * from pg_ls_waldir() order by modification asc;
           name           |   size   |      modification      
--------------------------+----------+------------------------
 000000010000000000000001 | 16777216 | 2022-04-22 22:16:07+08
(1 row)

postgres=#

切换 wal 日志

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/3000000
(1 row)


postgres=# select * from pg_ls_waldir() order by modification asc;
           name           |   size   |      modification      
--------------------------+----------+------------------------
 000000010000000000000004 | 16777216 | 2022-04-22 22:54:41+08
 000000010000000000000005 | 16777216 | 2022-04-22 22:54:46+08
 000000010000000000000003 | 16777216 | 2022-04-22 22:56:03+08

pg_waldump 查看 wal 重做日志

pg_waldump 可以查看wal的具体内容

[postgres@pg-01 pg_wal]$ pg_waldump /pgdata/12/data/pg_wal/000000010000000000000003 
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/03000028, prev 0/02000060, desc: RUNNING_XACTS nextXid 527 latestCompletedXid 526 oldestRunningXid 527
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/03000060, prev 0/03000028, desc: RUNNING_XACTS nextXid 527 latestCompletedXid 526 oldestRunningXid 527
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/03000098, prev 0/03000060, desc: CHECKPOINT_ONLINE redo 0/3000060; tli 1; prev tli 1; fpw true; xid 0:527; oid 16422; multi 1; offset 0; oldest xid 480 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 527; online
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/03000110, prev 0/03000098, desc: RUNNING_XACTS nextXid 527 latestCompletedXid 526 oldestRunningXid 527
pg_waldump: fatal: error in WAL record at 0/3000110: invalid record length at 0/3000148: wanted 24, got 0

5.8 ARCH WAL log

重做日志是几个文件轮询使用的，当这几个文件被写满之后会覆盖掉之前的数据，而我们如果想要保存这些将要被覆盖掉的日志就需要ARCH进行归档。在数据库发生故障后可以将归档日志应用到数据库中进行数据恢复。

// wal_level = replica # pg11+ 默认开启replica
该参数的可选的值有minimal， replica和nlogical， wal的级别依次增高，在wal的信息也越多。由于minimal这一级别的wal不包含从基础的备份和wal日志重建数据的足够信息，在该模式下，无法开启wal日志归档。

// archive_mode = on # 开启归档
上述参数为on，表示打开归档备份，可选的参数为on，off，always 认值为off，所以要手动打开。

// archive. command = ’test ！-f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f‘
该参数的默认信是—个空字符串,他的值可以是一条shell命令或者一个复杂的shell脚本.在shell脚本或命令中可以用"%p"表示将要归档的wal文件包含完整路径的信息的文件名,用"%f代表不包含路径信息的wal文件的文件名。
注意：wal_level和archive_mode参数修改都需要重新启动数据库才可以生效。而修改 archive_command 则不需要。所以一般配置新系统时，无论当时是否需要归档，都建议将这两个参数开启。

配置参数

# 开启归档
archive_mode = on

# 指定归档日志级别
wal_level = replica 

# 选择归档路径
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

重启数据库

pg_ctl restart

切换日志

select pg_switch_wal();

# 查看归档文件
[postgres@pg-01 archive]$ ll /archive/
total 32768
-rw------- 1 postgres postgres 16777216 Apr 22 23:47 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Apr 22 23:47 000000010000000000000004

归档的作用是防止数据库删除过期的 wal 日志，可以在数据库出现问题后使用归档的 wal 恢复数据。

【一文搞懂PGSQL】3.进程和关键文件介绍