网站后台附件无法上传,推广商,民宿网站建设,灯饰网站建设图片Day01-postgresql数据库基础入门培训 1、PostgresQL数据库简介2、PostgreSQL行业生态应用3、PostgreSQL版本发展与特性4、PostgreSQL体系结构介绍5、PostgreSQL与MySQL的区别6、PostgreSQL与Oracle、MySQL的对比 1、PostgresQL数据库简介 PostgreSQL【简称#xff1a;PG】是加… Day01-postgresql数据库基础入门培训 1、PostgresQL数据库简介2、PostgreSQL行业生态应用3、PostgreSQL版本发展与特性4、PostgreSQL体系结构介绍5、PostgreSQL与MySQL的区别6、PostgreSQL与Oracle、MySQL的对比 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(OLAPcitus分布式插件)。
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种模式: OraclePostgreSQLMySQL(MySQLMongoDBRedis)
从国内外数据库源流分支来说(依托开源协议来构建或二次开发)源流于PostgreSQL的开源协议框架据不完全准确统计列举部分-Part1
EDB(Enterprise Postgres Database)PostGlS、Citus、Timescaledb、PipelineDBGreenPlum电信TeleDB腾讯TDSQL PostgreSQL版(原TBase)阿里PolarDB for PostgreSQL/AnalyticDB PostgreSQL人大金仓KingBase
源流于PostgreSQL的开源协议框架据不完全准确统计列举部分-Part2
瀚高HighGo DB酷克HASHDATA优炫UXDB亚信AntDB偶数HAWQ华为GaussDB A(200300)华为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
可支持4K16TB8K32TB16K64TB32K128TB
页面可以分成两种
数据页面数据页面是用来存储用户数据的控制页面控制页面用来管理这些数据页面
数据库共享缓存中的空间划分也是按页为基本单位一个页的大小与数据文件中页的大小一致这样便于整页读取数据文件并放入到数据库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的区别
序号特性类型MySQLPostgreSQL1实例与数据库1.通过MySQL命令(mysqld)启动实例)2.一个实例可以管理一个或多个数据库3.一台服务器可以运行多个 mysqld 实例4.一个MySQL实例中的所有数据库共享同一个系统编目。1.通过Postmaster 进程(pg_ctl)启动实例2.一个实例可以管理一个或多个数据库这些数据库组成一个集群3.集群是磁盘上的一个区域这个区域在安装时初始化并由一个目录组成所有数据都存储在这个目录中4.使用 initdb 创建第一个数据库。一台机器上可以启动多个实例5.每个数据库有自己的系统编目但所有数据库共享pg_databases2配置文件my.confPostgresgl.conf3数据库连接使用 CONNECT或 USE 语句连接数据库这时要指定数据库名还可以指定用户id和密码。使用 connect 语句连接数据库这时要指定数据库名还可以指定用户 id 和密码。4客户机连接文件my.confpg_hba.conf5数据缓冲区通过 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都支持BTREE2.不支持函数索引只能在创建基于具体列的索引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 在会话级进行设置。16DBLINK不支持database link。有dblink、FDW可以连接到oracle和mysql上。
6、PostgreSQL与Oracle、MySQL的对比
