开发者学堂课程【 PostgreSQL 快速入门 ： 2 PostgreSQL 物理，逻辑，进程结构以及系统表系统函数 】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址 ： https://developer.aliyun.com/learning/course/16/detail/88

2 PostgreSQL 物理，逻辑，进程结构以及系统表系统函数

内容介绍：

一、 PostgreSQL 体系结构

二、PostgreSQL 数据库逻辑概貌

三、PostgreSQL 数据库进程结构概貌

四 、PostgreSQL 系统表介绍

五 、PostgreSQL 系统视图介绍

六 、PostgreSQL 管理函数

七 、PostgreSQL 进程结构

八、PostgreSQL 物理结构

九、PostgreSQL 数据库可靠性

十、练习

一、PostgreSQL 体系结构

1、了解系统表以及系统表之间的关系，系统视图，管理函数等。

2、了解 PG 进程结构。

3、了解 PG 物理结构，数据库逻辑概貌，物理概貌，可靠性等。

安装好 PostgreSQL，数据库初始化，可以连接到 PostgreSQL 的数据库里面。

二、PostgreSQL 数据库逻辑概貌

initdb 执行完后会初始化数据库，数据库在最顶端的是 cluster，cluster 下可以创建很多数据库，数据库和数据库之间是分开的关系，从a库连到c库需要 PostgreSQL 其它的库文件实现，比如 cluster 插件实现，在连接数据库时，通过程序连接数据库，通过ipq连接，就需要主机地址，监听端口，数据库名，用户名和密码。

host=localhost port=5432 dbnaneFnydb connect_ tineoute 10

网络端口 ， 数据库名 ， 参数

postresq1: // [user[:passvord]@] [ n etloc] [:port][/ dbname]

[?p aram1=value1 & . .. ]

在连接一个数据库时候提供数据库名 database ， 如果通过命令创建很多数据 。

CREATE DATABASE 可以创建很多数据库 ， 在一个集群下面可以有很多个数据库 ， 这些是逻辑分开的一些概念，每一个库下面，比如连在 dn 库下面可以放进很多 schemas，默认是 pubilc schemas，默认创建好 ， 权限赋予给所有pubilc 下面的角色 ， 创建 schemas sa，下面就会多sa的postgres ， 当连接到一个数据库之后要访问不同 schemas 下面的表，from sa.tablename;

当连接数据库之后访问本地数据库 ， 数据库里面所有的 schemas 直接带上点即可，在 digoal 库下面访问 postgres 库里面东西不行 ， 必须要重新连接到 postgres 库 ， 访问 postgres 的东西 。 在 postgres 里面不像 schemas ， 创建好多用户 ， 和 schemas 类似于绑定在一起的概念 ， 一旦数据库连接以后 ， 只要有权限用户都可以访问 ， 在集群下面如果有多个数据库 ， 跟数据库之间是没有办法直接访问的 ， 可以通过其它的一些方法，比如 databse 访问，库的库之间一个隔离状态 ， 集群层面有共享集群系统表，

pg database

S elect = from pg _ database

T emlate1

T emlate0

P ostgres

D igoal

全局的表 ， 不管连接到哪个库访问 pg database 表都一样 ， 连接到 digoal 也是一样 ， 因为是全局对象 ， 全局的数据表 ， 对于单个数据库 schemas下面建立的是到对象层级 ， 创建 Table(s) ， Index(s) ， View(s) ， Function(s) ， Sequmce(s) ， Other(s) ， 逻辑层面分为 5 个层面 ， 最顶级的 instance 的 cluster 。

cd $ PGDATA

6 pg93 pg93 4. 0 K Dec 27 18:58 base

2 pg93 pg93 4. 0 K Dec 27 19:09 global

2 pg93 pg93 4. 0 K Dec 2? 18:38 p9 clog

1 pg93 pg93 4.4K Dec 27 18:43 p9 hba . conf

1 pg93 pg93 1.6K Dec 27 18:38 pg ident . conf

2 pg93 pg93 4. 0 K Dec 2? 19: 0 2 p9 log

4 pg93 pg93 4. 0 K Dec 27 18:38 p9 nultixact

2 pg93 pg93 4. 0 K Dec 27 19:02 pg notify

2 pg93 pg93 4. 0 K Dec 2? 18:38 pg serial

2 pg93 pg93 4. 0 K Dec 27 18:38 p9 snaps hots

2 pg93 pg93 4. 0 K Dec 27 18:38 pg stat

2 pg93 pg93 4. 0 K Dec 27 18:38 pg stattmp

2 pg93 pg93 4. 0 K Dec 27 18:38 pg subtrans

2 pg93 pg93 4. 0 K Dec 27 18:50 p9 tblspc

2 pg93 pg93 4. 0 K Dec 27 18:38 pg twophase

1 pg93 pg93 4Dec2718:38PGUERSION

3 pg93 pg93 4. 0 K Dec 2? 18:57 p9 xlog

1 pg93 pg93 20K Dec 2? 19:02 pos tgresql. conf

1 pg93 pg93 2 ？ Dec 27 19:02 postmaster.opts

1 pg93 pg93 71 Dec 27 19:02 postmaster.pid

数据库里面有 schemas ， 默认有 public ， 创建其他的 schemas 。

digoa 1

List of schemas

P ublic postgres

S a postgres

在库里面创建 schemas 类似于连接到 ouner 用户之间的层级 ， schemas 类似于 ouner 层级 ， database 类似于 ouner 顶级的层级 ， 再往上一层是 cluster ， schemas 下面是创建对象 ， 逻辑层 。

对于每一个表 ， 每一个对象 ， toast 表或是普通的 table 表 ， index 索引或者 sequence 序列 ， 都会有自己对应的数据文件 ， 数据文件单个文件的大小在编译数据库时 ， 安装时指定大小 ， 默认是 1 gb

W ith-segsize=SEGSIZE set table segnent size in GB [1 ]

单个文件 1 gb ， 当表超过 1 gb时会生成同名 ， 但是后面加后缀 ， 比如 123， 当表超过 1 gb ， 就会变成 123 . 1 文件 ， 对于其它表 ， 索引也是一样的情况 ， 数据库的物理文件 ， 还包括数据文件 ， wals 日志 ， 还可以做归档文件 ， 跟 ouner 非常类似 。

三、PostgreSQL 数据库进程结构概貌

9 . 3 还会比这个多一些进程 。

P s - ewf ！ G rep pg 93

/ o pt/pgsq1932/bin/postgres 主进程 ， 监听进程

postgres : l ogger process

postgres: checkpointer process

postgres : writer process

postgres: wal writer proces s

grep pg93

netstat anpigrep 1999

监听在 13723 进程下面 ， 同时它也是所有进程的父进程 ， 比如收集日志的进程父进程是 13723，checkpointer 父进程也是 13723， 数据库启动时先启动 / o pt/pgsq1932/bin/postgres 进程 ， 再报一堆子进程 。

postmaster 是 / o pt/pgsq1932/bin/postgres 主进程 ， 它负责监听同时负责 fork 子进程 ， 包括autovacuum launcher ， autovacuum worker ，backend process， 跟用户连上时它也是先跟监听交互好后 fork 进程 ， fork 进程再跟 app 地址沟通 ， wal writer 是把 wal buffer 写到 xlogs 中也就是 wal 日志 ， shared buffer 是bgwriter写 shared buffer 写到 datafiles 中 ， 当 shared buffer 空间不够时 ， 如果执行 sql 语句申请 shared buffer ， backend process 会主动的把 shared buffer 里面的脏的 buffer 写到文件里 ， 也就是在 Postgre 里面不光光是 bgwriter 会写 shared buffer ， backend process 也会写 shared buffer ， 同时 wal buffer 也是一样 ， 当 wal buffer 满了也会到 xlogs 中 ， archiver 负责把已经写满的 xlogs 文件进行归档到另外的地方 。

四、PostgreSQL 系统表介绍

1、系统表，系统表之间基本上都是以 oid 关联，例如 pg_ attrdef.adrelid 关联 pg_ class.oid

oid 可能是隐藏的字段 ，

P g class

查看 postresql 帮助 ， d类似于 dsc 命令 ， 中括号表示可选 ， 加表示 additional detail 额外的详细信息 ， show system objects ， D s + 可以看到系统表的信息 ， pg catalog 后面都是系统 ， pg calss 大小是 96 kb ， 可以看到有字段 oid ， 描述里面打印出的看不到字段 ， 实际上是有 oid 的 ， select oid from pg _ class limit 1； 就能看到记录上面有 oid ， oid 也被其它的用来做关联 。

2、 select relkind，relname from pg_ class where

relnamespace = (select oid from pg_ namespace where

nspname= 'Pg_ catalog') and relkind='r' order by 1，2;

pg_ class 存储的所有对象都在里面 ， 包括表 。relkind 指的是对象的类型 ， r表示 relationships 表 ，S 就是序列 ， 看视图就是v ，pg_ namespace 也有 oid 字段 ， 直接看 pg_ namespace 也是看不到 oid 字段的 ， 隐含的字段 ， oid 对应的是 pg_ class 的 relnamespace 字段 ， 在做关联时可以直接用 。

3、r | pg_aggregate 聚合函数信息，包括聚合函数的中间函数，中间函数的初始值，最终函数等。

4、r | pg_am 系统支持的索引访问方法。(如Ibtree， hash， gist， gin ， spgist)

当前数据库支持哪些类型的索引通过 from pg_am 查询 ， 可以看到 amname 里面对应的 btree ， postresql 数据库支持hitmap 索引 ， btree 索引 ， hash 索引 ， gist 索引 ， gin 索引 ， spgist 索引 ， 支持五种索引方法 ， 在创建索引时可以选择哪个索引 ， 索引和数据类型有关系 ， 并不是所有的类型都支持 gist 索引 ， 并不是所有的类型都支持 btree 索引 ， 普通类型使用 btree 索引 ， 像一个字段里面存储多种值 ， 数据点的类型 ， 可以用 gin 或者 gist 类型 ， 比如存储数组 ， 数组类型可以选择用 gin 或者 gist ， hash 索引用的非常少 ， 只支持一种操作符号完全相等 。

5、r | pg_amop 存储每个索引访问方法操作符家族(pg _opfamily)中的详细操作符信息。

6、r | pg_ amproc 存储每个索引访问方法操作符家族(pg_ opfamily)支持的函数信息。

7、r | pg_ attrdef 存储数据表列的默认值(例如创建表时指定了列的default值)。 一张表创建完后对应的某一个列对应的默认值是什么 ， 对应的是 attrdef 系统表 。

8、r | pg_attribute 存储数据表列的详细信息包括隐含的列(ctid， cmin， cmax， xmin， xmax)

创建一个表table tb 1< id int .info text . crt _ time timestamp >；

D tbl就能看到字段 ， 也可以通过from pg attribute where attrelid = ‘tbl’ ：： regclass ； 做隐形转化 ， 可以直接把tbl类型转换成 regclass 类型 ， 转成 16470， sql 语句直接写成 16470 也可以 ， 常用的方法是转成 regclass ， 虚拟的数据类型 ， 通过 attribute 输出的三个字段 ， 对应的 attum 是 1，2，3，-1，-3，-5，-6，-7。

drop table tbl;

创建表

create table thl(id int ， info text ， crt_time timestanp> with oid s ;

查询系统表 ， 可以看到字段 ， 因为是隐含字段 ， 大于等于 1 的attnum中 ， 可以看到如果加了oid就会多了 -2 的attnum ， 包含隐含字段ctid ， ta bleoid，cmax，xnax，cmin， X min，oid

1. r | pg_ auth_ members 数据库用户的成员关系信息。 比如创建某一个角色 ， 所有的权限都是基于角色进行控制 。

grant digoal to postgres ;

select =from pg_auth_members

P g_roles 视图

在 Postgre 中很多系统都是通过 oid 做管理 。

Pg _ authid

select oid，* from pg_authid ;

有两个用户 ， 它的oid分别是 10 和 16482。

Grantor 10 把权限给 16482。

1. r | pg _authid 存储数据库用户的详细信息(包括是否超级用户，是否允许登陆，密码(加密与否和创建用户时是否指定encrypted有关)，密码失效时间等)。

输入 123， 可以认为是一个字符串 ， 也可以认为数据类型 ，

insert into tbl values <'123' ，' test' now<>>;

做了隐式转化所以可以输入 。

11、r | pg _cast 数据库的显性类型转换路径信息，包括内建的和自定义的。

12、r | pg_ class 几乎包括了数据库的所有对象信息(r = ordinary 表 table， i= index， 大写 S = sequence， 视图 v = view， m = materialized view， 自定义类型 c = composite type， t= TOAST table， 外部表 f= foreign table)

relkind中存储单字符串数据 。

13、r | pg_ collation 集信息，包括 国际化的信息 encoding， collate， ctype 等。

F rom pg_ collation；

可以看到collnamespace ， collowner ， collencoding ， collcollate ， collctype 。

Postgre支持的编码都在里面 。

14、r | pg _constraint 存储列上定义的约束信息(例如PK ， FK ， UK ， 排他约束 ， check 约束 ， 但是不包括非空约束)

15、r | pg _conversion 字符集之间的转换的相关信息

connname 转换名字叫 ascii _ to _ mic ， 可以把ascii编码转换成 mic ， 也有 gbk 相互转换 ，df euc_jr to_utf8

16、r | pg _ database 集群中的数据库信息

17、r | pg _db_role_setting基于角色和数据库组合的定制参数信息 。 (alterroleset... ）

F rom pg_db_role_setting没有信息

A lter role digoal set enable _ seqscan =off；16482 角色参数是关闭的 ， 用户连接后不允许做全链扫描 ， 是在其它所有的路径不通的情况下 ， 但是有一个索引在就允许它 。

grant se lect on ALL TABLES IN schema sa to digoal;

查 default 表中什么也没有 ，

创建 create table sa.tbl < id int > ;

D p’ sa. tb1

\ c digoal digoal

用户没有链接权限

alter role digoal login;

S elect*from sa.tbl;

permission denied for s c he m a sa

grant select on ALL TABLES IN *chem a sa to digoal;

LTER DEFAULT PRIỤI LEGES FOR role postgres in schema sa

grant all on tables to digoal ;

在 sa 里面所有的用户 postgres 创建的表 ， 所有的权限都赋予给 digoal ， 默认会进行 arwdxt postgres

C reate table sa .test ;

在创建表时会读 from Pg_ default _acl ； 系统表 ， 自动生成 arwdxt postgres 权限 。

ALTER DEFAULT PRIULEGES FOR role postgres in schema sa

grant select on tables to test ；

多了读的权限 ， 假设在 sa schema下面创建表的对象 ， 创建对象时自动把读的权限进行使用 ， 通过命令给予的权限都会反映在 Pg_ default _acl 系统表中 ， 只跟 ALTER DEFAULT PRIULEGES FOR 命令有关系 。

C reate table sa . test 1；

D p * sa .test1

18、r | pg _ default_ acl 存储新建对象的初始权限信息

19、r | pg _ depend 数据库对象之间的依赖信息

20、r | pg _ description 数据库对象的描述信息

21、r | pg _ enum 枚举类型信息

22、r | pg _event_ trigger 事件触发器信息

23、r | pg _extension 扩展插件信息

24、r | pg _ foreign_ data_ wrapper FDW信息

25、r | pg _ foreign_ server 外部服务器信息

26、r | pg _ foreign_ table 外部表信息

27、r | pg _ index 索引信息

28、r | pg inherits 继承表的继承关系信息

29、r | pg langage 过程语言信息

支持internal ， c ， sql ， plogsql四种语言 。

30、r | pg _ largeobject 大对象的切片后的真实数据存储在这个表里

31、r | pg _largeobject_ metadata 大对象的元信息，包括大对象的 owner ， 访问权限.

32、r | pg _namespace 数据库中的 schema 信息(pg 中称为 namespace)

33、r | pg . _opclass 索引访问方法的操作符分类信息.

34、r | pg _ operator 操作符信息

35、r | pg _opfamily 操作符家族信息

36、r | pg _pltemplate 过程语言的模板信息

37、r | pg _proc 数据库服务端函数信息

​ create function f ​​ _ ​​ tes ​​ t< ​​ id int ​​ > ​​ returns int as $$ ​

​ declare ​

​ begin ​

​ R ​​ eturn ​​ ​​ id+1; ​

​ end ; ​

​ $$ language plpgsql strict ; ​

​ F ​​ rom pg ​​ _ ​​ proc where pronane ​​ = ​​ 'f ​​ _ ​​ test ​​ ’; ​

​ 存在prosrc中 ​ ， ​

​ declare ​

​ begin ​

​ R ​​ eturn id+1 ; ​

​ end ; ​

在 Postgre 里面创建同名的函数 ，

​ create function f_testreturns int as $$ ​

查询到两个函数 < id ， idl >， 对应同一函数名 ， 只要参数类型不一样 ， 就可以创建多个函数 ， 调用一个类型就是 di ， 调用两个类型就是 f _ test 。

38、r | pg _range 范围类型信息

39、r | pg _rewrite 表和视图的重写规则信息

40、r | pg _ seclabel 安全标签信息(SELinux)

41、r | pg _shdepehd 数据库中的对象之间或者集群中的共享对象之间的依赖关系

42、r | pg _shdescription 共享对象的描述信息

43、r | pg _ shseclabel 共享对象的安全标签信息(SELinux)

44、r | pg _ statistic - analyze 生成的统计信息，用于查询计划器计算成本 。

45、r | pg _tablespace 表空间相关的信息 。

46、r | pg _rigger 表上的触发器信息

47、r | pg _ts_config 全文检索的配置信 息

48、r | pg ts_ config_map 全文检索配置映射信 息

49、r | pg _ts_dict 全文检索字典信息

50、r | pg _ts_parser-全文检索解析器信息

51、r | pg _ts_ template 全文检索模板信息

52、r | pg _type 数据库中的类型信息

53、r | pg _user mapping - foreign server 的用户配置信息 。

五、PostgreSQL 系统视图介绍

1、 ​ select relkind ​ ， ​ relname from Pg class where

relnamespace = (select oid from Pg_ namespace where

nspname- 'Pg catalog) and ​ ​ relkind=v' order by ​ 1， ​ 2 ​ ； ​

系统视图通过另外一个 sql 语句可以达到 ， 或者 dv s 也能得到系统的值 。 包括安装的 Pldbgapi 数据库 ， 在启动时会读 。

2、 v | pg_ available_extension_ versions 显示当前系统已经编译的扩展插件的版本信息

通过版本进行查询 ， plpgsql 1 . 1 . 0

安装了两个插件 Pldbgapi ， plpgsql 。

3、 v | pg_ available_extensions 显示当前系统已经编译的扩展插件信息

4、v | pg _cursors 当前可用的游标

5、v | pg group 用户组信息

6、v | pg _indexes 索引信息

7、 v |pg_ locks 锁信息

8、 v | pg_matviews 物化视图信息

9、v | pg prepared statements 当前会话中使用 prepare 语法写的预处理 SQL 信息 。

10、v | pg _ prepared _xacts 二阶事务信息

11、v | pg _ roles 数据库角色信息

12、v | pg _rules 数据库中使用 create rule 创建的规则信息 ， 规则可以用来做数据分区 。

13、 v | pg. seclabels -安全标签信息

select from pg _ shadow

14、v | pg_ settings 当前数据库集群的参数设置信息

15、 v| pg_ shadow 数据库用户信息

可以看到密码 nd53175bce1 d3201d16594ce bf 9d7eb3f 9d ， 空的代表用户没有密码 。 输入权限的角色 。

16、v | pg _stat_activity 会话活动信息

17、v | pg _stat _ all _ indexes 查询用户权限范围内的所有索引的统计信息

18、 v | pg_stat_ all _tables 查询用户权限范围内的所有表的统计信息 ， stat统计信息的意思

19、 v | pg_ stat_ bgwriter _ bgwriter 进程的统计信息

20、 v | pg_stat_ database 数据库级别的统计信息

21、v | pg _ stat_ database_ conficts 数据库冲突统计信息

22、 v | pg_ stat_ replication 流复制相关的统计信息

23、v | pg_ stat_ sys_ indexes 系统表相关的索引统计信息

24、 v Ipg_ stat_ sys_tables 系统表统计信息

25、 v | pg _stat_ user_ functions 用户函数统计信息

26、v | pg _stat_user_ indexes 用户表的索引相关的统计信息

27、v | pg _stat_user_ tables 用户表统计信息

28、v | pg_ stat_ xact all tables 当前事务的表级统计信息，显示用户可以访问的所有表

29、v | pg_stat_ xact_ sys _tables 当前事务的表级统计信息，仅显示系统表

30、v | pg_ stat _xact_user_ functions 当前事务的用户函数的统计信息

31、v | pg _stat_ xact_ user _ables 当前事务的用户表的统计信息

32、v | pg_ stalio_ all indexes - io 相关的统计信息

33、v | pg_ statio_ all sequences

34、v | pg_ statio_ all tables

35、v | pg_ statio_ sys_ indexes

36、v | pg_ statio_ sys_ sequences

37、v | pg_ statio_ sys_ tables

38、v | pg_ statio_ user_indexes 用户级别

39、v | pg_ statio_ user_ sequences

40、v | pg_statio_ user_ tables

41、v | pg_ stats 数据库中的统计信息，以列为最小统计单位输出

42、v | pg_tables 数据库中的表对象的信息

43、v | pg_ timezone_ abbrevs 时区缩写信息

44、v | pg_ timezone_ names 时区信息，包含全名

45、v | pg_ user 用户信息

46、v | pg _user_mappings 外部表的用户映射权限信息

47、v | pg_ views 视图信息

六、PostgreSQL 管理函数

系统视图还是系统表之间的关联都是通过 oid ， references 表示关联关系 ， relnamespace 是 oid 类型 ， 关系到pg namespace . oid 表的字段 ， 在 pg . class 查表对应的放在哪个 space 下面 ， 可以通过关联关系进行对应 ， 比如对应的类型是什么对应的是 pg type . oid ， 属于哪个用户下面的 pg authid. oid 查询 ， 很多都是通过 oid 进行关联 。

1、http://www.postgresql.org/docs/9.3/static/functions-admin.html

2、 管理函数

3、 服务端信号发送函数

4.备份控制函数

5、 恢复 控制函数

6、 事物镜像导出函数

7、数据库对象管理函数

8、数据库对象存储位置管理函数

表对应的是哪个数据文件可以通过函数查询 ， 在 pg _ root 下相对路径 ，base /16392/16476_ fsn，base/16392/16476_vm， 如果看底层信息可以创建 create extension pgpageinspect ;

select from fsm_ page contents

0>>; 输出底层信息 ， 输入 main ， vim 查看

9、文件访问函数

10、应用锁函数，对于长时间持锁的应用非常有效， 做 dml 之后对一条记录产生多个版本 ， 通过多个版本进行 bmr控制 ， 降低控制冲突 ， 所以数据库要不停的回收垃圾 ， 如果是 key bigint 事物并且长时间开在这里 ， 不关闭 ， 事物之前所产生的垃圾不能被会收掉 ， 因为是 key bigint 隔离级别的事物 ， 能够有权利读到以前变更 ， 并且已提交的垃圾数据 ， 即使数据删掉 ， 也能读到数据 ， 垃圾回收并不能把所有数据都回收掉 ， 因为长时间的数据库重量锁会带来垃圾回收的问题。

七、PostgreSQL 进程结构

1、进程源码大部分在: src/backend/postmaster。

2、postmaster 所有数据库进程的主进程(负责监听和 fork 子进程)。

3、startup 主要用于数据库恢复的进程。

4、syslogger 记录系统日志。

5、pgstat 收集统计信息。

6、pgarch 如果开启了归档，那么 postmaster 会 for 一 个归档进程。

7、checkpointer 负责检查点的进程，

8、bgwriter 负责把 shared buffer 中的脏数据写入磁盘的进程

9、autovacuum lanucher 负贵回收垃圾数据的进程，如果开启 了 autovacuum 的话，那么 postmaster 会 fork 这个进程。

10、autovacuum worker 负责回收垃圾数据的 worker 进程，是 lanucher 进程 fork 出来的。

八、PostgreSQL 物理结构

1、数据存储结构

第一个问题:对象对应的物理文件在哪里?

​ c ​​ reate table tb ​​ lt ​​ (id int) tablespace tbs test ; ​

​ pg tblspc/15384/PG_ 9.3 201306121/16392/16609 ​

目录 ， 数据库 ， 表空间的 oid ， 数据库 ， 表的 id

​ select oid from pg_tablespace where spcname= ​ ‘ ​ tbs_test' ; ​

16392 连接的是当前对应的数据库

​ select oid from pg database where datname .' digoal ' ​

16392

表空间 ， 表空间的 oid，版本信息 ， 数据库的 oid ， filenode oid

​ pg_ tblspc/66422/PG_9.3_201306121/16384/12658 ​

​ (1 row) ​

分解 ：

数据库总目录 ， 短链接

​ pg_ tblspc/66422/PG_ 9.3_ 201306121/16384/12658 ​

代表$PGDATA 中的相对路径

2.66422

这个对应表空间 oid

​ digoal= # select spcname from pg_ ,tablespace where oid=66422; ​

​ spcname ​

​ tbs_ _digoal ​

​ (1 row) ​

2、 数据文件结构

​ One DataFile(s) Per Table or Index . ​

​ BlockID : ​

​ sequentially, 0 to 0xFFFFFFE ​

Initilixd Block 0x00000000 是数据块 ， 每插入一个记录 ， 有合适的会取 ， 没有合适的会新生成数据块 ， 生成数据块 ， 数据块的大小在编译数据库时的大小默认是 8。

​ with-blocks ​ i ​ ze -BLOCKSIZE set table block size in kB [8 ] ​

数据块不够用 ， 或者没有数据空间 ， 会生成数据块 8kB。 数据块在整个数据体系中有 id ，0 号数据块 ，1 号数据块 ，0 号数据块里面第一条记录 <0,1>， 数据块里面存储了很多条数据 ，0，1，0，2，0，3，32 位的地址空间 ， 所以最多到 0xFFFFFFE 数据大小 ， 新生成block id继续往后 ， datafile 应该是 datafiles ， 因为是很多数据文件 ， 一个数据文件的大小有限 ， 默认 1 gb ， 有 2 gb 时第 ​ 二个数据文件不是从0 号开始编 ，从前面那个号用完开始再加一个号开始编的。

3、单个 BLOCK 的结构

PageHeaderData(24 Bytes) 配置头部信息 24 字节

ItemIdData(Array of (offset,length) pairs 在数据块中的位移地址 ， 位移地址是 6000， 从第 6000 个字节开始 ， 长度是 100，6000-6100 就是在数据块中的位置

数据开始分配 ， 在页面中的索引

​ pointing to«she actual items. 4 bytes per item) ​

​ Free space(The unallocated space. ​

​ New item pointers are allocated from the start of this area, ​

​ new items from the end.) ​

行的索引从下往上分配

​ Items (The actual items themselves.) ​

如果是索引数据块 ， 在最底部有索引相关的信息 ， 如果是存数据的数据块 ， 存表 ， 以下信息就没有 。

​ Special space (Index access method specific data. ​

​ Different methods store different data. Empty ​

​ in ordinary tables. )( an access method should always ​

​ initialize its pages with PageInit ​

​ and then set its own opaque fields. ) ​

4、BLOCK 头数据结构

2、TUPLE 数据结构

如果存储的是表的数据块 ， 表里面有每一条记录 ， 每一条记录对应每一行的头部信息是 23 个字节 ， 中间三个虚线是可选的 ， 只要有字段允许为空 ， 就有 null bitmap，1 表示非空 ，0 表示空 ， 如果有空 ， 空的字段在行中没有占任何空间 ， 只能通过 bitmap 标识它 。

​ HeapTupleHeaderData(23 Bytes on most machine) ​

​ null bitmap(Optional,The null bitmap is only present ​

​ if the HEAP_ HASNULL bit is set in t infomask. ​

​ occupies enough bytes to have one bit per data column. ​

​ a 1 bit indicates not-null, a 0 bit is a null) ​

存储对齐添加

​ Padding(Optional, Any padding needed to make t_ hoff a

MAXALIGN multiple will appear between the null bitmap

and the object ID) ​

创建表时有 object ID， 行头部里面也会包含

​ object ID(Optional, only present if the ​

​ HEAP_ HASOID bit is set in t_ infomask) ​

​ ColumnData ​

3、TUPLE 头部数据结构

7、使用 pg_ pageinspect 插件可以观察这些数据

图例 ， data 目录 ， base 相对目录默认表空间 ，16385 数据库 oid ，24692 表空间 oid ， 默认表空间下面是数据库的oid ， 如果是自定义的表空间 ， 下面会有表空间的 odi ， 数据文件被切成数据块的单位 ， 在数据块中存储行的信息 ， 数据块头部 ， 行的信息 tuple ， 从下往上分配 ， 在当前页面中的索引 。

九、PostgreSQL 数据库可靠性

1、 当一批事物提交后 ， 数据库只要硬件没有坏 ， 事物在下次也是提交的状态 ， 不会变成中间状态 ， 通过 wal 保证 ， fsync 指的是把 wal buffer里面的数据放到 wal 存储的磁盘里面 ， 通过调用 fsync 的接口 ， 调用内部短信的接口 ， 把buffer 信息写到存储上面 ， 对于 write cache 有断电保护 ， 断电了也能写到磁盘里面 ， 硬盘级别的 cache 没有断电保护 ， 比如笔记本硬盘 write cache 打开 ， 再写数据 ， 笔记本是死机状态 ， 会发现某些数据丢失 ， 写在硬盘 cahce 上面 ， 没有写到硬盘非易失存储 ， 会导致丢失 ， postgre 只到了 write cache 这一层 ， 在这个架构里面硬盘 ， 硬盘的write cache 不要打开 ， 确保存储有断电保护才要打开 ， 如果不能保证 ， 硬件在损坏之后 ， 存储掉电 ， 数据会丢失 ， 只能保证到 buffer cache ， 交给存储控制器 ， 靠硬件 ， 如果没有掉电保护 ， 建议关掉 ， 硬盘的 cache 一定要关掉 。 当安全时数据可以得到保障 。

1. 归档的图例 ， 做了 checkpoint 的点 ， 数据块在第一次变更时整个数据块都会写到 wal ， 后面变只写一部分 ， 确保在做恢复时拿到所有的数据 ， 左边是在线的备份 ， 备份完把归档日志也要备份 ， 如果有误操作 ， 恢复到点 ， 可以把备份拿出来 ， 做时间点的恢复 ， 确保数据库可靠 。

3、让数据库可靠的注意事项

跟数据库可靠性息息相关 ， 当配置不当 ， 会失去可靠性 /

1、事务提交后确保这个事务未来可恢复吗?

事务返回成功前,事务日志(xlog)写入磁盘, synchronous_ commit = on 保持数据库一次性 ， 但是不能保证数据库完全不丢失 ， 不可能出现先提交的事物在后提交的事物之后 ， 数据一次性可以保证 ， 可能会丢失没有写入磁盘的一些信息 ， 大大提高写的效率 ， 如果磁盘 io 性能不够的情况下 ， 会发现使用 synchronous_ commit 之后 ， 写信会有极大的提高 。

2、备份可恢复吗?恢复后确保数据 一 致吗?

fsync= on . full_ page_ writes = on 打开 ， 数据一致 。

3、必须写入非易失存储的 数据已经写入到非易失存储了吗?

write - through，write - back 存储没有掉电保护 ， 建议关闭 write cache ， 使用 write - through

关闭磁盘的 write cache

只允许有断电保护的 write cache。

4、主机异常 DOWN 机后重启数据库能不能起到一个一致的状态?

write 打开 ， ​ PostgreSQL periodically writes full page images

to permanent WAL storage before modifying the actual

page on disk. -- full_ _page_ writes= on ​

5、数据库异常 DOWN 机后重启数据库能不能起到一个一致的状态?

​ PostgreSQL periodically writes full page images to

permanent WAL storage before modifying the actual page

on disk. -- full page_ writes = on ​

6、事务日志可以用于恢复到任意时间点吗?

开启归档,并且有良好的备份策略。

wal _level = archive 或 hot standby

7、如果存储挂了怎么办?

开启归档,并且有良好的备份策略。

wal_ level = archive 或 hot_standby

archive_ mode = on 打开归档

​ archive_ command = 'cp %p /backup/%f ​

8、如果 IDC 挂了怎么办?

开启归档,并且有良好的备份策略。

​ wal_ level = archive 或 hot _standby ​

异地容灾,如流复制。

​

十、练习

1、系统表直接的关联关系的熟悉

2、系统视图的使用

3、系统管理函数的使用

4、pageinspect 插件观察数据块,uple,数据