PGSQL 架构体系
2021-11-03 18:58:25 3 举报AI智能生成
PG数据库架构体系整理，包括物理结构，逻辑结构
作者其他创作
大纲/内容
简述
定义
PostgreSQL数据库是由一系列位于文件系统上的物理文件组成，通常将这些物理文件称为数据库
将这些物理文件、管理这些物理文件的进程、进程管理的内存称为这个数据库的实例
在内部功能实现上的几部分
系统控制器
负责接收外部连接请求
查询分析器
对连接请求查询进行分析并生成优化后的查询解析树
事务系统
对数据做处理
恢复系统
保证数据安全性
文件系统
获取结果集和持久化
1、逻辑结构
PG对比ORACLE的逻辑结构
schema
在PostgreSQL中Schema可以理解为一个命名空间或目录
每个schema有各自的表，视图等对象，不同的schema下名称可以相同。
ORACLE的Schema功能上和PostgreSQL相同，但是ORACLE一个用户即一个Schema。
数据库与实例
一个数据库实例对应多个数据库
PostgreSQL MySQL SQLServer
pg多库之间不能直接访问，需借助其他的方式
1、通过schema，将数据和存储过程分别放到不同的schema上，经过权限管理后进行访问
2、通过dblink
3、通过postgres_fdw实现跨库访问
一个数据库实例对应一个数据库
Oracle
多个数据库实例对应—个数据库
ORACLE RAC
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构成
表空间
存储数据库的一个逻辑空间，可以存放不同的数据库 --对应在物理层面上是一个目录
OID
所有数据库对象都有各自的oid(object identifiers)
总结
从大小排列 tablespace--database cluster--databases--schema--objects
Tablespace是数据最大的存储空间， Database是构成表空间的存储单元，pages是 PostgreSQL数据库中最小的IO单元
逻辑结构管理
数据库
创建数据库
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;
create database test owner=pguser template=template1 encoding=utf8 tablespace=tbs_mydb CONNECTION LIMIT=1000;
调整数据库
ALTER DATABASE name RENAME TO new_nameALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }ALTER DATABASE name SET TABLESPACE new_tablespaceALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }ALTER DATABASE name SET configuration_parameter FROM CURRENTALTER DATABASE name RESET configuration_parameterALTER 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;
删除数据库
drop database [if exists] name;
注意：如果有人连接这个数据库，则不能删除；不能再事物块中删除数据库；可以修改。
查看数据库
查看database属性
select * from pg_database;
查看database大小
查看database路径
SHOW data_directory;
创建
create schema osdba;
删除
drop schema osdba;
查看
\\dn
为pguser创建schema
create schema authorization pguser;
修改
alter schema osdba rename to osdbaold；alter schema osdbaold owner to xxx;
搜索路径
show search_path;
如果不同的schema下有相同的表名出现，那么数据库将会自动根据search_path的顺序展示出第一个schema下的对象，其余的不显示
设置搜索路径时不需要添加引号，否则会导致无法访问数据表
set search_path = \"$user\
\\db
创建表空间
create tablespace \"my_tablespace\" location '/usr/local/pgdata';
初始化数据库目录时会自动创建pg_default和pg_global两个表空间
pg_global表空间的物理文件位置在数据目录的global目录中，它用来保存系统表
pg_default表空间的物理文件位置在数据目录中的base目录，是template0和template1数据库的默认表空间
对象
查看所有表大小
3、物理结构
数据库的文件默认保存在initdb时创建的数据目录中
在数据目录中有很多类型、功能不同的目录和文件，除了数据文件之外，还有参数文件、控制文件、数据库运行日志及预写日志等
软件安装目录
PostgreSQL数据库的软件目录通常是在/usr 目录下（也可自定义位置）
使用pg_confg命令可以看到当前数据库的基本情况
目录说明
bin：二进制可执行文件，是PG数据库的所有相关命令所在，为方便使用需设置到环境变量中
include：头文件目录
lib ：动态库目录，PostgreSQL运行所需要的动态库都在此目录下
share：放有文档和配置模板文件，一些拓展插件的SQL文件在此目录下的extension中
数据库目录结构
结构图
文件说明
PG_VERSION\t包含postgresql主版本号的文件
pg_hba.conf\t控制postgresql客户端验证的文件 控制PG客户端认证文件 主机 数据库 用户 ip地址 认证方式
postgresql.conf\t配置参数文件
postgresql.auto.conf\t用于存储在ALTER SYSTEM（版本9.4或更高版本）中设置的配置参数的文件
postmaster.opts\t记录服务端上一次启动的命令行选项
subdirectories\tdescription
base/\t包含每个数据库子目录的子目录
global/\t包含群集范围表的子目录，例如pg_database和pg_control
pg_commit_ts/\t包含事务提交时间戳数据的子目录。 9.5版本以后
pg_clog/ (Version 9.6 or earlier)\t包含事务提交状态数据的子目录。它在版本10中重命名为pg_xact. CLOG将在5.4章节中详解。.
pg_dynshmem/\t包含动态共享内存子系统使用的文件的子目录。9.4版本以后
pg_logical/\t包含逻辑解码的状态数据的子目录。9.4版本以后
pg_multixact/\t包含多事务状态数据的子目录（用于 shared row locks）
pg_notify/\t包含LISTEN / NOTIFY状态数据的子目录
pg_repslot/\t包含复制槽数据的子目录（9.1版本以后）
pg_serial/\t包含有关已提交的序列化事务（9.1版本以后）信息的子目录
pg_snapshots/\t包含导出快照的子目录（9.2版本以后）。 PostgreSQL的函数pg_export_snapshot在此子目录中创建快照信息文件
pg_stat/\t包含统计子系统永久文件的子目录
pg_stat_tmp/\t包含统计子系统临时文件的子目录
pg_subtrans/\t包含子事物状态数据的子目录
pg_tblspc/\t表空间符号链接目录
pg_twophase/\t包含prepare事务的状态文件
pg_wal/ (Version 10 or later)\t包含WAL（Write Ahead Logging）段文件的子目录。在版本10中从pg_xlog重命名而来.
pg_xact/ (Version 10 or later)\t包含事务提交状态数据的子目录。在版本10中从pg_clog重命名而来.CLOG将在5.4章节中详解
pg_xlog/ (Version 9.6 or earlier)\t包含WAL（Write Ahead Logging）段文件的子目录。在版本10中重命名为pg_
base的物理布局设计
表空间与数据库关系
在Oracle数据库中；一个表空间只属于一个数据库使用；而一个数据库可以拥有多个表空间。属于\"一对多\"的关系
在PostgreSQL集群中；一个表空间可以让多个数据库使用；而一个数据库可以使用多个表空间。属于\"多对多\"的关系。
系统自带表空间
表空间pg_default是用来存储系统目录对象、用户表、用户表index、和临时表、临时表index、内部临时表的默认空间。对应存储目录$PADATA/base/
表空间pg_global用来存放系统字典表；对应存储目录$PADATA/global/
示意图
表和索引的物理布局设计
说明
每一个表和索引如果不超过1G大小，都只有一个文件。
如果表和索引超过1GB， PostgreSQL对该表或者索引会进行拆分
表和索引的文件大小的限制可以在编译的时候通过–with-segsize设置
如果使用 delete删除了部分数据，那么 vacuum表是不会释放
示例
以下操作空间 vacuum回收了，但是物理文件依然存在，不会被删除
copy数据到新表 drop旧表 rename新表名称为旧表名称 CTAS Where 1=0 CREATE TABLE tname（like旧表）
.fsm .vm文件
fsm（free space mapping）
空闲空间映射文件表文件每个页面上的空闲空间信息
vm（visuable mapping）
表空间物理结构
使用表空间的场景
通过创建表空间解决已有表空间磁盘不足并无法逻辑扩展的问题；
将索引、WAL、数据库文件分配在性能不同的磁盘上，使硬件利用率何性能最大化。
用户自定义表空间会在pg_tbsspc目录创建ln软链接到指定表空间目录
在PG中，除了base目录，自己新建的tablespace对应的目录都会再pg_tblspc下
创建表空间时需要指定已存在的路径
PostgreSQL表空间的布局
命名方式PG主版本号目录的版本号：PG_12_201909212
可以存放在服务器上面 Postgres用户可以读写的任意目录
数据文件
在数据库创建的数据文件对象，如表、索引、视图、函数、序列等都会以文件存储
对于大小超过1GB的表数据文件，PostgreSQL会自动将其切分为多个文件来存储，切分出的文件用OID.来命名
后缀为_fsm和_vm的这两个表文件的附属文件是空闲空间映射表文件和可见性映射表文件
空闲空间映射用来映射表文件中可用的空间
可见性映射表文件跟踪哪些页面只包含已知对所有活动事务课件的元组，它也跟踪哪些页面只包含未被冻结的元组
堆表文件的内部结构
PG目录总结
查看相关数据库信息
数据库数据目录
SELECT current_setting('data_directory') AS \"data_directory\
数据库socket目录
SELECT current_setting('unix_socket_directories') AS \"unix_socket_directories\
数据库log目录
数据库wal目录
2、内存结构
oracle的内存结构
PG内存结构
内存大概被分为两块
Local memory area
为每一个backend process 分配的内存
work_mem
maintenance_work_mem
vacuum，reindex会用到这块区域
temp_buffers
存储临时表会用到这块区域
Shared memory area
PostgreSQL server 所有的backgroud process使用的内存
在服务器启动的时候分配，这块区域也是分为好几个子区域
shared buffer pool
将表或者索引的page从磁盘加载到shared buffer，然后在shared buffer操作
WAL buffer
在服务端出现问题的时候，确保数据不会丢失，在写到磁盘之前，wal buffer是wal log的缓存区域
commit log
为了并发控制所有事物的状态的保持而分配的区域
4、实例架构
实例是由数据库内存，后台进程和数据库集簇组成的。
5、进程结构
运行流程分析
客户端建立连接流程
客户端建立和数据库之间的连接---服务器进程--- SQL请求--- shared_buffer--找到数据-- 软解析--没有找到数据--协同后台进程从数据文件获取--加载到 shared_buffer中--服务器进程将结果从 shared_buffer中返回给客户端--硬解析
服务器进程之间使用信号和共享内存进行通讯， 以确保并发数据访问过程中的数据完整性
客户端每创建一个数据库连接，postmaster就生成一个pstogres进程，是一种1:1的进程结构（一个客户端对应一个服务进程
一个连接只能操作一个数据库，所以需要显式指定数据库进行连接
多进程的好处在于
系统的各个模块，各个服务进程之间耦合度更低，多用户直接并发运行不受影响
带来的影响
多进程比多线程的结构开销要大，进程的创建和回收比线程更加消耗资源，当用户发来的请求过多时，会造成负载过大，执行速度变慢
当一个前端数据库应用程序需要访问数据库
调用libpq，将连接请求发送给Postmaster；
Postmaster启用一个新的后端服务器进程postgres与前端应用连接；
服务进程Postgres直接与用户通信，不再通过Postmaster；
Postgres接收客户端的命令请求，完成并返回结果；
Postmaster主进程和服务进程
postmaster进程的主要职责
❑ 数据库的启停。
❑ 监听客户端连接。
❑ 为每个客户端连接fork单独的postgres服务进程。
❑ 当服务进程出错时进行修复。
❑ 管理数据文件。
身份验证
查看服务进程信息
BgWriter（后台写）进程
作用
一是定期把脏数据从内存缓冲区刷出到磁盘中，减少查询时的阻塞
二是PG在定期作检查点时需要把所有脏页写出到磁盘，通过BgWriter预先写出一些脏页，可以减少设置检查点时要进行的IO操作，使系统的IO负载趋向平稳
把共享内存中的脏页写到磁盘上的进程
BgWriter是PostgreSQL 8.0以后新加的特性，它的机制可以通过postgresql.conf文件中以\"bgwriter_\"开头配置参数来控制
相关参数
Bgwriter_delay
表示在一次刷脏页面后的睡眠时间。注意bgwriter的休眠时间不是一定是这个值，当缓冲池中没有脏缓冲区时，不管bgwriter_delay是多少，它都会进入更长时间的休眠。
Bgwriter_lru_maxpages
每个写入器在一次bgwriter工作时所允许写出的最大页面数量。
Bgwriter_lru_multiplier
在每轮中写出的脏缓冲区的数量基于最近几轮中服务器进程需要的新缓冲区的数量
bgwriter_flush_after
只要后台写入的数据超过这个数量，尝试强制 OS 把这些写发送到底层存储上
PgArch（归档）进程
类似于Oracle数据库的ARCH归档进程，不同的是ARCH是吧redo log进行归档，PgArch是把WAL日志进行归档
WAL日志会被循环使用，也就是说，过去的WAL日志会被新产生的日志覆盖
PgArch进程就是为了在覆盖前把WAL日志备份出来
归档日志的作用是为了数据库能够使用全量备份和备份后产生的归档日志，从而让数据库回到过去的任一时间点
archive_mode
表示是否进行归档操作，可选择为off（关闭）、on（启动）和always（总是开启），默认值为off（关闭）。
archive_command
由管理员设置的用于归档WAL日志的命令
在用于归档的命令中，预定义变量“%p”用来指代需要归档的WAL全路径文件名，“%f”表示不带路径的文件名
archive_timeout
表示归档周期，在超过该参数设定的时间时强制切换WAL段，默认值为0（表示禁用该功能）。
PgStat（统计数据收集）进程
PostgreSQL数据库的统计信息收集器，用来收集数据库运行期间的统计信息，如表的增删改次数，数据块的个数，索引的变化等等
收集统计信息主要是为了让优化器做出正确的判断，选择最佳的执行计划
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，提高性能
AutoVacuum（自动清理）进程
在PG数据库中，对数据进行UPDATE或者DELETE操作后，数据库不会立即删除旧版本的数据，而是标记为删除状态
当事务提交后，旧版本的数据已经没有价值了，数据库需要清理垃圾数据腾出空间，而清理工作就是AutoVacuum进程进行的
autovacuum
是否启动系统自动清理功能，默认值为on
log_autovacuum_min_duration
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_limit
WalWriter（预写式日志写）进程
预写式日志WAL（Write Ahead Log，也称为Xlog）的中心思想是对数据文件的修改必须是只能发生在这些修改已经记录到日志之后，也就是先写日志后写数据（日志先行）
使用这种机制可以避免数据频繁的写入磁盘，可以减少磁盘I/O
数据库在宕机重启后可以运用这些WAL日志来恢复数据库
wal_level
控制wal存储的级别。wal_level决定有多少信息被写入到WAL中
默认值是最小的（minimal），其中只写入从崩溃或立即关机中恢复的所需信息
replica 增加 wal 归档信息 同时包括 只读服务器需要的信息。
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的有效性。
当脏数据超过阈值时，会被刷出到磁盘。
CheckPoint（检查点）进程
检查点是系统设置的事务序列点，设置检查点保证检查点前的日志信息刷到磁盘中
SysLogger进程
数据库系统运行状态日志
SysLogger会在日志文件达到指定的大小时关闭当前日志文件，产生新的日志文件
log_destination
配置日志输出目标，根据不同的运行平台会设置不同的值，Linux下默认为stderr
logging_collector
是否开启日志收集器，当设置为on时启动日志功能；否则，系统将不产生系统日志辅助进程。
log_directory
配置日志输出文件夹
log_filename
配置日志文件名称命名规则
log_rotation_size
配置日志文件大小，当前日志文件达到这个大小时会被关闭，然后创建一个新的文件来作为当前日志文件。
postmaster.pid
97961\t代表Postgres主进程的PID
/data/pg12.5/pgdata\t代表数据目录
1629211908\t代表postmaster文件的创建时间
5432\t代表数据库监听端口，在postgresql.conf中对应port = 5432
/tmp\t代表是unix socket的监听目录，在postgresql.conf中对应 unix_socket_directory = ‘/tmp’
*** **:\t代表数据库监听地址，对应postgresql.conf的listen_addresses = '* ’
5432001 1048576\t代表的是共享内存的地址(shared memory segments中的key和shmid)。
ready\t代表主进程状态
客户端进程
psql pgadmin jdbc libpq 等等
PGSQL 架构体系
职业：暂无
0 条评论
下一页
