【PG体系架构】Postgresql 体系架构学习总结.md 转载 https://www.modb.pro/db/100597?utm_source=index_ai
Table of Contents
- 1 PG的逻辑结构
- 2 PG的内存结构
- 3 实例架构
- 4 PG的物理结构
- 5 进程结构
- 5.1 运行流程分析
- 5.2 Postmaster主进程和服务进程
- 5.3 BgWriter（后台写）进程
- 5.4 PgArch（归档）进程
- 5.5 PgStat（统计数据收集）进程
- 5.6 AutoVacuum（自动清理）进程
- 5.7 WalWriter（预写式日志写）进程
- 5.8 CheckPoint（检查点）进程
- 5.9 SysLogger进程
- 5.10 postmaster.pid
- 5.11 客户端进程
- 6 网络架构
PostgreSQL数据库是由一系列位于文件系统上的物理文件组成，在数据库运行过程中，通过整套高效严谨的逻辑管理这些物理文件。通常将这些物理文件称为数据库，将这些物理文件、管理这些物理文件的进程、进程管理的内存称为这个数据库的实例。在PostgreSQL的内部功能实现上，可以分为系统控制器、查询分析器、事务系统、恢复系统、文件系统这几部分。其中系统控制器负责接收外部连接请求，查询分析器对连接请求查询进行分析并生成优化后的查询解析树，从文件系统获取结果集或通过事务系统对数据做处理，并由文件系统持久化数据。
1 PG的逻辑结构
1.1 PG对比ORACLE的逻辑结构
逻辑结构
一个数据库实例对应多个数据库（ PostgreSQL MySQL SQLServer）
一个数据库实例对应一个数据库（ Oracle）
多个数据库实例对应—个数据库（ ORACLE RAC）
一个PostgreSQL数据库服务下可以有多个数据库，多个数据库之间无法互相访问，需要访问需要使用dblink等手段。
ORACLE数据库一个实例只能有属于一个数据库，一个库可以有多个实例，比如RAC就是如此。Post的这种其实和sql server， MYSQL都类似。
Database，instance
| Postgresql | ORACLE | SQL Server |
|---|---|---|
| 一个库不能属于多个实例 | 一个库可以有多个实例 | |
| 一个实例下有多个库 | 一个实例只能属于一个库 |
Schema
在PostgreSQL中Schema可以理解为一个命名空间或目录，每个schema有各自的表，视图等对象，不同的schema下名称可以相同。ORACLE的Schema功能上和PostgreSQL相同，但是ORACLE一个用户即一个Schema。
1.2 PG的逻辑结构
-
Database cluster（数据库集簇）: 由postgresql server管理的数据库的集合，下面由多个database组成。一个数据库集簇可以包含多个Database、多个User，每个Database以及Database中的所有对象都有它们的所有者：User。
-
Database数据库：Postgres 默认数据库、Template0 最精简模板、Template1 默认模板；数据库本身也是数据库对象，并且在逻辑上彼此分离。存储 schema 的一个逻辑空间，对应在物理层面上也是一个目录。
-
Schema：一个数据库可以有多个User用户，多个Schema模式，默认创建一个数据库会存储在默认表空间，它包含一个Public名称的Schema模式（拷贝自Template1模板）。它可隔离多个用户之间相同名称的对象。一个数据库可以有多个Schema模式，他们互不相关互相隔离。实际存储数据库对象的逻辑空间–>逻辑上的对象编号，schema是依附于数据库而存在的。
-
**User用户：**postgres用户是默认创建的超级管理员；每个数据库都有一个OWNER用户，每个用户可以OWNER多个数据库。
-
数据库对象：这里包含了table，index，view，序列，函数等，数据最终存储在表中。表的组成表由多个 page [ block]组成）一个page包含（页头信息空闲空间 Tuple），实际存储数据的区域–>对应到物理层面上就是文件 --> page构成
-
**tablespace：**存储数据库的一个逻辑空间，可以存放不同的数据库 -->对应在物理层面上是一个目录
-
OID：所有数据库对象都有各自的oid(object identifiers),oid是一个无符号的四字节整数，相关对象的oid都存放在相关的system catalog表中，比如数据库的oid和表的oid分别存放在pg_database,pg_class表中。
总结：
1、从大小排列 database cluster-->databases-->schema-->objects
2、Tablespace是数据最大的存储空间， Database是构成表空间的存储单元，pages是 PostgreSQL数据库中最小的IO单元
1.3 逻辑结构管理
1.3.1 数据库
1.3.1.1 创建数据库
create database name
[ [with] [owner [=] user_name ] // 指定新建的数据库属于哪个用户，不指定使用当前用户。
[template [=] template] // 模板名（从哪个模板创建，不指定使用默认template1）
[encoding [=] encoding ] // 字符集编码
[lc_collate [=] lc_ctype]
[tablespace [=] tablespace ] // 指定的新数据库关联 表空间 的名字
[connection limit [=] connlimit] // 数据库可以接受多少并发连接，默认-1（无限制）
[] 中的参数都可省略为create database db_name;
postgres@[local]:5432=# create database test owner=pguser template=template1 encoding=utf8 tablespace=tbs_mydb CONNECTION LIMIT=1000;
CREATE DATABASE
postgres@[local]:5432=#
postgres@[local]:5432=#
postgres@[local]:5432=#\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privile
ges
-----------+----------+----------+-------------+-------------+-----------------
------
mydb | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+
| | | | | postgres=CTc/pos
tgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+
| | | | | postgres=CTc/pos
tgres
test | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
连接数据库
postgres@[local]:5432=#\c test pguser
You are now connected to database "test" as user "pguser".
test@[local]:5432=#select current_database;
ERROR: column "current_database" does not exist
LINE 1: select current_database;
^
test@[local]:5432=#select current_database();
current_database
------------------
test
(1 row)
test@[local]:5432=#select current_user;
current_user
--------------
pguser
(1 row)
postgres@[local]:5432=#select * from pg_database;
oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid |
datminmxid | dattablespace | datacl
-------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+
------------+---------------+-------------------------------------
14187 | postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 14186 | 479 |
1 | 1663 |
16389 | mydb | 16387 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 14186 | 479 |
1 | 16388 |
1 | template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 14186 | 479 |
1 | 1663 | {=c/postgres,postgres=CTc/postgres}
14186 | template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 14186 | 479 |
1 | 1663 | {=c/postgres,postgres=CTc/postgres}
16409 | test | 16387 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | 1000 | 14186 | 479 |
1 | 16388 |
(5 rows)
postgres@[local]:5432=#
1.3.1.2 调整数据库
test@[local]:5432=#\h alter database
Command: ALTER DATABASE
Description: change a database
Syntax:
ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:
ALLOW_CONNECTIONS allowconn
CONNECTION LIMIT connlimit
IS_TEMPLATE istemplate
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL
修改数据库的最大连接数
alter database test connection limit 10;
修改数据库名称
alter database test rename to testdb1;
关闭在数据库testdb上的默认索引扫描
alter database testdb set enable_indexscan to off;
1.3.1.3 删除数据库
drop database [if exists] name;
注意：如果有人连接这个数据库，则不能删除；
不能再事物块中删除数据库；可以修改。
1.3.1.4 其他脚本
查看database属性
postgres@[local]:5432=#select * from pg_database;
oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid |
datminmxid | dattablespace | datacl
-------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+
------------+---------------+-------------------------------------
14187 | postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 14186 | 479 |
1 | 1663 |
16389 | mydb | 16387 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 14186 | 479 |
1 | 16388 |
1 | template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 14186 | 479 |
1 | 1663 | {=c/postgres,postgres=CTc/postgres}
14186 | template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 14186 | 479 |
1 | 1663 | {=c/postgres,postgres=CTc/postgres}
16409 | test | 16387 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | 1000 | 14186 | 479 |
1 | 16388 |
(5 rows)
查看database大小
SELECT
datname,
pg_size_pretty(pg_database_size(datname))
FROM
pg_database
ORDER
BY pg_database_size(datname) DESC;
datname | pg_size_pretty
-----------+----------------
mydb | 9033 kB
postgres | 8201 kB
test | 8193 kB
template1 | 8049 kB
template0 | 8049 kB
查看database路径
postgres@[local]:5432=#SHOW data_directory;
data_directory
---------------------
/data/pg12.5/pgdata
(1 row)
1.3.2 schema
定义：模式可以理解为一个命名空间或者目录。不同模式下可以有相同名称的表，函数等对象且互相不冲突。每个模式的对象可以相互调用。
一个数据库可以包含多个模式，一个模式中可以包含表、函数以及数据库对象。
postgresql中，不能同时访问不同数据库中的对象，而模式没有此限制。schema的这点概念类似于mysql中的database。
使用schema的原因：
允许多个用户在使用同一个数据库时互不干扰。
把数据库对象放在不同的模式下，然后组成逻辑组，让他们更便于管理。
第三方应用可以放在不同的模式中，这样就不会和其它对象的名字冲突了。
1.3.2.1 创建、查看、删除一个schema
create schema osdba;
mydb@[local]:5432=#\dn
List of schemas
Name | Owner
--------+----------
osdba | pguser
public | postgres
(2 rows)
mydb@[local]:5432=#drop schema osdba;
DROP SCHEMA
1.3.2.3 为pguser创建schema
postgres@[local]:5432=#create schema authorization pguser;
CREATE SCHEMA
同时创建一些对象
create schema osdba
create table t1 (id int, title text)
create table t2 (id int, content text)
create view v1 as
select a.id,a.title,b.content from t1 a,t2 b where a.id=b.id;
alter schema osdba rename to osdbaold；
alter schema osdbaold owner to xxx;
1.3.2.3 搜索路径
show search_path;
postgres@[local]:5432=#show search_path;
search_path
-----------------
"$user", public
(1 row)
默认的search_path 是public
设置搜索路径时不需要添加引号，否则会导致无法访问数据表
如果不同的schema下有相同的表名出现，那么数据库将会自动根据search_path的顺序展示出第一个schema下的对象，其余的不显示
postgres@[local]:5432=# SELECT a.relname,b.nspname
postgres-# FROM pg_class a ,pg_catalog.pg_namespace b
postgres-# WHERE a.relnamespace = b.oid AND b.nspname IN ('osdba');
relname | nspname
---------+---------
t1 | osdba
t2 | osdba
v1 | osdba
(3 rows)
postgres@[local]:5432=#
调整
postgres@[local]:5432=#set search_path = "$user", public ,osdba
postgres-# ;
SET
postgres@[local]:5432=#show search_path;
search_path
------------------------
"$user", public, osdba
(1 row)
postgres@[local]:5432=#\dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
osdba | t1 | table | postgres
osdba | t2 | table | postgres
public | test | table | postgres
(3 rows)
postgres@[local]:5432=#
1.3.3 tablespace
在PostgreSQL中最大的逻辑存储单位是表空间，数据库中创建的对象都保存在表空间中，例如表、索引和整个数据库都可以被分配到特定的表空间。在创建数据库对象时，可以指定数据库对象的表空间，如果不指定则使用默认表空间，也就是数据库对象的文件的位置。初始化数据库目录时会自动创建pg_default和pg_global两个表空间。如下所示：
postgres@[local]:5432=#\db
List of tablespaces
Name | Owner | Location
------------+----------+------------------------------
pg_default | postgres |
pg_global | postgres |
tbs_mydb | pguser | /data/pg12.5/pgdata/tbs_mydb
(3 rows)
❑ pg_global表空间的物理文件位置在数据目录的global目录中，它用来保存系统表。
❑ pg_default表空间的物理文件位置在数据目录中的base目录，是template0和template1数据库的默认表空间，我们知道创建数据库时，默认从template1数据库进行克隆，因此除非特别指定了新建数据库的表空间，默认使用template1的表空间，也就是pg_default。
1.3.4 objects
查看所有表大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
2 PG的内存结构
Oracle的内存结构
PG的内存结构
在Postgresql中，内存大概被分为两块
Local memory area – 为每一个backend process 分配的内存
Shared memory area – PostgreSQL server 所有的backgroud process使用的内存
2.1 Local memory area
每一个backend process 都会分配一块local memory area, 每一块区域又分为三个子区域 ，见下表
| sub-area | description | reference |
|---|---|---|
| work_mem | 用户在sort,distinct,merge join，hash join的时候会用到这块区域，内部排序聚合操作和hash表在使用临时磁盘文件时使用的内存缓冲区，需要关注的是：每个排序操作都会消耗一个work_mem内存，并不是一个SQL消耗一个，因此，当您的系统中有大量的排序时，此值可适当调小，防止内存用尽 | Chapter 3 |
| maintenance_work_mem | vacuum，reindex会用到这块区域 | Section 6.1 |
| temp_buffers | 存储临时表会用到这块区域 |
2.2 Shared memory area
这块区域在服务器启动的时候分配，这块区域也是分为好几个子区域，见下面介绍
| sub-area | description | reference |
|---|---|---|
| shared buffer pool | 将表或者索引的page从磁盘加载到shared buffer，然后在shared buffer操作 | Chapter 8 |
| WAL buffer | 在服务端出现问题的时候，确保数据不会丢失，在写到磁盘之前，wal buffer是wal log的缓存区域 | Chapter 9 |
| commit log | 为了并发控制所有事物的状态的保持而分配的区域 |
另外，Postgresql还分配一些其他的内存区域：
- 为访问控制分配的子区域，比如轻量级锁，共享或者专有锁.
- 为其他backgroud process提供的子区域，比如检查点，vacuum.
- 为事物处理提供的子区域，比如事物中的保存点，和二阶段事物提交.
3 实例架构
实例是由数据库内存，后台进程和数据库集簇组成的。
4 PG的物理结构
数据库的文件默认保存在initdb时创建的数据目录中。在数据目录中有很多类型、功能不同的目录和文件，除了数据文件之外，还有参数文件、控制文件、数据库运行日志及预写日志等。
本质上都是PG的相关一些文件
- DATABASE CLUSTER 数据库集簇–多个数据库的集合
- BASE目录（包含一系列子目录和文件）
4.1 软件安装目录
PostgreSQL数据库的软件目录通常是在/usr 目录下（也可自定义位置）,使用pg_confg命令可以看到当前数据库的基本情况，也可以在环境变量中可以看到。最直接可以使用which命令看到
[postgres@pgserver12 base]$ which psql
/data/pg12.5/bin/psql
[postgres@pgserver12 base]$
安装目录里的基本内容如下
[postgres@pgserver12 base]$ cd /data/pg12.5/
[postgres@pgserver12 pg12.5]$ ll
total 16
drwxrwxr-x. 2 postgres postgres 4096 Aug 13 21:19 bin
drwxrwxr-x. 4 postgres postgres 4096 Aug 13 21:19 include
drwxrwxr-x. 4 postgres postgres 4096 Aug 13 21:19 lib
drwx------. 20 postgres postgres 4096 Aug 16 22:29 pgdata
drwxrwxr-x. 5 postgres postgres 46 Aug 13 21:19 share
[postgres@pgserver12 pg12.5]$
其中各个目录里的内容及用途：
- bin：二进制可执行文件，是PG数据库的所有相关命令所在，为方便使用需设置到环境变量中
- include：头文件目录
- lib ：动态库目录，PostgreSQL运行所需要的动态库都在此目录下
- share：放有文档和配置模板文件，一些拓展插件的SQL文件在此目录下的extension中
4.2 数据库目录结构
-bash-4.2$ tree -L 1 -d /data/pg12.5/pgdata/
/data/pg12.5/pgdata/ --数据目录
├── base --表和索引文件存放目录
├── global --影响全局的系统表存放目录
├── pg_commit_ts --事务提交时间戳数据存放目录
├── pg_dynshmem --被动态共享所使用的文件存放目录
├── pg_logical --用于逻辑复制的状态数据
├── pg_multixact --多事务状态的数据
├── pg_notify --LISTEN/NOTIFY状态的数据
├── pg_replslot --复制槽数据存放目录
├── pg_serial --已提交的可序列化信息存放目录
├── pg_snapshots --快照
├── pg_stat --统计信息
├── pg_stat_tmp --统计信息子系统临时文件
├── pg_subtrans --子事务状态数据
├── pg_tblspc --表空间
├── pg_twophase --预备事务状态文件
├── pg_wal --事务日志（预写日志）
└── pg_xact --日志提交状态的数据存放目录
文件和目录相关作用描述
| files | description |
|---|---|
| PG_VERSION | 包含postgresql主版本号的文件 |
| pg_hba.conf | 控制postgresql客户端验证的文件 控制PG客户端认证文件 主机 数据库 用户 ip地址 认证方式 |
| pg_ident.conf | 控制postgresql用户名映射的文件, 配置操作系统用户和数据库服务器上的用户映射 |
| postgresql.conf | 配置参数文件 |
| postgresql.auto.conf | 用于存储在ALTER SYSTEM（版本9.4或更高版本）中设置的配置参数的文件 |
| postmaster.opts | 记录服务端上一次启动的命令行选项 |
| subdirectories | description |
| base/ | 包含每个数据库子目录的子目录 |
| global/ | 包含群集范围表的子目录，例如pg_database和pg_control |
| pg_commit_ts/ | 包含事务提交时间戳数据的子目录。 9.5版本以后 |
| pg_clog/ (Version 9.6 or earlier) | 包含事务提交状态数据的子目录。它在版本10中重命名为pg_xact. CLOG将在5.4章节中详解。. |
| pg_dynshmem/ | 包含动态共享内存子系统使用的文件的子目录。9.4版本以后 |
| pg_logical/ | 包含逻辑解码的状态数据的子目录。9.4版本以后 |
| pg_multixact/ | 包含多事务状态数据的子目录（用于 shared row locks） |
| pg_notify/ | 包含LISTEN / NOTIFY状态数据的子目录 |
| pg_repslot/ | 包含复制槽数据的子目录（9.1版本以后） |
| pg_serial/ | 包含有关已提交的序列化事务（9.1版本以后）信息的子目录 |
| pg_snapshots/ | 包含导出快照的子目录（9.2版本以后）。 PostgreSQL的函数pg_export_snapshot在此子目录中创建快照信息文件 |
| pg_stat/ | 包含统计子系统永久文件的子目录 |
| pg_stat_tmp/ | 包含统计子系统临时文件的子目录 |
| pg_subtrans/ | 包含子事物状态数据的子目录 |
| pg_tblspc/ | 表空间符号链接目录 |
| pg_twophase/ | 包含prepare事务的状态文件 |
| pg_wal/ (Version 10 or later) | 包含WAL（Write Ahead Logging）段文件的子目录。在版本10中从pg_xlog重命名而来. |
| pg_xact/ (Version 10 or later) | 包含事务提交状态数据的子目录。在版本10中从pg_clog重命名而来.CLOG将在5.4章节中详解 |
| pg_xlog/ (Version 9.6 or earlier) | 包含WAL（Write Ahead Logging）段文件的子目录。在版本10中重命名为pg_ |
数据库有两个基础的对象 一个是oid，一个是表空间。
oid是数据库对象的标识。
表空间实际上是文件系统中的一个位置链接，即一个目录,它是一个逻辑上的概念，目录是它的物理存在方式。数据库中创建的对象（表、索引、数据库对象）都保存在表空间中。postgresql初始化完成后，会有两个默认的表空间，一个是pg_default，如果用户建表时没有指定表空间，所有新建的表都会放在pg_default中，另一个是pg_global,存放的是整个实例数据库所共享的系统表。
4.2.1 base的物理布局设计
每个数据库都会在$PGDATA/base下面生成一个子目录，如下图，都会一一对应。
[postgres@pgserver12 base]$ ll
total 36
drwx------. 2 postgres postgres 8192 Aug 13 21:20 1
drwx------. 2 postgres postgres 8192 Aug 13 21:20 14186
drwx------. 2 postgres postgres 8192 Aug 17 19:59 14187
postgres@[local]:5432=#select datname,oid from pg_database;
datname | oid
-----------+-------
postgres | 14187
mydb | 16389
template1 | 1
template0 | 14186
test | 16409
(5 rows)
表空间跟数据库关系
在Oracle数据库中；一个表空间只属于一个数据库使用；而一个数据库可以拥有多个表空间。属于"一对多"的关系
在PostgreSQL集群中；一个表空间可以让多个数据库使用；而一个数据库可以使用多个表空间。属于"多对多"的关系。
系统自带表空间
表空间pg_default是用来存储系统目录对象、用户表、用户表index、和临时表、临时表index、内部临时表的默认空间。对应存储目录$PADATA/base/
表空间pg_global用来存放系统字典表；对应存储目录$PADATA/global/
4.3 表和索引的物理布局设计
每一个表和索引如果不超过1G大小，都只有一个文件。表和索引也有和数据库一样的OID，另外还有一个relfilenode,这个值不会总是匹配OID，在发生一truncate,reindex,cluster等相关的操作，会发生变化，见如下示例：
可以看到开始oid和relfilenode是一样的，truncate后，relfilenode发生了变化.
1 创建用户和数据库
postgres@[local]:5432=#create user yanwei SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
CREATE ROLE
postgres@[local]:5432=#
postgres@[local]:5432=#
postgres@[local]:5432=#alter user yanwei with ENCRYPTED PASSWORD 'yanwei';
ALTER ROLE
postgres@[local]:5432=#create database yanwei owner = yanwei ;
CREATE DATABASE
postgres@[local]:5432=#\c yanwei yanwei
You are now connected to database "yanwei" as user "yanwei".
yanwei@[local]:5432=#select datname,oid from pg_database;
datname | oid
-----------+-------
postgres | 14187
mydb | 16389
template1 | 1
template0 | 14186
test | 16409
yanwei | 16433
2 创建表和查询路径
yanwei@[local]:5432=#create table test (id int,name varchar(10)); CREATE TABLE yanwei@[local]:5432=#SELECT relname, oid, relfilenode FROM pg_class WHERE relname = 'test'; relname | oid | relfilenode ---------+-------+------------- test | 16434 | 16434 (1 row) 查询路径 [postgres@pgserver12 16433]$ ll 16434* -rw-------. 1 postgres postgres 0 Aug 17 22:19 16434 [postgres@pgserver12 16433]$ pwd /data/pg12.5/pgdata/base/16433 truncate表重新验证
yanwei@[local]:5432=#truncate table test
yanwei-# ;
TRUNCATE TABLE
yanwei@[local]:5432=#SELECT relname, oid, relfilenode FROM pg_class WHERE relname = 'test';
relname | oid | relfilenode
---------+-------+-------------
test | 16434 | 16437
(1 row)
yanwei@[local]:5432=#SELECT pg_relation_filepath('test');
pg_relation_filepath
----------------------
base/16433/16437
(1 row)
yanwei@[local]:5432=#
如果表和索引超过1GB， PostgreSQL对该表或者索引会进行拆分
relfilenode.1形成一个新文件，如果该新文件也被装满（1GB），那么
efilenode2就会是一个新文件
insert into test select id， id||'adfadfasdf'
from generate series（1， 25000000）id
[postgres@sdedu 17306]S ll-h 17310
-rw------1 postgres postgres 1.0G Feb 4 20： 14 17310
-rw------ 1 postgrespostgres 221M Feb 4 20： 14 17310.1
rw-------1 postgrespostgres 336K Feb 4 20： 1317310 fsm
如果想要更改表和索引文件的大小，那么使用编译安装参数
如果数据数据文件超过1GB，那么就会新生成一个文件，如下：
cd $PGDATA
$ ls -la -h base/16384/19427*
-rw------- 1 postgres postgres 1.0G Apr 21 11:16 data/base/16384/19427
-rw------- 1 postgres postgres 45M Apr 21 11:20 data/base/16384/19427.1
注意：表和索引的文件大小的限制可以在编译的时候通过–with-segsize设置。
如果使用 delete删除了部分数据，那么 vacuum表是不会释放
copy数据到新表
drop旧表
rename新表名称为旧表名称
CTAS Where 1=0
CREATE TABLE tname（like旧表）
注意的一点，空间 vacuum回收了，但是物理文件依然存在，不会被删除
**.fsm .vm文件
fsm（free space mapping）
vm（visuable mapping）
空闲空间映射文件表文件每个页面上的空闲空间信息
可见性映射文件表文件每个页面上的可见性信
但是索引没有可见性映射文件，只有fsm
空闲空间映射文件是表和索引数据文件的第一个分支
可见性映射表时数据文件的第二个分支
4.4 表空间物理结构
在以下两种典型场景之下，我们可能会创建自定义表空间：
通过创建表空间解决已有表空间磁盘不足并无法逻辑扩展的问题；
将索引、WAL、数据库文件分配在性能不同的磁盘上，使硬件利用率何性能最大化。
用户自定义表空间会在pg_tbsspc目录创建ln软链接到指定表空间目录。表空间创建命令参考：
在PG中，除了base目录，自己新建的tablespace对应的目录都会再pg_tblspc下，如下图
4.4.1 默认表空间
默认表空间
pg_default默认表空间对应数据目录为 base， 数据库分别以oid方式在base目录下存放。
yanwei@[local]:5432=#\db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+------------------------------+-------------------+---------+--------+-------------
pg_default | postgres | | | | 32 MB |
pg_global | postgres | | | | 623 kB |
tbs_mydb | pguser | /data/pg12.5/pgdata/tbs_mydb | | | 17 MB |
(3 rows)
yanwei@[local]:5432=#
4.4.2 postgre对象标识符 OID 查询方法
yanwei@[local]:5432=#select oid, datname, datlastsysoid, dattablespace from pg_catalog.pg_database order by 1,2;
oid | datname | datlastsysoid | dattablespace
-------+-----------+---------------+---------------
1 | template1 | 14186 | 1663 < 数据库模板
14186 | template0 | 14186 | 1663 < 最精简数据库，不可修改
14187 | postgres | 14186 | 1663 < 默认数据库
16389 | mydb | 14186 | 16388 < 用户数据库
16409 | test | 14186 | 16388 < 用户数据库
16433 | yanwei | 14186 | 1663 < 用户数据库
(6 rows)
yanwei@[local]:5432=#
[postgres@pgserver12 base]$ tree -L 1 /data/pg12.5/pgdata/base/
/data/pg12.5/pgdata/base/
├── 1
├── 14186
├── 14187
└── 16433
4 directories, 0 files
[postgres@pgserver12 base]$ tree -L 1 /data/pg12.5/pgdata/pg_tblspc/16388/PG_12_201909212/
/data/pg12.5/pgdata/pg_tblspc/16388/PG_12_201909212/
├── 16389
└── 16409
2 directories, 0 files
4.2.3 用户表空间操作
在以下两种典型场景之下，我们可能会创建自定义表空间：
- 通过创建表空间解决已有表空间磁盘不足并无法逻辑扩展的问题；
- 将索引、WAL、数据库文件分配在性能不同的磁盘上，使硬件利用率何性能最大化。
PostgreSQL表空间的布局
表空间概念上逻辑，形式上物理
使用 CREATE TABLESPACE创建表空间
它的命名方式PG主版本号目录的版本号：PG_12_201909212
表空间可以存放在服务器上面 Postgres用户可以读写的任意目录
但是在 pg tbsp中会同时创建一个软连接，这个软连接的目录值与
表空间的OID值相同
[postgres@pgserver12 base]$ tree -L 1 /data/pg12.5/pgdata/pg_tblspc/16388/PG_12_201909212/
/data/pg12.5/pgdata/pg_tblspc/16388/PG_12_201909212/
├── 16389
└── 16409
[postgres@pgserver12 pg_tblspc]$ ll
total 0
lrwxrwxrwx. 1 postgres postgres 28 Aug 16 22:30 16388 -> /data/pg12.5/pgdata/tbs_mydb
[postgres@pgserver12 pg_tblspc]$
yanwei@[local]:5432=#select * from pg_tablespace ;
oid | spcname | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
16388 | tbs_mydb | 16387 | |
(3 rows)
yanwei@[local]:5432=#
1 创建目录和用户
[postgres@pgserver12 pgdata]$ pwd
/data/pg12.5/pgdata
[postgres@pgserver12 pgdata]$ mkdir yanwei_tbs
[postgres@pgserver12 pgdata]$
2 创建表空间
postgres@[local]:5432=#create tablespace yanwei_tbs owner yanwei
postgres-# location '/data/pg12.5/pgdata/yanwei_tbs';
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE
postgres@[local]:5432=#
3 创建database
postgres@[local]:5432=#create database yanwei1 with owner='yanwei' TEMPLATE=template1 TABLESPACE=yanwei_tbs; CREATE DATABASE postgres@[local]:5432=#\l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- mydb | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres test | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | yanwei | yanwei | UTF8 | en_US.UTF-8 | en_US.UTF-8 | yanwei1 | yanwei | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres@[local]:5432=#select oid,datname from pg_database ; oid | datname -------+----------- 14187 | postgres 16389 | mydb 1 | template1 14186 | template0 16409 | test 16433 | yanwei 16439 | yanwei1 (7 rows) [postgres@pgserver12 16439]$ pwd /data/pg12.5/pgdata/yanwei_tbs/PG_12_201909212/16439 4.5 数据文件
数据文件
在数据库创建的数据文件对象，如表、索引、视图、函数、序列等都会以文件存储。它们会在创建时被分配一个OID命名，对于大小超过1GB的表数据文件，PostgreSQL会自动将其切分为多个文件来存储，切分出的文件用OID.<顺序号>来命名。不过表文件并不是总是"OID.<顺序号>"命名，实际上真正管理表文件的时pg_class表中的relfilenode字段值，在新创建对象时会在pg_class系统表中插入该表的记录，默认会以OID作为relfilenode值，但经过几次VACUUM、TRUNCATE操作之后，relfilenode值会发生变化。
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
postgres=# select oid,relfilenode from pg_class where relname = 'test';
oid | relfilenode
-------+-------------
16384 | 16384
(1 row)
postgres=# truncate test;
TRUNCATE TABLE
postgres=# checkpoint;
CHECKPOINT
postgres=# select oid,relfilenode from pg_class where relname = 'test';
oid | relfilenode
-------+-------------
16384 | 16399
(1 row)
postgres=# INSERT INTO test(id,name) SELECT n,n || '_francs' from generate_series(1,50000000) n;
INSERT 0 50000000
postgres=# \dt+ test
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+---------+-------------
public | test | table | postgres | 2873 MB |
(1 row)
[postgres@postgresql01 ~]$ ll /pgdata/11.2/data/base/13287/16399*
-rw------- 1 postgres postgres 1073741824 Aug 24 11:11 /pgdata/11.2/data/base/13287/16399
-rw------- 1 postgres postgres 1073741824 Aug 24 11:13 /pgdata/11.2/data/base/13287/16399.1
-rw------- 1 postgres postgres 864206848 Aug 24 11:14 /pgdata/11.2/data/base/13287/16399.2
-rw------- 1 postgres postgres 761856 Aug 24 11:13 /pgdata/11.2/data/base/13287/16399_fsm
-rw------- 1 postgres postgres 8192 Aug 24 11:13 /pgdata/11.2/data/base/13287/16399_vm
上述结果中，后缀为_fsm和_vm的这两个表文件的附属文件是空闲空间映射表文件和可见性映射表文件。空闲空间映射用来映射表文件中可用的空间，可见性映射表文件跟踪哪些页面只包含已知对所有活动事务课件的元组，它也跟踪哪些页面只包含未被冻结的元组。
数据文件和文件结构
4.6 堆表文件的内部结构
在表对应的datafile中，被分离为固定大小的page(or block),默认为8KB，这些page在datafile中从0开始计数，如果一个page被填充满，那么就会生成新的page以添加到文件，所以我们看到的datafile会随着表的增大，也在不断增大。
^
postgres@[local]:5432=# create table heap_t(id int,name varchar(20));
CREATE TABLE
postgres@[local]:5432=# select pg_relation_size('heap_t')
postgres-# ;
pg_relation_size
------------------
0
postgres@[local]:5432=#insert into heap_t values(1);
INSERT 0 1
postgres@[local]:5432=# select pg_relation_size('heap_t')
postgres-# ;
pg_relation_size
------------------
8192
(1 row)
postgres@[local]:5432=#
一个堆表数据文件的内部结构设计如下图：
以块的方式进行存储，并且块的编号是从 0 开始的。
标准的缓冲页面的定义
/data/pg12.5/include/postgresql/server/storage/bufpage.h
* +----------------+---------------------------------+
* | PageHeaderData | linp1 linp2 linp3 ... |
* +-----------+----+---------------------------------+
* | ... linpN | |
* +-----------+--------------------------------------+
* | ^ pd_lower |
* | |
* | v pd_upper |
* +-------------+------------------------------------+
* | | tupleN ... |
* +-------------+------------------+-----------------+
* | ... tuple3 tuple2 tuple1 | "special space" |
* +--------------------------------+-----------------+
* ^ pd_special
一个页面的构成:
PageHeaderData linepn pd_lower pg_upper tuplen pd_special
* space management information generic to any page
*
* pd_lsn --标识该也买呢最后一次 xlog 的记录
* pd_checksum --如果设置，就是页面的校验和 initdb -k
* pd_flags --标记位
* pd_lower --空闲空间的起始位置(位置索引的偏移量)
* pd_upper --空闲空间的结束位置(位置索引的偏移量)
* pd_special --指定(特殊)空间的起始的偏移量.
* pd_pagesize_version --页层的版本编号和页面大小的版本版本编号
* pd_prune_xid --在该页面上可以裁剪的最老的XID
一个页面的定义
/data/pg12.5/include/postgresql/server/storage/block.h
块/页的编号是从 0 开始编号
元组，也叫tuple，这个叫法是很学术的叫法，但是现在数据库中一般叫行或者记录。下面是元组的结构：
typedef struct HeapTupleFields
{
TransactionId t_xmin; /* inserting xact ID */
TransactionId t_xmax; /* deleting or locking xact ID */
union
{
CommandId t_cid; /* inserting or deleting command ID, or both */
TransactionId t_xvac; /* old-style VACUUM FULL xact ID */
} t_field3;
} HeapTupleFields;
struct HeapTupleHeaderData
{
union
{
HeapTupleFields t_heap;
DatumTupleFields t_datum;
} t_choice;
ItemPointerData t_ctid; /* current TID of this or newer tuple (or a
* speculative insertion token) */
/* Fields below here must match MinimalTupleData! */
#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK2 2
uint16 t_infomask2; /* number of attributes + various flags */
#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK 3
uint16 t_infomask; /* various flag bits, see below */
#define FIELDNO_HEAPTUPLEHEADERDATA_HOFF 4
uint8 t_hoff; /* sizeof header incl. bitmap, padding */
/* ^ - 23 bytes - ^ */
#define FIELDNO_HEAPTUPLEHEADERDATA_BITS 5
bits8 t_bits[FLEXIBLE_ARRAY_MEMBER]; /* bitmap of NULLs */
/* MORE DATA FOLLOWS AT END OF STRUCT */
};
t_xmin：代表插入此元组的事务xid；
t_xmax：代表更新或者删除此元组的事务xid，如果该元组插入后未进行更新或者删除，t_xmax=0；
t_cid：command id，代表在当前事务中，已经执行过多少条sql，例如执行第一条sql时cid=0，执行第二条sql时cid=1；
t_ctid：保存着指向自身或者新元组的元组标识（tid），由两个数字组成，第一个数字代表物理块号，或者叫页面号，第二个数字代表元组号。在元组更新后tid指向新版本的元组，否则指向自己，这样其实就形成了新旧元组之间的“元组链”，这个链在元组查找和定位上起着重要作用。
一个表的page包括三种类型的数据
1.heap tuple: 存放数据本身，从一个page的末端有序的堆积。
2.line pointer: 一个四字节的行指针，指向每一个heap tuple,也叫item pointer,line pointer是一个简单的数组，索引page的数据文件是从1开始计数，也叫offset number,新的tuple增加到page时，line piniter就在推送到数组中，指向新的tuple.
3.header data: header data是page生成的时候随之产生的，由pageHeaderData定义结构，24个字节长，包含了page的一般信息，主要结构描述如下：
pd_lsn: 存储XLOG最后的改变的这个page的LSN号，是一个8字节的无符号整数，和WAL相关，后续章节会有描述
pd_checksum：存储page的校验和
pd_lower，pd_upper: pd_lower指向line pointer的尾部，pd_upper指向最新heap tuple的开头
pd_special: 此变量用于索引，在表的page中，它指向page的末尾(在索引的page中，它指向特殊空间的开头)
Page Header定义参考：
| 项 | 定义 |
|---|---|
| pd_lsn | 它确定和记录了最后更改此页的xlog记录的LSN，把数据页的WAL日志关联，用于恢复数据时校验日志文件和数据文件的一致性 |
| pg_flags | 标识页面的数据存储情况 |
| pd_special | 指向索引相关数据的开始位置，该项在数据文件中为空，主要是针对不同索引 |
| pd_lower | 指向空闲空间的起始位置 |
| pd_upper | 指向空闲空间的结束位置 |
| pd_pagesize_version | 不同的版本的页的格式可能会不同 |
| pd_linp | 行指针数组，它将Item地址指向Tuple的存储位置 |
| Special | 如果是索引页，那么根据索引类型的不同存储的数据也不同 |
4.6.1读写tuple的方法
写head tuple
假设我们我们的表只有一个page,这个page里只有一个tuple,如下图，pd_lower指向line pointer尾部，pd_upper指向tuple1的头部，当tuple2插入后，2号line pointer指向tuple2的头部，pd_lower指向了2号line pointer的末尾，pd_upper指向了tuple2的头部，其他的数据（pg_lsn,pg_flags等等）也会适当的被重写。后面章节会详解
读heap tuple
两种典型的访问方法，顺序扫描和B-tree索引扫描
a. sequential scan：表中的所有page中的所有tuple通过每个page中的所有line pointer依次读取
b. B-tree index can: 每个索引文件都包含index tuple,每个index tuple都是由索引键和一个指向目标heap tuple的point构成的TID所构成，如果索引的键值被找到，那么就从index tuple中获取TID的值去找想要的数据。如以下示例：通过索引的键值Queen,在index tuple中找到对应的TID(block=7,Offset=2)，这里的意思就是第七个page的第二个tuple.因此PG不需要在page中进行没有必要的扫描。
4.7 PG目录总结
SELECT
current_setting('config_file') AS "config_file",
current_setting('hba_file') AS "hba_file",
current_setting('ident_file') AS "ident_file";
SELECT
current_setting('data_directory') AS "data_directory",
current_setting('external_pid_file') AS "external_pid_file";
SELECT
current_setting('unix_socket_directories') AS "unix_socket_directories",
current_setting('unix_socket_permissions') AS "unix_socket_permissions",
current_setting('unix_socket_group') AS "unix_socket_group";
SELECT
-- not available on PostgreSQL < 10
pg_current_logfile(),
current_setting('log_directory') AS "log_directory", -- log
current_setting('log_filename') AS "log_filename"; -- postgresql-%Y-%m-%d_%H%M%S.log
SELECT
-- CASE WHEN pg_current_logfile() IS NOT NULL THEN pg_ls_logdir() END AS pg_ls_logdir,
pg_ls_waldir(),
-- not available on PostgreSQL <= 11.8
pg_ls_archive_statusdir(),
pg_ls_tmpdir();
~~~~~~~数据库数据目录~~~~~~~~~
postgres@[local]:5432=#
postgres@[local]:5432=#SELECT
postgres-# current_setting('data_directory') AS "data_directory",
postgres-# current_setting('external_pid_file') AS "external_pid_file";
data_directory | external_pid_file
---------------------+-------------------
/data/pg12.5/pgdata |
(1 row)
~~~~~~~数据库socket目录~~~~~~~~~
postgres@[local]:5432=#
postgres@[local]:5432=#SELECT
postgres-# current_setting('unix_socket_directories') AS "unix_socket_directories",
postgres-# current_setting('unix_socket_permissions') AS "unix_socket_permissions",
postgres-# current_setting('unix_socket_group') AS "unix_socket_group";
unix_socket_directories | unix_socket_permissions | unix_socket_group
-------------------------+-------------------------+-------------------
/tmp | 0777 |
(1 row)
~~~~~~~~~数据库log目录~~~~~~~~~~~
postgres@[local]:5432=#SELECT
postgres-# -- not available on PostgreSQL < 10
postgres-# pg_current_logfile(),
postgres-# current_setting('log_directory') AS "log_directory", -- log
postgres-# current_setting('log_filename') AS "log_filename"; -- postgresql-%Y-%m-%d_%H%M%S.log
pg_current_logfile | log_directory | log_filename
--------------------+---------------+--------------------------------
| log | postgresql-%Y-%m-%d_%H%M%S.log
~~~~~~~~~数据库wal目录~~~~~~~~~~~
postgres@[local]:5432=#
postgres@[local]:5432=#SELECT
postgres-# -- CASE WHEN pg_current_logfile() IS NOT NULL THEN pg_ls_logdir() END AS pg_ls_logdir,
postgres-# pg_ls_waldir(),
postgres-# -- not available on PostgreSQL <= 11.8
postgres-# pg_ls_archive_statusdir(),
postgres-# pg_ls_tmpdir();
pg_ls_waldir | pg_ls_archive_statusdir | pg_ls_tmpdir
--------------------------------------------------------------+-------------------------+--------------
(000000010000000000000001,16777216,"2021-08-17 22:41:46+08") | |
(1 row)
postgres@[local]:5432=#
5 进程结构
PostgreSQL采用的是经典的C/S架构模型。
PgSQL的多进程间的通信是通过share memory的方式进行的
5.1 运行流程分析
客户端建立连接流程
PostgreSQL是用一个简单的"每用户一进程" 的 client/server 模型实现的。在这种模式里一个客户端进程 只与一个服务器进程连接。因为事先并不知道具体要建立多少个连接， 所以不得不利用一个主进程在每次连接请求时派生出一个新的服务器进程。 这个主进程叫做postgres，它监听着一个特定的 TCP/IP 端口等待连接的到来。 每当检测到一个连接请求时，postgres进程派生出一个新的服务器进程。 服务器进程之间使用信号和共享内存进行通讯， 以确保并发数据访问过程中的数据完整性。
客户端建立和数据库之间的连接--->服务器进程---> SQL请求---> shared_buffer
-->找到数据--> 软解析
-->没有找到数据-->协同后台进程从数据文件获取-->加载到 shared_buffer中-->服务器进程将结果从 shared_buffer中返回给客户端--硬解析
当一个前端数据库应用程序需要访问数据库：
- 调用libpq，将连接请求发送给Postmaster；
- Postmaster启用一个新的后端服务器进程postgres与前端应用连接；
- 服务进程Postgres直接与用户通信，不再通过Postmaster；
- Postgres接收客户端的命令请求，完成并返回结果；
客户端每创建一个数据库连接，postmaster就生成一个pstogres进程，是一种1:1的进程结构（一个客户端对应一个服务进程）。
多进程的好处在于：系统的各个模块，各个服务进程之间耦合度更低，多用户直接并发运行不受影响。
带来的影响是：多进程比多线程的结构开销要大，进程的创建和回收比线程更加消耗资源，当用户发来的请求过多时，会造成负载过大，执行速度变慢。
本文主要讲述了PG的几个主要进程，以及PG的核心架构。进程和体系结构详见下图：
从上面的体系结构图可以看出来，PG使用经典的C/S架构，进程架构。在服务器端有主进程、服务进程、子进程、共享内存以及文件存储几大部分，下面着重讲述服务器端的进程部分：
5.2 Postmaster主进程和服务进程
首先从postmaster（守护进程）说起。
postmaster进程的主要职责有：
❑ 数据库的启停。
❑ 监听客户端连接。
❑ 为每个客户端连接fork单独的postgres服务进程。
❑ 当服务进程出错时进行修复。
❑ 管理数据文件。
❑ 管理与数据库运行相关的辅助进程。
Postgres server process
执行启动数据库的时候启动该父进程
pg ctl start -D SPGDATA-I/tmp/logfile
启动其他进程分配内存
通常， Postgres服务器进程仅有一个监听端口，默认就是5432（MySQL3306）（Oracle 1521）
如果在一台服务器上运行多个 PostgreSQL数据库那么需要配置不同的监听端口
（在一台服务器上安装多个 PostgreSQL实例，只能通过编译安装的方式）
PostgreSQL
个实例对应多个数据库
**后端进程（服务器进程）**客户端的连接请求分配的进程
postgres backend process
负责客户端发出的请求和接收处理后的数据返回给客户端（ TCP UDP）
当客户端断开连接，后端进程就会终」
postgres 10191 90830 2001？ 00： 00： 00 postgres： sdedu sdedudb 192168.43.27（50504）idle
一个连接只能操作一个数据库，所以需要显式指定数据库进行连接
PostgreSQL可以允许多个数据库进行连接（ max connections）
对于高并发Wweb服务连接，要避免连接分配服务器进程开销过大的问题，可以使用 些池化中间件进行连接数据库（ pgbouncer pgpool-II）
当PG数据库启动时，首先会启动Postmaster主进程。这个进程是PG数据库的总控制进程，负责启动和关闭数据库实例。实际上Postmaster进程是一个指向postgres命令的链接，如下：
[postgres@drz ~]$ ll /opt/postgresql/bin/postmaster
lrwxrwxrwx. 1 postgres dba 8 Aug 7 23:33 /opt/postgresql/bin/postmaster -> postgres
当用户和PG数据库建立连接时，要先与Postmaster进程建立连接，此时客户端进程会发送身份验证消息给Postmaster主进程，Postmaster主进程根据消息进行身份验证，验证通过后，Postmaster主进程会fork出一个会话服务进程为这个用户连接服务。可以通过pg_stat_activity表来查看服务进程的pid，如下：
test=# select pid,usename,client_addr,client_port from pg_stat_activity;
pid | usename | client_addr | client_port
-------±---------±------------±------------
26402 | postgres | | -1
(1 row)
eg
postgre=# select pid,usename,client_addr,client_port from pg_stat_activity;
pid | usename | client_addr | client_port
------+---------+-------------+-------------
2721 | postgre | |
2719 | | |
7718 | postgre | | -1
8905 | postgre | | -1
2717 | | |
2716 | | |
2718 | | |
(7 rows)
postgre=#
[postgre@yanwei ~]$ pstree -ap |grep post |grep -v grep
|-postgres,2713 -D /mydata/pgdata
| |-postgres,2714
| |-postgres,2716
| |-postgres,2717
| |-postgres,2718
| |-postgres,2719
| |-postgres,2720
| |-postgres,2721
| `-postgres,7718
| | `-su,7679 - postgre
| `-su,8847 - postgre
[postgre@yanwei ~]$
[postgre@yanwei ~]$ ps -ef |grep postgres |grep -v grep
postgre 2713 1 0 02:27 ? 00:00:00 /usr/local/pgsql10.2/bin/postgres -D /mydata/pgdata
postgre 2714 2713 0 02:27 ? 00:00:00 postgres: logger process
postgre 2716 2713 0 02:27 ? 00:00:00 postgres: checkpointer process
postgre 2717 2713 0 02:27 ? 00:00:00 postgres: writer process
postgre 2718 2713 0 02:27 ? 00:00:00 postgres: wal writer process
postgre 2719 2713 0 02:27 ? 00:00:00 postgres: autovacuum launcher process
postgre 2720 2713 0 02:27 ? 00:00:00 postgres: stats collector process
postgre 2721 2713 0 02:27 ? 00:00:00 postgres: bgworker: logical replication launcher
postgre 7718 2713 0 09:56 ? 00:00:00 postgres: postgre postgre [local] idle
[postgre@yanwei ~]$
5.3 BgWriter（后台写）进程
BgWriter进程是把共享内存中的脏页写到磁盘上的进程。它的作用有两个：一是定期把脏数据从内存缓冲区刷出到磁盘中，减少查询时的阻塞；二是PG在定期作检查点时需要把所有脏页写出到磁盘，通过BgWriter预先写出一些脏页，可以减少设置检查点（CheckPoint，数据库恢复技术的一种）时要进行的IO操作，使系统的IO负载趋向平稳。BgWriter是PostgreSQL 8.0以后新加的特性，它的机制可以通过postgresql.conf文件中以"bgwriter_"开头配置参数来控制：
-Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds #bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
#bgwriter_flush_after = 512kB # measured in pages, 0 disables
bgwriter_delay:
backgroud writer进程连续两次flush数据之间的时间的间隔。默认值是200，单位是毫秒。
bgwriter_lru_maxpages：
backgroud writer进程每次写的最多数据量，默认值是100，单位buffers。如果脏数据量小于该数值时，写操作全部由backgroud writer进程完成；反之，大于该值时，大于的部分将有server process进程完成。设置该值为0时表示禁用backgroud writer写进程，完全有server process来完成；配置为-1时表示所有脏数据都由backgroud writer来完成。(这里不包括checkpoint操作)
bgwriter_lru_multiplier：
这个参数表示每次往磁盘写数据块的数量，当然该值必须小于bgwriter_lru_maxpages。设置太小时需要写入的脏数据量大于每次写入的数据量，这样剩余需要写入磁盘的工作需要server process进程来完成，将会降低性能；值配置太大说明写入的脏数据量多于当时所需buffer的数量，方便了后面再次申请buffer工作，同时可能出现IO的浪费。该参数的默认值是2.0。
bgwriter的最大数据量计算方式：
1000/bgwriter_delaybgwriter_lru_maxpages8K=最大数据量
bgwriter_flush_after：
数据页大小达到bgwriter_flush_after时触发BgWriter，默认是512KB。
5.4 PgArch（归档）进程
类似于Oracle数据库的ARCH归档进程，不同的是ARCH是吧redo log进行归档，PgArch是把WAL日志进行归档。再深入点，WAL日志会被循环使用，也就是说，过去的WAL日志会被新产生的日志覆盖，PgArch进程就是为了在覆盖前把WAL日志备份出来。归档日志的作用是为了数据库能够使用全量备份和备份后产生的归档日志，从而让数据库回到过去的任一时间点。PG从8.X版本开始提供的PITR（Point-In-Time-Recovery）技术，就是运用的归档日志。
PgArch进程通过postgresql.conf文件中的如下参数进行配置：
- Archiving -
#archive_mode = off # enables archiving; off, on, or always
# (change requires restart)
#archive_command = ‘’ # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. ‘test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f’
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
archive_mode：
表示是否进行归档操作，可选择为off（关闭）、on（启动）和always（总是开启），默认值为off（关闭）。
archive_command：
由管理员设置的用于归档WAL日志的命令。在用于归档的命令中，预定义变量“%p”用来指代需要归档的WAL全路径文件名，“%f”表示不带路径的文件名（这里的路径都是相对于当前工作目录的路径）。每个WAL段文件归档时将调用archive_command所指定的命令。当归档命令返回0时，PostgreSQL就会认为文件被成功归档，然后就会删除或循环使用该WAL段文件。否则，如果返回一个非零值，PostgreSQL会认为文件没有被成功归档，便会周期性地重试直到成功。
archive_timeout：
表示归档周期，在超过该参数设定的时间时强制切换WAL段，默认值为0（表示禁用该功能）。
5.5 PgStat（统计数据收集）进程
PgStat进程是PostgreSQL数据库的统计信息收集器，用来收集数据库运行期间的统计信息，如表的增删改次数，数据块的个数，索引的变化等等。收集统计信息主要是为了让优化器做出正确的判断，选择最佳的执行计划。postgresql.conf文件中与PgStat进程相关的参数，如下：
#------------------------------------------------------------------------------
RUNTIME STATISTICS
#------------------------------------------------------------------------------
- Query/Index Statistics Collector -
#track_activities = on
#track_counts = on
#track_io_timing = off
#track_functions = none # none, pl, all
#track_activity_query_size = 1024 # (change requires restart)
#stats_temp_directory = ‘pg_stat_tmp’
track_activities：表示是否对会话中当前执行的命令开启统计信息收集功能，该参数只对超级用户和会话所有者可见，默认值为on（开启）。
track_counts：表示是否对数据库活动开启统计信息收集功能，由于在AutoVacuum自动清理进程中选择清理的数据库时，需要数据库的统计信息，因此该参数默认值为on。
track_io_timing：定时调用数据块I/O，默认是off，因为设置为开启状态会反复的调用数据库时间，这给数据库增加了很多开销。只有超级用户可以设置
track_functions：表示是否开启函数的调用次数和调用耗时统计。
track_activity_query_size：设置用于跟踪每一个活动会话的当前执行命令的字节数，默认值为1024，只能在数据库启动后设置。
stats_temp_directory：统计信息的临时存储路径。路径可以是相对路径或者绝对路径，参数默认为pg_stat_tmp，设置此参数可以减少数据库的物理I/O，提高性能。此参数只能在postgresql.conf文件或者服务器命令行中修改。
5.6 AutoVacuum（自动清理）进程
在PG数据库中，对数据进行UPDATE或者DELETE操作后，数据库不会立即删除旧版本的数据，而是标记为删除状态。这是因为PG数据库具有多版本的机制，如果这些旧版本的数据正在被另外的事务打开，那么暂时保留他们是很有必要的。当事务提交后，旧版本的数据已经没有价值了，数据库需要清理垃圾数据腾出空间，而清理工作就是AutoVacuum进程进行的。postgresql.conf文件中与AutoVacuum进程相关的参数有：
#------------------------------------------------------------------------------
AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
#autovacuum = on # Enable autovacuum subprocess? ‘on’
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
# before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
autovacuum：是否启动系统自动清理功能，默认值为on。
log_autovacuum_min_duration：这个参数用来记录 autovacuum 的执行时间，当 autovaccum 的执行时间超过 log_autovacuum_min_duration参数设置时，则autovacuum信息记录到日志里，默认为 “-1”, 表示不记录。
autovacuum_max_workers：设置系统自动清理工作进程的最大数量。
autovacuum_naptime：设置两次系统自动清理操作之间的间隔时间。
autovacuum_vacuum_threshold和autovacuum_analyze_threshold：设置当表上被更新的元组数的阈值超过这些阈值时分别需要执行vacuum和analyze。
autovacuum_vacuum_scale_factor和autovacuum_analyze_scale_factor：设置表大小的缩放系数。
autovacuum_freeze_max_age：设置需要强制对数据库进行清理的XID上限值。
autovacuum_vacuum_cost_delay：当autovacuum进程即将执行时，对 vacuum 执行 cost 进行评估，如果超过 autovacuum_vacuum_cost_limit设置值时，则延迟，这个延迟的时间即为 autovacuum_vacuum_cost_delay。如果值为 -1, 表示使用 vacuum_cost_delay 值，默认值为 20 ms。
autovacuum_vacuum_cost_limit：这个值为 autovacuum 进程的评估阀值, 默认为 -1, 表示使用 "vacuum_cost_limit " 值，如果在执行 autovacuum 进程期间评估的cost 超过 autovacuum_vacuum_cost_limit, 则 autovacuum 进程则会休眠。
5.7 WalWriter（预写式日志写）进程
预写式日志WAL（Write Ahead Log，也称为Xlog）的中心思想是对数据文件的修改必须是只能发生在这些修改已经记录到日志之后，也就是先写日志后写数据（日志先行）。使用这种机制可以避免数据频繁的写入磁盘，可以减少磁盘I/O。数据库在宕机重启后可以运用这些WAL日志来恢复数据库。postgresql.conf文件中与WalWriter进程相关的参数如下：
#------------------------------------------------------------------------------
WRITE AHEAD LOG
#------------------------------------------------------------------------------
- Settings -
#wal_level = minimal # minimal, replica, or logical
# (change requires restart)
#fsync = on # flush data to disk for crash safety
# (turning this off can cause
# unrecoverable data corruption)
#synchronous_commit = on # synchronization level;
# off, local, remote_write, remote_apply, or on
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_compression = off # enable compression of full-page writes
#wal_log_hints = off # also do full page writes of non-critical updates
# (change requires restart)
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#wal_writer_flush_after = 1MB # measured in pages, 0 disables
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
wal_level：控制wal存储的级别。wal_level决定有多少信息被写入到WAL中。 默认值是最小的（minimal），其中只写入从崩溃或立即关机中恢复的所需信息。replica 增加 wal 归档信息 同时包括 只读服务器需要的信息。（9.6 中新增，将之前版本的 archive 和 hot_standby 合并）
logical 主要用于logical decoding 场景
fsync：该参数直接控制日志是否先写入磁盘。默认值是ON（先写入），表示更新数据写入磁盘时系统必须等待WAL的写入完成。可以配置该参数为OFF，表示更新数据写入磁盘完全不用等待WAL的写入完成。
synchronous_commit：参数配置是否等待WAL完成后才返回给用户事务的状态信息。默认值是ON，表明必须等待WAL完成后才返回事务状态信息；配置成OFF能够更快地反馈回事务状态。
wal_sync_method：WAL写入磁盘的控制方式，默认值是fsync，可选用值包括open_datasync、fdatasync、fsync_writethrough、fsync、open_sync。open_datasync和open_sync分别表示在打开WAL文件时使用O_DSYNC和O_SYNC标志；fdatasync和fsync分别表示在每次提交时调用fdatasync和fsync函数进行数据写入，两个函数都是把操作系统的磁盘缓存写回磁盘，但前者只写入文件的数据部分，而后者还会同步更新文件的属性；fsync_writethrough表示在每次提交并写回磁盘会保证操作系统磁盘缓存和内存中的内容一致。
full_page_writes：表明是否将整个page写入WAL。
wal_buffers：用于存放WAL数据的内存空间大小，系统默认值是64K，该参数还受wal_writer_delay、commit_delay两个参数的影响。
wal_writer_delay：WalWriter进程的写间隔时间，默认值是200毫秒，如果时间过长可能造成WAL缓冲区的内存不足；时间过短将会引起WAL的不断写入，增加磁盘I/O负担。
wal_writer_flush_after：
commit_delay：表示一个已经提交的数据在WAL缓冲区中存放的时间，默认值是0毫秒，表示不用延迟；设置为非0值时事务执行commit后不会立即写入WAL中，而仍存放在WAL缓冲区中，等待WalWriter进程周期性地写入磁盘。
commit_siblings：表示当一个事务发出提交请求时，如果数据库中正在执行的事务数量大于commit_siblings值，则该事务将等待一段时间（commit_delay的值）；否则该事务则直接写入WAL。系统默认值是5，该参数还决定了commit_delay的有效性。
wal_writer_flush_after：当脏数据超过阈值时，会被刷出到磁盘。
5.8 CheckPoint（检查点）进程
检查点是系统设置的事务序列点，设置检查点保证检查点前的日志信息刷到磁盘中。postgresql.conf文件中与之相关的参数有：
- Checkpoints -
#checkpoint_timeout = 5min # range 30s-1d
#max_wal_size = 1GB
#min_wal_size = 80MB
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB # measured in pages, 0 disables
#checkpoint_warning = 30s # 0 disables
5.9 SysLogger进程
日志信息是数据库管理员获取数据库系统运行状态的有效手段。在数据库出现故障时，日志信息是非常有用的。把数据库日志信息集中输出到一个位置将极大方便管理员维护数据库系统。然而，日志输出将产生大量数据（特别是在比较高的调试级别上），单文件保存时不利于日志文件的操作。因此，在SysLogger的配置选项中可以设置日志文件的大小，SysLogger会在日志文件达到指定的大小时关闭当前日志文件，产生新的日志文件。在postgresql.conf里可以配置日志操作的相关参数：
log_destination：配置日志输出目标，根据不同的运行平台会设置不同的值，Linux下默认为stderr。
logging_collector：是否开启日志收集器，当设置为on时启动日志功能；否则，系统将不产生系统日志辅助进程。
log_directory：配置日志输出文件夹。
log_filename：配置日志文件名称命名规则。
log_rotation_size：配置日志文件大小，当前日志文件达到这个大小时会被关闭，然后创建一个新的文件来作为当前日志文件。
此外，postgresql.conf中还提供了其他配置参数，可以根据需要进行设置。
5.10 postmaster.pid
[postgres@pgserver12 log]$ ps -ef|grep postgre
root 90658 90433 0 20:35 pts/0 00:00:00 su - postgres
postgres 90660 90658 0 20:35 pts/0 00:00:00 -bash
root 94473 94421 0 21:47 pts/1 00:00:00 su - postgres
postgres 94474 94473 0 21:47 pts/1 00:00:00 -bash
root 94704 94601 0 21:51 pts/2 00:00:00 su - postgres
postgres 94705 94704 0 21:51 pts/2 00:00:00 -bash
postgres 97961 1 0 22:51 ? 00:00:00 /data/pg12.5/bin/postgres -D /data/pg12.5/pgdata
postgres 97962 97961 0 22:51 ? 00:00:00 postgres: logger
postgres 97964 97961 0 22:51 ? 00:00:00 postgres: checkpointer
postgres 97965 97961 0 22:51 ? 00:00:00 postgres: background writer
postgres 97966 97961 0 22:51 ? 00:00:00 postgres: walwriter
postgres 97967 97961 0 22:51 ? 00:00:00 postgres: autovacuum launcher
postgres 97968 97961 0 22:51 ? 00:00:00 postgres: stats collector
postgres 97969 97961 0 22:51 ? 00:00:00 postgres: logical replication launcher
postgres 98005 94474 0 22:52 pts/1 00:00:00 psql
postgres 98006 97961 0 22:52 ? 00:00:00 postgres: postgres postgres [local] idle
postgres 98224 94705 0 22:56 pts/2 00:00:00 ps -ef
postgres 98225 94705 0 22:56 pts/2 00:00:00 grep --color=auto postgre
[postgres@pgserver12 pgdata]$ cat postmaster.pid
97961
/data/pg12.5/pgdata
1629211908
5432
/tmp
*
5432001 1048576
ready
| 97961 | 代表Postgres主进程的PID |
| /data/pg12.5/pgdata | 代表数据目录 |
| 1629211908 | 代表postmaster文件的创建时间 |
| 5432 | 代表数据库监听端口，在postgresql.conf中对应port = 5432 |
| /tmp | 代表是unix socket的监听目录，在postgresql.conf中对应 unix_socket_directory = ‘/tmp’ |
| *** **: | 代表数据库监听地址，对应postgresql.conf的listen_addresses = '* ’ |
| 5432001 1048576 | 代表的是共享内存的地址(shared memory segments中的key和shmid)。 |
| ready | 代表主进程状态 |
[root@yanwei pgdata]# ipcs
[postgres@pgserver12 pgdata]$ ipcs
------ Message Queues --------
key msqid owner perms used-bytes messages
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x0052e2c1 1048576 postgres 600 56 7
------ Semaphore Arrays --------
key semid owner perms nsems
5.11 客户端进程
客户端进程
psql pgadmin jdbc libpq 等等
存在与客户端所在的主机上
6 网络架构
网络架构主要由以下内容组成：相关内容会在后续的安全和访问等文章介绍
网络监听
主机访问控制
对等认证控制
TCP/IP
SS