概览

PG是一个典型的c/s模型

体系结构=实例+存储结构
实例=进程+内存结构

实例结构

PG是多进程的，类似oracle，区别于MySQL的单进程多线程。
PostMaster进程(PM)处理连接层和部分SQL层相关的；
SP不光是处理连接，还处理会话中的SQL，包含两层功能；
BP是后台进程，比如ckpt，archive等等。
SGA共享内存区，功能类似buffer pool；
PGA私有内存区，为上面的SP分配区域。

进程结构

我们在OS上ps -ef |grep postgres能看到这些后台进程。

• PM进程：提供监听、连接协议、验证、fork其他进程。服务一启动就有。
  监听哪个ip是受到postgressql.conf影响的，提供socket和TCP/IP方式连接。
  验证功能是通过pg_hba.conf和用户验证模块。
• SP进程：会话进程，是真正的工作进程。
  用户一旦验证成功就会fork一个新的进程。
  然后完成整个SQL的预处理，语义语法检查，分析优化执行。
  可理解为充当了MySQL中连接线程和SQL线程的作用。
• BGW进程：background writer，主要负责后台刷新脏页。
• sysloger进程：主要负责数据库状态信息的日志记录。
• ckpt进程：checkpoint，检查点。
• walwriter：WAL日志（就是他的redo）的刷写进程。
• arch：归档进程，类比oracle的归档日志。
  -AV：数据整理，因为堆表(heap table)和索引组织表不一样。
  -SC：状态控制进程。

内存结构

（XLOG就是WAL在旧版本中的名称。）

存储结构

重点文件

日志文件种类

• $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,csvlog requires logging_collector to be on，一般选择csvlog，可以导入数据库中查看
logging_collector = on	选csv格式的日志必须设置这个参数on才行，修改需要重启才生效
log_directory = 'log'	日志输出目录
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'	日志文件名字的格式
log_file_mode=0600	日志文件权限
log_truncate_on_rotation = on	设置重用日志
log_rotation_age = 1d	多长时间重用日志
log_rotation_size = 10MB	日志达到多大重用
log_min_messages = warning	可选项由少到多分别有debug5,debug4,debug3,debug2,debug1,info,notice,warning,error,log,fatal,panic
log_min_duration_statement = 5	超过多长时间记录慢SQL
log_checkpoints = on	记录检查点操作
log_connections = on	记录会话连接操作
log_disconnections = on	记录会话断开操作
log_duration = on	记录SQL执行时间
log_lock_waits = on	log lock waits >= deadlock_timeout记录时间长的阻塞
log_statement='ddl'	none,ddl,mod,all 记录ddl

更全的请查看postgresql.conf查看说明，记录的越多压力越大，合理取舍。

csv日志入库存储
先建表

CREATE TABLE pg_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上去：

copy pg_log from '$PGLOG/postgresql-20210808.csv' with csv;

postgresql.conf基础配置

参数名	可选值及说明
listen_addresses='*'	监听客户端的地址，默认只监听本地，需要修改为*或者0.0.0.0
port = 5432	端口，默认是5432
max_connection = 2000	最大连接数，默认100
unix_socket_directories	socket文件的位置。默认在/tmp下面。一般不会配置多实例，所以一般不用修改
shared_buffers	数据缓存区，类比Oracle的buffer cache，建议值1/4至1/2主机内存
maintenance_work_mem	维护工作内存，用于vacuum,create index,reindex等，建议值(1/4主机内存)/autovacuum_max_workers
max_worker_processes	总worker数
max_parallel_workers_per_gather	单条query中，每个node最多允许开启的并行计算worker数
wal_level	wal的记录级别，11以上的版本默认是replica
wal_buffers	类似oracle的log buffer
checkpoint_timeout	checkpoint时间间隔
max_wal_size	控制wal的数量，最多不超过多少个
min_wal_size	控制wal的数量，默认3个轮转
archive_command	开归档的命令，其实就是拷贝命令，比如'test ! -f /disk1/digoal/arch/%f && cp %p /disk1/digoal/arch/%f'
autovacuum	开启自动vacuum

pg_hba.conf
PG防火墙访问控制配置，见之前的安装配置的远程连接配置。

pg_ident.conf
用户映射配置文件。结合pg_hba.connf，method为ident可以用特定的操作系统用户和指定的数据库用户登录数据库。使用几率不大。

控制文件
存储数据库当前状态的一些信息，但无法直接读取，需要通过pg_controldata查看控制文件的内容。

pg_controldata $PGDATA

重点关注dbid，主从信息，控制文件最后修改时间，checkpoint位置点，redo位置点，正在使用的redo文件，下一个事务id，下一个对象ID(oid)，wal级别，最大连接数，数据块大小(默认8k)，wal数据块大小，单个wal大小

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

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

查看t1表在哪个段上：

select relfilenode from pg_class where relname='t1';

查看对应的文件：

select pg_relation_filepath('t1');

查看数据目录：

show data_directory;

然后根据目录就能看到对应的文件，文件时二进制的无法打开。

online WAL
作用其实可以理解为redo日志，保证崩溃后的安全，一旦系统崩溃，可以重放从最后一次检查点以来的日志项来恢复数据库的一致性。
也存在日志膨胀的问题。
通过max_wal_size和min_wal_size控制wal日志的大小。

wal在$PGDATA/pg_wal下，未见名称为16进制的24个字符组成，每8个一组，前8位代表时间线，中间代表逻辑id，后8位代表物理id，我们比较关注物理id。

查看wal时间：

select pg_walfile_name(pg_current_wal_lsn());
select * from pg_ls_waldir() order by modification asc;

可以使用pg_waldump查看wal的具体内容，重点关注commit和commit之间的部分，虽然看不到具体SQL，不过大致进行了什么操作可以分辨；比如建库就是copy一步，建表就有一大堆insert，insert就insert和commit两步，删表就有一大堆delete，删库就是有delete有drop dir等等。
还可以看归档日志，因为格式一模一样。

手动切换下一个：

select pg_switch_wal();

arch WAL
生产环境中一般需要设置归档；所谓归档其实就是把pg_wal里面的日志备份出来，当系统故障后可以通过归档的日志文件对数据进行恢复。
配置归档需要开启如下参数：

• wal_level = replica (pg11默认是replica，配置文件打开即可)
  该参数的可选值有minimal，replica和logical，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_comman则不需要。因此一般配置新系统时，无论是否需要归档，都建议将这两个参数开启。归档日志目录可以定制，应该提前创建好。