Day01-postgresql数据库基础入门培训

1、PostgresQL数据库简介

• PostgreSQL【简称：PG】是加州大学伯克利分校计算机系开发的，一个开源免费的关系式数据库管理系统，经过长达15年以上的积极开发和不断改进，PostgreSQL已在可靠性、稳定性、数据一致性等获得了业内相对高的声誉。

• PostgreSQL可以运行在所有主流操作系统上，包括Linux、AIX、HP-UX、Solaris和Windows等34种平台。

• PostgreSQL是完全的事务安全性数据库，完整地支持外键、联合、视图、触发器和存储过程(并支持多种语言开发存储过程)。

• PostgreSQL支持了大多数SQL标准的数据类型，包括整型、数值型、布尔型、字节型、字符型、日期型、时间型、二进制的大对像(图片、声音和视频)。

• PostgreSQL的存储过程开发可以使用众多的程序语言，包括Java、Perl、Python、Ruby、Tcl、C/C++和自带的PL/pgSQL，其中的PL/pgSQL与Oracle的PL/SQL很相似，内置了数百个函数，功能从基本的算术计算和字符串处理到加密逻辑计算并与Oracle有高度兼容性。

• PostgreSQL支持国际字符集、多字节编码并支持使用当地语言进行排序、大小写处理和格式化等操作。

• PostgreSQL对很多高级开发语言有原生的编程接口，如C/C++、Java、.Net、Perl、Python、Ruby、Tcl和ODBC以及其他语言等，也包含各种文档。

• PostgreSQL有很多高级功能，像多版本并发控制(MVCC)、按时间点恢复(PITR)、表空间、异步复制、嵌套事务、在线热备、复杂查询的规划和优化以及为容错而进行的预写日志等。

• PostgreSQL有很多高品质的图形化的PostgreSQL管理工具软件，包括开源和商业性质的。

• PostgreSQL的源代码可以自由获取，它的授权是在非常自由的开源授权下，这种授权允许用户在各种开源或是闭源项目中使用、修改和发布PostgreSQL的源代码。用户对源代码的可以按用户意愿进行任何修改、改进。

• PostgreSQL的快速发展是由于MySQL被Oracle公司收购导致的，同时在2019年微软官宣收购PostgreSQL初创公司Citus Data(OLAP：citus分布式插件)。

2、PostgreSQL行业生态应用

• PostgreSQL被称为“世界上最先进的开源关系型数据库”，属于一专多长的全栈数据库，主要场景应用如下:

  • OLTP交易系统
  • OLAP、批处理、数据仓库
  • 空间与地理数据库(PostGIS)
  • 缓存系统、消息队列/流处理
  • 搜索索引、图数据库
  • NoSOL数据库
  • 时序数据库 【MySQL被称为“世界上最流行的开源关系型数据库”】

• 在全球数据库排名中，PostgreSQL排名全球第四(统计时间:2023.06)

• 在商业数据库当中，Oracle属于多专多长的全数据库，全球最强。
• 在开源数据库当中，MySQL主要应用于互联网业务的OLTP，所以PostgreSQL弥补了MySOL在传统行业不擅长的方向，又解决了商业数据库的成本问题。
• 随着信创项目的推动，国产数据库主要以Oracle、MySQL、PostgreSQL三个方向为主，目前以PostgreSQL开源进行二次开发的国产数据库应用越来越广，同时国内外使用的客户越来越多，比如：平安、苏宁、人寿、招商，DELL，富士通，SAP苹果，本田，丰田等。
• PostgreSQL基于云平台的使用，如AWS、阿里云、腾讯、华为都有用到PG的社区版作为数据库可选方案之一

根据中国信通院发布的数据库发展研究报告2021，我国关系型数据库基于MySQL和PostgreSQL二次开发的数量依次占关系型数据库比例为28.40%和29.63%。

• 从目前国内发布的产品来说，大部分都高度兼容Oracle。
• 未来国内市场数据库主流的产品线为3种模式:
  • Oracle
  • PostgreSQL
  • MySQL(MySQL+MongoDB+Redis)

从国内外数据库源流分支来说(依托开源协议来构建或二次开发)，源流于PostgreSQL的开源协议框架，据不完全准确统计，列举部分-Part1：

• EDB(Enterprise Postgres Database)
• PostGlS、Citus、Timescaledb、PipelineDB
• GreenPlum
• 电信TeleDB
• 腾讯TDSQL PostgreSQL版(原TBase)
• 阿里PolarDB for PostgreSQL/AnalyticDB PostgreSQL
• 人大金仓KingBase

源流于PostgreSQL的开源协议框架，据不完全准确统计，列举部分-Part2：

• 瀚高HighGo DB
• 酷克HASHDATA
• 优炫UXDB
• 亚信AntDB
• 偶数HAWQ
• 华为GaussDB A(200+300)
• 华为openGauss

源流于PostgreSQL的开源协议框架，据不完全准确统计-Part3

其中基于华为openGauss又包括-Part1：

• 中国移动-磐维数据库
• 中国联通-CUDB
• 海量Vastbase
• 恩墨MogDB
• 神舟通用
• 南大通用Gbase 8c/8s
• 恒生电子LightDB

源流于PostgreSQL的开源协议框架，据不完准确统计-Part4

其中基于华为openGauss又包括-Part2：

• 超聚变FusionDB
• 虚谷伟业-有蓉数据库
• 北京太阳塔LNXDB-RDS
• 沐融信息MuDB
• 天曦TXDB

3、PostgreSQL版本发展与特性

PostgreSQL最新正式版15.3(2023.6)，2023年发布正式版16.1。

各版本发布的时间与生命周期如下:

postgresql各版本特性介绍：https://www.postgresql.org/about/featurematrix/

PostgreSQL各版本特性介绍:

• 1986年：Postgre诞生

• 1989年：Postgres 1 发布

• 1990年：Postgres 2 发布，重写了规则系统

• 1991年：Postgres 3 发布，改进了规则系统，增加了对多种存储系统支持的能力，并且改进了查询引擎。

• 1993年：Postgres 4 发布，随便用户与特性需求急剧增加，随后该项目正式终止

• 1994年：新增SQL语言解释器，建立了Postgres95。

• 1996年：Postgres改名PostgreSQL，正式社区化发展。

• 1997年：PostgreSQL 6.0 发布，第一个正式版本，后续1997年~1999年期间陆续发布了6.1/6.2/6.3/6.4/6.5，新增的功能有：

  • 多列索引、序列、货币和时间数据类型，GEQO。
  • JDBC接口、触发器、服务端编程接口、约束，SOL92标准的子查询
  • 增加了可读视图、PL/pgTGL。
  • MVCC、临时表、更多的SOL语句支持。

• 2001年：PostgreSQL7.0发布，增加了外键、JOIN连接。

• 2001年：PostgreSQL7.1发布，增加了WAL预写式日志、外连接。

• 2002年：PostgreSQL7.2发布，支持PostGlS，索引，函数，增加了PL/Python。

• 2003年：PostgreSQL7.3发布，增加了模式、表函数、prepared query。

• 2004年：PostgreSQL7.4发布，优化了JOIN和数据仓库函数。

• 2005年：PostgreSQL8.0发布，支持Win平台/增加savepoints/表空间/时间点恢复。

• 2005年：PostgreSQL8.1发布，性能增强、增加了两阶段提交、表分区、位图索引扫描、共享行锁、角色。

• 2006年：PostgreSQL8.2发布，性能增强，增加了在线重建索引/咨询锁/热备

• 2008年：PostgreSQL8.3发布，增加全文搜索、SQL/XML、枚举类型、UUID类型等

• 2009年：PostgreSQL8.4发布，窗口查询，数据透视，递归查询，并行恢复，列级权限，CTE公用表表达式。

• 2010年：PostgreSQL9.0发布，支持Windows 64位系统，增加内置二进制流复制、热备、内置升级功能。

• 2011年：PostgreSQL9.1发布，增加同步复制、无日志表、序列快照隔离级别、SELinux集成、扩展、外部表，可写CTE公用表表达式。

• 2012年：PostgreSQL9.2发布，增加级联流复制、原生JSON支持、增强锁管理、range类型、索引扫描、空间分区GIST索引。

• 2013年：PostgreSQL9.3发布，增加触发器、视图、可写外部表、物化视图、复制功能增强，横向连接LATERAL JOIN、。

• 2014年：PostgreSQL9.4发布，Linux大页支持，增加了JSONB、RANGE类型，ALTER SYSTEM语法、不阻塞读的刷新物化视图、动态注册/起停后台进程、逻辑API、GIN索引增强。

• 2016年：PostgreSQL9.5发布，新的BRIN索引，增加UPSERT插入更新、CUBE/ROLLUP/GROUPING SETS分组集合等语法，行级安全

• 2016年：PostgreSOL9.6发布，支持并行查询，FDW功能增强，多机同步standby，快速清空大表。

为了加快市场推广，跟进Oracle版本策略，平均每年发布一个大版本：

• 2017年：PostgreSQL 10发布，支持逻辑复制，内置分区表，增强并行查询机制，数据库编程能力，并行功能增强，全文检索支持JSON和JSONB数据类型，估值计算，文本挖掘，物联网优化，GIS业务优化，图像搜索，基因测序，3D数据处理，机器学习UDF库等。

• 2018年：PostgreSQL 11发布，分区表的改进与增强，存储过程支持事务，并行能力的增强，增加对JIT编译的支持，其它功能完善。

• 2019年：PostgreSQL 12发布，分区性能提升，B树索引增强，公用表表达式（CTE），准备好的计划控制，即时编译，校验和控制，在线重建索引，支持SQL/JSONpath，支持生成列，新增 Pluggable Table Storage Interface等。

• 2020年：PostgreSQL 13发布，Btree索引优化，支持增量排序，支持并行vacuum index，支持可信插件，支持扩展统计信息，支持hash aggregation使用磁盘存储，逻辑复制对分区表的支持。

• 2021年：PostgreSQL 14发布，数据类型与函数更多支持，管理功能增强，复制和恢复增强，索引功能改进，性能提升，安全增强等。

• 2022年：PostgreSQL 15发布，大数据集的排序性能提升，各类查询也进行了优化，支持使用LZ4、Zstandard算法进行压缩备份，引入了jsonlog数据格式用于日志记录，安全性增强，删除public模式的创建权限。

• 2023年：PostgreSQL 16发布，可以实现级联逻辑复制，支持更多的并行查询，安全增强，监控管理增强。

4、PostgreSQL体系结构介绍

应用程序的访问架构

PostgreSQL体系结构

它由共享内存、一系列后台进程和数据文件组成。

PostgreSQL进程分为主进程与辅助进程。

主进程：

• Postmaster进程是整个数据库实例的总控进程，负责启动关闭该数据库实例。

辅助进程：

• SysLogger(系统日志)进程
• BgWriter(后台写)进程
• WALWrite(预写式日志)进程
• PgArch(归档)进程
• AutoVacuum(系统自动清理)进程
• PgStat(统计收集)进程
• CheckPoint(检查点)进程

PostgreSQL的逻辑结构分为实例、数据库、schema、对象；

实例中允许创建多个数据库，每个数据库中可以创建多个schema，每个schema下面可以创建多个对象。对象包括表、索引、视图、序列、函数等。

表空间是物理结构，同一表空间下可以有多个数据库。

数据库是逻辑结构，是表、索引、视图、存储过程、函数等对象的集合，一个数据库下可以有多个schema。

模式是逻辑结构，是对数据库的逻辑划分。

在oracle中用户和schema基本上可以画上等同关系,但是pg中两者没有这样严格的对应关系

将数据文件中的空间从逻辑上划分成一个个页面（数据块）

数据页大小：默认为8K

（可支持4K=16TB，8K=32TB，16K=64TB，32K=128TB）

页面可以分成两种：

• 数据页面：数据页面是用来存储用户数据的
• 控制页面：控制页面用来管理这些数据页面

数据库共享缓存中的空间划分也是按页为基本单位，一个页的大小与数据文件中页的大小一致，这样便于整页读取数据文件，并放入到数据库Buffer中，从Buffer写入数据文件也同理，保证了缓存与数据文件结构和内容上的一致性。

数据库包含的文件种类：

• 数据库文件：数据库对象，如：数据库、表，索引，序列等对象。
• 控制文件：用来记录数据库集群的状态信息，如：版本信息、集群所管理的各种文件信息、检查点信息、事务状态信息等
• 参数文件：存放数据库运行参数
• 日志文件：记录数据修改操作的日志，用于系统发生故障时进行数据恢复。
• 临时文件：存放数据库进行计算的过程中，生成的各种中间对象，如排序运算的外存归并单元。

PostgreSQL由一系列数据库组成。

一套PostgreSQL程序称之为一个数据库群集。

当initdb()命令执行后，template0,template1,和postgres数据库被创建。

template0和template1数据库是创建用户数据库时使用的模版数据库，他们包含系统元数据表。

initdb()刚完成后，template0和template1数据库中的表是一样的。但是template1数据库可以根据用户需要创建对象。用户数据库是通过克隆template1数据库来创建的;

一个表空间可以被多个数据库同时使用。此时，每一个数据库都会在表空间路径下创建为一个新的子路径。

创建一个用户表空间会在$PGDATA\pg_tblspc目录下面创建一个软连接，连接到表空间制定的目录位置。

PostgreSQL内存主要为分共享内存与本地内存。

共享内存
PostgreSQL启动后，会生成一块共享内存，用于做数据块的缓冲区，以便提高读写性能。WAL日志缓冲区和Clog缓冲区也存在共享内存中，除此之外还有全局信息比如进程、锁、全局统计等信息也保存在共享内存中。

其中最重要的组成部分是Shared Buffer和WAL Buffer。

本地内存

非全局存储的数据都存在本地内存中，主要包括：

临时缓冲区：用于访问临时表的缓冲区

work_mem：内部排序操作和Hash表在使用临时操作文件之前使用的存储缓冲区。

manintance_work mem：在维护操作比如：VACUUM（收集表和索引的统计信息，整理表和索引)、CREATE INDEX、ALTER TABLE ADD FOREIGN Key等中使用的内存缓冲区。

5、PostgreSQL与MySQL的区别

序号	特性类型	MySQL	PostgreSQL
1	实例与数据库	1.通过MySQL命令(mysqld)启动实例)； 2.一个实例可以管理一个或多个数据库； 3.一台服务器可以运行多个 mysqld 实例； 4.一个MySQL实例中的所有数据库共享同一个系统编目。	1.通过Postmaster 进程(pg_ctl)启动实例； 2.一个实例可以管理一个或多个数据库，这些数据库组成一个集群； 3.集群是磁盘上的一个区域，这个区域在安装时初始化并由一个目录组成，所有数据都存储在这个目录中； 4.使用 initdb 创建第一个数据库。一台机器上可以启动多个实例； 5.每个数据库有自己的系统编目，但所有数据库共享pg_databases
2	配置文件	my.conf	Postgresgl.conf
3	数据库连接	使用 CONNECT或 USE 语句连接数据库，这时要指定数据库名，还可以指定用户id和密码。	使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。
4	客户机连接文件	my.conf	pg_hba.conf
5	数据缓冲区	通过 innodb_buffer_pool _size参数设置数据缓冲区，这个参数最高可以设置为机器物理内存量的 80%。	通过Shared_buffers参数设置数据缓冲区。在默认情况下分配64个缓冲区。默认的块大小是 8K。可以通过设置postgresql.conf 文件中的 shared_buffers 参数来更新缓冲冲区缓存。
6	执行计划	使用 EXPLAIN 命令查看查询的解释计划。	使用 EXPLAIN 命令查看查询的解释计划，返回丰富的信息,
7	备份恢复	InnoDB 使用重做日志记录，支持在线和离线完全备份以及崩溃和事务恢复，也支持热备份。	在数据目录的一个子目录中维护重做日志。支持在线和离线完全备份以及崩溃、时间点和事务恢复，可以支持热备份。
8	表	取决于存储引擎。例如，NDB存储引擎支持分区表，内存引擎支持内存表。	支持临时表、常规表以及范围和列表类型的分区表，不支持哈希分区表。由于PostgreSQL的表分区是通过表继承和规则系统完成了，所以可以实现更复杂的分区方式。
9	索引	1.取决存储引擎。MyISAM与InnoDB都支持BTREE 2.不支持函数索引，只能在创建基于具体列的索引	1.支持 B-树、哈希、R-树和 Gist 索引。 2.支持函数索引，同时还支持部分数据索引。
10	约束	支持主键、外键、唯一和非空约束。对检查约束进行解析，但是不强制实施。	支持主键、外键、唯一、非空和检查约束。
11	存储过程	支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL 和C++ 编写。	没有单独的存储过程，都是通过函数实现的。
12	函数	用户定义函数可以用 SQL、c 和 C++编写	用户定义函数可以用 PL/pgSQL(专用的过程语言)、PL/TcI、PL/Perl、PL/Python、SQL和C编写。
13	触发器	支持事前、事后触发器和语句触发器，触发器语句用过程语言复合语句编写。	支持事前、事后触发器和语句触发器，触发器过程用C编写。
14	物化视图	不支持物化视图。	通过规则系统可以实现物化视图的功能。
15	事务与锁	支持表级和行级锁。 InnoDB 存储引擎支持读未提交(read-uncommitted)、不可重复读(read-committed)、可重复读(repeatable-read)、串行化(serializable)。 使用 SET TRANSACTION ISOLATION LEVEL语句在事务级设置隔离级别。	支持表级和行级锁。 支持的隔离级别是Read Committed(默认-看到查询启动时数据库的快照)和 Serialization(与 Repeatable Read 相似，只能看到在事务启动之前提交的结果)。 使用 SET TRANSACTION 语句在事务级设置隔离级别。 使用 SET SESSION 在会话级进行设置。
16	DBLINK	不支持database link。	有dblink、FDW，可以连接到oracle和mysql上。

6、PostgreSQL与Oracle、MySQL的对比