史上最全postgreSQL体系结构（转）
1.概述
PostgreSQL是最像Oracle的开源数据库，我们可以拿MySQL和Oracle来比较学习它的体系结构，比较容易理解。本文会讨论pg的如下结构：
- 日志文件
- 参数文件
- 控制文件
- 数据文件
- redo日志（WAL）
- 后台进程
PostgreSQL的主要结构如下：
2.日志文件
2.1.日志文件种类
-
$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
2.2.运行日志
2.2.1 运行日志参数
运行日志主要相关的参数如下，默认没有开启的话没有log目录，开启后会自动生成。
| 参数 | 可选值/说明 |
|---|---|
| log_destination = 'csvlog' | # stderr, csvlog, syslog, and eventlog ,csvlog requires logging_collector to be on一般选择这个，可以将csv日志导入数据库中查看 |
| logging_collector=on | # Enable capturing of stderr and csvlog into log files选scv格式的日志必须设置这个参数on，修改需要重启 |
| log_directory = 'log' | 日志输出目录 |
| log_filename='postgresql-%Y-%m-%d_%H%M%S.log | 日志文件名字格式 |
| log_file_mode=0600 | # creation mode for log files 日志文件权限 |
| 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 = 60 | 慢sql记录（超过多长时间的sql） |
| log_checkpoints = on | 记录checkpoint操作 |
| 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 |
2.2.2 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)
);
< pg@cs-db /oracle/soft/data/log 16:12 --> pwd
/oracle/soft/data/log
< pg@cs-db /oracle/soft/data/log 16:12 --> ls -rtl
total 8
-rw------- 1 pg pg 168 Nov 10 16:08 postgresql-16.log
-rw------- 1 pg pg 502 Nov 10 16:08 postgresql-16.csv
< pg@cs-db /oracle/soft/data/log 16:12 --> pwd
/oracle/soft/data/log
< pg@cs-db /oracle/soft/data/log 16:12 --> psql
psql (11beta3)
Type "help" for help.
li=# copy pg_log from '/oracle/soft/data/log/postgresql-16.csv' with csv;
COPY 3
这样就可以用sql来查看了。
2.3 pg_wal
见第六部分
2.4 事务日志pg_xact
pg_xact是事务提交日志，记录了事务的元数据。默认开启。内容一般不能直接读。
2.5 服务器日志
如果用pg_ctl启动的时候没有指定-l参数来指定服务器日志，错误可能会输出到cmd前台。服务器日志记录了数据库的重要信息，一个服务器日志的内容如下：
[postgres@whf307 log]$ more alert.log
2019-05-16 14:11:36.718 CST [14660] LOG: listening on IPv4 address "0.0.0.0", port 5432
2019-05-16 14:11:36.718 CST [14660] LOG: listening on IPv6 address "::", port 5432
2019-05-16 14:11:36.720 CST [14660] LOG: listening on Unix socket "/data/.s.PGSQL.5432"
2019-05-16 14:11:36.729 CST [14660] LOG: redirecting log output to logging collector process
2019-05-16 14:11:36.729 CST [14660] HINT: Future log output will appear in directory "log".
lsof或许可以过滤出在写的日志文件
lsof -c postgres| grep REG | grep -v /usr | grep -v /dev | grep -v /SYS
3.参数文件
3.1 postgresql.conf
pg的主要参数文件，有很详细的说明和注释，和Oracle的pfile，MySQL的my.cnf类似。默认在$PGDATA下。很多参数修改后都需要重启。9.6之后支持了alter system来修改，修改后的会报存在$PGDATA/postgresql.auto.conf下，可以reload或者 restart来使之生效。
主要的参数如下：
| 参数 | 可选值/说明 |
|---|---|
| listen_addresses='*' | 监听客户端的地址，默认是本地的，需要修改为*或者0.0.0.0 |
| port = 5432 | pg端口，默认是5432 |
| max_connections = 2000 | 最大连接数，默认100 |
| unix_socket_directories | socket文件的位置，默认在/tmp下面 |
| shared_buffers | 数据缓存区，类型Oracle的buffer cache。建议值 1/4主机内存 |
| 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的数量 |
| archive_command | 开归档，比如 'test ! -f /disk1/digoal/arch/%f && cp %p /disk1/digoal/arch/%f' |
| autovacuum | 开启自动vacuum |
3.2 pg_hba.conf
这个是黑白名单的设置。文件里有详细的参数说明，默认参数如下：
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
type 列有local，host，hostssl，hostnossl四种。local是本地认证
database 可以是all，或者指定的数据库
**user ** 列可以是all，或者具体的用户
address 可以是ip或者网段
method 比较重要，有"trust", "reject", "md5", "password", "scram-sha-256", # "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert"这么多可选。trust是免密登录；reject是黑名单拒绝；md5是加密的密码；password是没有加密的密码；ident是Linux下PostgreSQL默认的local认证方式，凡是能正确登录服务器的操作系统用户（注：不是数据库用户）就能使用本用户映射的数据库用户不需密码登录数据库
3.3 pg_ident.conf
pg_ident.con是用户映射配置文件。结合pg_hba.connf中，method为ident可以用特定的操作系统用户和指定的数据库用户登录数据库。如下：
pg_ident.conf如下：
# MAPNAME SYSTEM-USERNAME PG-USERNAME
mapll test sa
pg_hba.conf如下：
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all ident map=mapll
4.控制文件
4.1 控制文件位置
$PGDATA/global/pg_control
控制文件在数据库目录的global目录下。控制文件记录了数据库的重要信息。
4.2 查看控制文件
pg_controldata可以查看控制文件的内容
[postgres@whf307 global]$ pg_controldata $PGDATA
pg_control version number: 1100
Catalog version number: 201809051
Database system identifier: 6684270596680436587 #dbid
Database cluster state: in production # primary
pg_control last modified: Thu 16 May 2019 02:26:37 PM CST
Latest checkpoint location: 0/48812A0
Latest checkpoint's REDO location: 0/4881268 #redo 位置
Latest checkpoint's REDO WAL file: 000000010000000000000001 #wal文件号
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:572 #下一个事务id
Latest checkpoint's NextOID: 16388 #下一个OID
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 561
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 572
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Thu 16 May 2019 02:26:36 PM CST
Fake LSN counter for unlogged rels: 0/1
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: off
max_connections setting: 100 #最大连接数
max_worker_processes setting: 8
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: 67108864 #单个wal大小
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 1
Mock authentication nonce: f01b78f5a88882f2f1811bbbc637cc4766d57d39831139a6b3e881d76272d892
[postgres@whf307 global]$
controlfile记录了数据库运行的一些信息，比如数据库id，是否open，wal的位置，checkpoint的位置，等等。controlfile是很重要的文件，数据库部署和调整。
5.数据文件
5.1 page
pg中，每个索引和表都是一个单独的文件，pg中叫做page。默认是每个大于1G的page会被分割pg_class.relfilenode.1这样的文件。page的大小在initdb的时候指定（--with-segsize）。
5.2 page物理位置
page的物理位置在$PGDATA/BASE/DATABASE_OID/PG_CLASS.RELFILENODE
mydb=# select relfilenode from pg_class where relname='t1';
relfilenode
-------------
16385
(1 row)
mydb=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16384/16385
(1 row)
mydb=# show data_directory;
data_directory
----------------
/data
(1 row)
mydb=# \q
[postgres@whf307 global]$ ls -rtl /data/base/16384/16385
-rw------- 1 postgres postgres 8192 May 16 14:26 /data/base/16384/16385
[postgres@whf307 global]$
需要注意的是，pg_class.relfilenode类似dba_objects.data_object_id，truncate表之后relfilenode会变。对应的物理文件名字也会变。
6.WAL日志
6.1 wal位置
wal在$PGDATA/pg_wal下。10之前为pg_xlog
[postgres@whf307 data]$ cd pg_wal
[postgres@whf307 pg_wal]$
[postgres@whf307 pg_wal]$ ls -rtl
total 65536
drwx------ 2 postgres postgres 6 Apr 27 02:23 archive_status
-rw------- 1 postgres postgres 67108864 May 16 15:56 000000010000000000000001
[postgres@whf307 pg_wal]$
6.2 wal命名格式
文件名称为16进制的24个字符组成，每8个字符一组，每组的意义如下：
00000001 00000000 00000001
-------- -------- --------
时间线 逻辑id 物理id
6.3 查看wal时间
postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000001
(1 row)
postgres=#
postgres=# select * from pg_ls_waldir() order by modification asc;
name | size | modification
--------------------------+----------+------------------------
000000010000000000000001 | 67108864 | 2019-05-16 15:56:32+08
(1 row)
postgres=#
6.4 切换wal
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/48814E8
(1 row)
postgres=# select * from pg_ls_waldir() order by modification asc;
name | size | modification
--------------------------+----------+------------------------
000000010000000000000001 | 67108864 | 2019-05-16 16:12:29+08
000000010000000000000002 | 67108864 | 2019-05-16 16:12:30+08
(2 rows)
6.5 pg_waldump查看wal
pg_waldump可以查看wal的具体内容
7、后台进程
postgres 15309 1 0 15:51 pts/0 00:00:00 /pg/pg11/bin/postgres
postgres 15310 15309 0 15:51 ? 00:00:00 postgres: logger
postgres 15312 15309 0 15:51 ? 00:00:00 postgres: checkpointer
postgres 15313 15309 0 15:51 ? 00:00:00 postgres: background writer
postgres 15314 15309 0 15:51 ? 00:00:00 postgres: walwriter
postgres 15315 15309 0 15:51 ? 00:00:00 postgres: autovacuum launcher
postgres 15316 15309 0 15:51 ? 00:00:00 postgres: stats collector
postgres 15317 15309 0 15:51 ? 00:00:00 postgres: logical replication launcher
- postgres 主进程
- logger 日志进程
- checkpointer checkpoint进程
- background writer 数据文件写进程
- walwriter wal写进程
- autovacuum launcher autovacuum进程
- stats collector 统计信息收集进程
本文为转载，原文：史上最全PostgreSQL体系结构
