跳转至

简介

背景和历史

PostgreSQL 是一款高级的企业级开源关系数据库，支持 SQL（关系型）和 JSON（非关系型）查询。

它是一个高度稳定的数据库管理系统，依托 20 多年的社区发展，造就了其高水平的故障恢复能力、完整性和正确性。

PostgreSQL 可用作很多 Web、移动、地理空间和分析应用程序的主要数据存储或数据仓库。目前广为使用的版本为 PostgreSQL 12，最新的的版本已经发到PostgreSQL 16。

PostgreSQL 尝试遵守 SQL 标准，这种一致性不会与传统功能相矛盾，也不会导致糟糕的架构决策。支持 SQL 标准所需的许多功能，尽管有时语法或功能略有不同。随着时间的推移，预计将进一步实现一致性。截至 2023 年 9 月发布的第 16 版，PostgreSQL 至少符合 SQL:2023 Core 一致性的 179 项强制功能中的 170 项。截至撰写本文时，还没有关系数据库完全符合该标准。

PostgreSQL（简称 PG）是世界领先的开源数据库，起源于20 世纪70 年代美国加州伯克利大学，创始人是荣获图灵奖的 Michael Stonebraker。

它的源代码基于 PostgreSQL 许可协议 发行，这是一个类似于 BSD 或者 MIT 的自由开放源码许可协议。用户可以基于任何目的使用、修改和发布 PostgreSQL，甚至直接包装一下拿出去卖钱都没问题，唯一的要求就是保留它的版权声明。这一点相对于 MySQL 社区版的 GPL 协议友好许多。

PG 是强大的企业级数据库，特别适合在大型企业数据管理中运用。作为一种企业级数据库，PG 具有的各种高级功能，像多版本并发控制(MVCC)、按时间点恢复(PITR)、表空间、异步复制、嵌套事务、在线热备、复杂查询的规划和优化以及为容错而进行的预写日志等。PG支持国际字符集、多字节编码并支持使用当地语言进行排序、大小写处理和格式化等操作。同时PG也在所能管理的大数据量和所允许的大用户量并发访问时间具有完全的高伸缩性。

目前包括亚马逊，华为，阿里等世界五百强级别的大型企业在使用PG技术；应用涵盖金融，能源，零售，IT，互联网等各个行业。PG 是大型企业和互联网去 Oracle 的优选方案，能够帮助企业有效管理数据和降低成本；也是大数据、云计算领域架构中关系数据库存储管理的最佳选择。同时，在国内也有包括阿里、腾讯、华为，瀚高在内的许多企业基于 PG 研发云产品及自主可控数据库产品。

工信部中国开源软件联盟 PostgreSQL 分会（简称中国PG 分会）是工信部下属，国内唯一官方认可的 PG 行业协会组织，承担在国内发展和推广PG 技术的职能。 基于Postgresql在中国发展的良好态势，由中国PG分会联合权威认证机构、知名培训机构共同打造中国 PostgreSQL 培训认证体系（简称“中国PG 培训认证”）。

中国 PG 培训认证体系旨在为 PG 生态企业发展提供PG 专业技术人才。为有志于从事 PG 相关工作的人员，提供高效的培训指导、权威的等级认证；

中国 PG 培训认证是目前国内唯一的 PG 技术等级认证，填补了国内 PG 技术领域相关空白，这对 PG 培训乃至 PG 技术的发展推广都会起到巨大的支持和推动作用。

开源协议

PostgreSQL: The world’s most advanced open source database

MySQL：World’s Most Popular Open Source Database

PostgreSQL开源协议：PostgreSQL基于自由的BSD/MIT许可，任何组织都可以使用、复制、修改和重新分发代码，只需要提供一个版权声明即可。

PG的开源协议特别灵活，任何公司的和个人都可以把PG作为一个产品销售，而不需要像MySQL那样必须修改大部分代码才可以作为公司的产品。

PostgreSQL许可是一种自由的开源许可，与BSD或MIT许可证类似。1994年之前的部分版权属于加利福尼亚董事会；1996年-2020年，部分版权属于PostgreSQL全球开发小组；全球开发小组主要成员分散在世界各地，其背后不受任何公司实体控制，这使其成为一个真正的开放项目。

PG事实上不被任何一家公司所掌控，它背后的控制机构——PostgreSQL全球开发小组，是一个松散的组织。其核心成员来自全球各地的不同公司，如果你感兴趣，可以在PG官网查询到这个组织的成员列表。可以这么说，没有任何一个公司享有对PG的绝对控制权，PG永远是属于社区的。

BSD开源协议是一个给于使用者很大自由的协议。可以自由的使用，修改源代码，也可以将修改后的代码作为开源或者专有软件再发布，被誉为开源许可的“活雷锋”。

BSD 代码鼓励代码共享，但需要尊重代码作者的著作权。BSD由于允许使用者修改和重新发布代码，也允许使用或在BSD代码上开发商业软件发布和销售，因此是对商业集成很友好的协议。而很多的公司企业在选用开源产品的时候都首选BSD协议，因为可以完全控制这些第三方的代码，在必要的时候可以修改或者二次开发。

PostgreSQL许可描述：https://www.postgresql.org/about/licence/

MySQL开源协议：核心代码基于GPL或Commercial License。

MySQL的开源协议是基于GPL协议，任何公司都可以免费使用，不允许修改后和衍生的代码做为闭源的商业软件发布和销售，MySQL的版权在甲骨文手中，甲骨文可以推了其商业闭源版本。

GPL(General Public license)是公共许可，遵循了GPL的软件是公共的。如果某软件使用了GPL软件，那么该软件也需要开源，如果不开源，就不能使用GPL软件，这和是否把该软件商用与否是没关系的。

如果无法满足GPL，就需要获得商业许可，通过与Oracle公司联系，制定解决方案，受Oracle公司约束。

具体约束：

  1. 不允许对在MySQL上作出的修改申请专利；
  2. MySQL上的修改需要公开，且所有权归Oracle所有；
  3. 出于纯学术目的、练习目的源码修改也是符合GPL的；
  4. Oracle的MySQL企业版或高级功能会涉及费用，并且Oracle公司不允许其它基于MySQL的闭源产品。

其它基于MySQL的数据库也遵循并必须遵循GPL许可或GPL的修订版GPL V2，譬如Mariadb。GPL 许可逻辑上与商业许可存在冲突，可以理解为：商业许可是控制MySQL的公司留给自己的特权。

由于GPL严格要求使用了GPL类库的软件产品必须使用GPL协议，对于使用GPL协议的开源代码，商业软件或者对代码有保密要求的就不适合集成/采用作为类库和二次开发的基础。从GPL 到GPL V2 V3，另外还有LGPL，这个协议一直有在演变中，内容表述比较复杂，这会影响开源精神的发展与传承并且容易产生分歧。

事务支持

PostgreSQL支持事务的强一致性，事务保证性好，完全支持ACID特性。 MySQL只有innodb引擎支持事务，事务一致性保证上可根据实际需求调整，为了最大限度的保护数据，MySQL可配置双一模式，对ACID的支持上比PG稍弱弱。 MySQL的"双1验证"指的是innodb_flush_log_at_trx_commit和sync_binlog两个参数设置，这两个是是控制MySQL 磁盘写入策略以及数据安全性的关键参数。

对SQL标准的支持

PostgreSQL几乎支持所有的SQL标准，支持类型相当丰富。 MySQL只支持部分SQL标准，相比于PG支持类型稍弱。

并发控制

PostgreSQL通过其MVCC实现有效地解决了并发问题，从而实现了非常高的并发性。PG新老数据一起存放的基于XID的MVCC机制,新老数据一起存放，需要定时触发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀。当然PostgreSQL还有一点影响比较，为了保证事务的强一致性，未决事务会影响所有表VACUUM清理，导致表膨胀。

MySQL仅在InnoDB中支持MVCC，innodb的基于回滚段实现的MVCC机制，但是MySQL的间隙锁影响较大，锁定数据较多。

对ACID的支持

PostgreSQL支持事务的强一致性，事务保证性好，完全支持ACID特性。

MySQL只有innodb引擎支持事务，事务一致性保证上可根据实际需求调整，为了最大限度的保护数据，MySQL可配置双一模式，对ACID的支持上比PG稍弱弱。

性能

PostgreSQL

1）PostgreSQL广泛用于读写速度高和数据一致性高的大型系统。此外，它还支持各种性能优化，当然这些优化仅在商业解决方案中可用，例如地理空间数据支持，没有读锁定的并发性等等。

2）PostgreSQL性能最适用于需要执行复杂查询的系统。

3）PostgreSQL在OLTP/OLAP系统中表现良好，读写速度以及大数据分析方面表现良好，基于PG的GP数据库，在数据仓库领域表现良好。

4）PostgreSQL也适用于商业智能应用程序，但更适合需要快速读/写速度的数据仓库和数据分析应用程序。

MySQL

1）MySQL是广泛选择的基于Web的项目，需要数据库只是为了简单的数据事务。但是当遇到重负载或尝试完成复杂查询时，MySQL通常会表现不佳。

2）MySQL的读取速度，在OLTP系统中表现良好。

3）MySQL + InnoDB为OLTP场景提供了非常好的读/写速度。总体而言，MySQL在高并发场景下表现良好。

4）MySQL是可靠的，并且与商业智能应用程序配合良好，因为商业智能应用程序通常读取很多。

官方文档

官方文档pdf下载

官方文档在线HTML阅读

postgresql基本概念

cluster

A database cluster is a collection of databases that is managed by a single instance of a running database server

初始化后，一个cluster中默认就会包含

从文件系统角度，一个database cluster就是所有数据的一个存放目录，也叫data directory或data area，它通常需要你指定目录，即使很常见是路径是/usr/local/pgsql/data或/var/lib/pgsql/data。

database

在PostgreSQL中，database的创建是通过克隆database template来实现的，这与SQL SERVER是同样的机制。

PostgreSQL安装后会默认附带三个库，分别是postgres、template0、template1。

如果CREATE DATABASE dbname并没有指明database template，所以系统将默认克隆template1数据库，得到新的数据库dbname。

By default, the new database will be created by cloning the standard system database template1

这种特性，可以让用户很好的制定数据库模板。template0和template1在初始状态是相同的。一般可以在template1中自定义模板，template0做为干净崭新的库。

在数据库创建的同时，就已经默认为数据库创建了一个模式--public，这也是该数据库的默认模式。所有为此数据库创建的对象(表、函数、试图、索引、序列等)都是创建在这个模式中的。

CREATE DATABASE name
    [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ STRATEGY [=] strategy ] ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ ICU_LOCALE [=] icu_locale ]
           [ LOCALE_PROVIDER [=] locale_provider ]
           [ COLLATION_VERSION = collation_version ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ]
           [ OID [=] oid ]

schema

在PostgreSQL中，模式是数据库实例的逻辑分割。

数据库(database)是被模式(schema)来切分的，一个数据库(database)至少有一个模式(schema)，所有数据库对象(object)是被创建在模式中的。

用户登录到系统，连接到一个数据库后，是通过该数据库的search_path来寻找schema的搜索顺序：

# search_path类似于linux中的path环境变量。默认值是 `"$user",public`。
# 意思就是当以某个用户登录到数据库的时候，默认就是先查找和登录用户同名的schema，再查找public。

postgres=#
postgres=# SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

postgres=#
postgres=#